Using Cross Tab Queries a Date and Time Example

Question:

MS Access 2000 Database (ODBC connection to Oracle database) of emergency department patient data, I am attempting to return numbers of patients per hour for a 24 hour period; Time of patient attendance recorded as date/time field [PP_ARRIVAL_DATE], any assistance appreciated

Answer:

The easiest way to chart this would be as a Cross Tab Query.

Create a query based on your table; you will need to include the following fields:

Date of admission field;

Time of Admission field;

ID, UR, or similar field (something the query can count so it will have to be a field with data in every record).

Note: If the Date and Time fields are the same field, which by your question they may be, then add the same field twice, but call them different names, i.e. when you add them to the column, in the top row type a new name followed by a colon, then the field name.

Example:

DateOA: PP_ARRIVAL_DATE and

TimeOA: PP_ARRIVAL_DATE

Once you have added these fields in the DateOA field, change the format to shortdate. Do this by right clicking on the name of the column and selecting properties from the drop down list. Choose ‘ShortDate’ under the general tab, next to format.

Now make the Cross Tab Query.

Open the Query object section of the database and click on ‘New’. Choose ‘Cross Tab’ query from the options.

Window 1. Choose the query you just created.

Window 2. Select the Date field (DateOA) by double clicking on it. The admission data will be grouped by dates. Each date (grouped) will be displayed as a row name.

Window 3. Select the Time field (TimeOA) by double clicking on it. This causes the admission data to also be grouped by Time, these times will be column headings. This gives you a grid format which is the basis of the cross tab query.

Window 4. Choose the Date/Time option. (This groups by each individual time, not exactly what we want, but we will be changing it shortly.)

Window 5. Select your final field by clicking on it and select Count from the second column. This then counts how many admissions for a particular date, at a particular time.

Window 6. Name and Finish your query.

It will currently be displaying the wrong data (i.e., each individual time will be used as a column heading, we want the times to be grouped per hour so we can determine how many admissions per hour, for a specified date), but do not worry. To fix this:

Change to Design view.

In the Time field column change the row name to the following:

Hours: Format([TimeExamples],"hh")

changing the name TimeExamples to the name of your time field. By changing this format, the admissions are now grouped and counted by the hour.

Do you like this tip? Subscribe to my weekly Newsletter to receive tips weekly via email. Click Here to subscribe.