This database is linked to the facility database on the AIS Data Warehouse and consists of two data tables. The facility data table accesses the facility records for a given campus and the event data table accesses the room scheduling records for a given campus per semester. The combination of these data tables allows the user to determine room/building availability.

These sample queries are intended to help the user properly access the data in order to retrieve the desired results from a database query.

• FACILITY query will return a list of rooms that meet the users requirements. The query can be refined to provide a list of rooms based on any combination of the following:
  • Campus
  • Room function (General Purpose, Department)
  • Room technology
  • Number of seats, type of seating
  • Physical feature (air conditioning, chalk board, elevator, projector, etc.)
  • Priority Department/College
  • Rooms that have a scheduling block in place for a given date/time or a range of dates/times

• SCHEDULED ROOMS query will return a list of rooms that are scheduled for courses or supporting events. The query can be refined to provide rooms based on any combination of the following:
  • Scheduled rooms in a particular building
  • Scheduled rooms on a particular floor of a building
  • Scheduled rooms on a particular day of the week/date/range of dates/time/range of times

Select the desired query from the Queries Tab in the Facility Database and select the DESIGN button. The query will be displayed in the Design View. The Design View is where the query can be modified to retrieve the desired population of data records.

Note:

• When using the semester field for the Room Scheduling Information data table, use "S1 not "SU".
• When using Room Scheduling Information data table (facility) use "SU" not "S1".

Using the Facility query as an working example, the group of returned rooms will meet the following criteria:

• Campus = UP
• Available rooms for the current semester
• Rooms have a particular technology (ITEC, STEC, VTEC or MTEC)

Locate the field campus enter UP (University Park campus)

Locate the field room_semester_end enter null (field is blank - no ending semester) on the next line down, for the same field, enter  >= 200102SP (ending semester is equal to or greater than Spring 2002)

Note:   

• These two entries are necessary for the first field due to the format of the data.  A facility record with no ending semester means the room is in effect indefinitely.  When an ending semester is entered for a facility record, the room is active until the end of that semester.  Both values are needed to ensure all possible records are checked.

Locate the field room_tech_code and enter ITEC (technology code for instructional technology)

Note:   

• The values entered for campus and tech_code need to be repeated on the second line for each of those fields because of the multiple values for the semester_end field. This guarantees the campus and tech_code values are the same for both of the values of semester_end.

Once the desired criteria has been entered, execute the query by clicking on the run icon ( ! ) or save the query, close down the query window and select the OPEN button from the QUERIES tab.  The executed query will return a list of records that meet the above criteria.

Using the Facility query again in design mode, the next example will return records that meet the following criteria:

• Campus = UP
• Rooms have a scheduling block on any of the three block options for 05/07/02 or greater

Locate the field campus and enter  UP (University Park campus)

Locate the field room_block_date_begin1 and enter the date desired. For purposes of this example, the date will be May 7, 2002, entered as   > 5/7/02   (Any room with scheduling blocks greater than 5/7/02)

Due to the facility records having up to three possible blocks listed, enter the same date criteria for fields  room_block_date_begin2 and  room_block_date_begin3   

Note: 

• Entering search criteria for Block-1, Block-2 and Block-3 on the same line will return only the records that have block information listed on all three block fields (Block-1 and Block-2 and Block-3).

Entering search criteria for Block-1, Block-2 and Block-3 on different lines will return the records that have block information listed on any of the three block fields (Block-1 or Block-2 or Block-3).  In this case, remember to repeat the campus code for each line that has room_block_date_begin  information entered on the query. This will ensure that all three lines of block information are pulled from the same campus.

Once the desired criteria has been entered, execute the query by selecting the run icon ( ! ) or save the query, close down the query window and select the OPEN button from the QUERIES tab.  The executed query will return a list of records that meet the above criteria.

Using the Event query as an example, return records that meet the following criteria:

• Campus = UP
• Rooms are scheduled for a particular date/range of dates on a particular floor of a particular building. For this example, search for Monday, 02/18/02, second floor of Willard building.

Locate the field campus and enter UP (University Park campus)

Locate the field event_scheduling_day and enter MON (three character abbreviation for day of the week)

Locate the field event_date_scheduled and enter null on the first line and enter the specific date to search for on the second line,   02/18/02.  Both values are needed due to the format of the data. Semester long scheduling records are written with one record for each day of the week the course/event meets. Courses/events that meet for part of the semester have one record written for date scheduled. The null entry will find the semester long records for the day of the week and the date entry is used to check for any records scheduled for the date entered.

Locate the field building_name and enter WILLARD (name of the building)

Locate the field room_number and enter  Like 2** (all rooms that begin with 2, meaning second floor)

Once the desired criteria has been entered, execute the query by selecting the run icon ( ! ) or save the query, close down the query window and select the OPEN button from the QUERIES tab.  The executed query will return a list of records that meet the above criteria.

Using the Events by Dept/Event Code query as an example, return records that meet the following criteria:

• Campus = UP
• Semester = 200102SP
• Event Code = Either the department that scheduled the event (ex: MATH) or a predefine event scheduling code

• Date event is scheduled (semester long records sort first)
• Begin time of event (24 hour time format)
• Building name
• Room number

Click on Download Here to download the MS Access database.