109
4.4 Aggregate query - Group By
If we want to use the stay list for the real user interface,
we still lack something: the arrival date and the room
number. In order to get this data, we have to include
data from tblRoomState:
arrival = first date among the room states.
room = first roomID among the room states.
When there is more than one room,
show only the text "more".
We will first compute an extended version of the stay
table with these data added to each stay. The small da-
tasheet to the right in Figure 4.4A outlines the desired
result. In order to do this, we have to bundle all room
states of the stay into one, leaving only the arrival date
and the room indication. This is an example of an ag-
gregate query. Here is how to do it:
1. From the database window, create a new query in
design mode. Include tblRoomState and tblStay.
(See top of Figure 4.4A.)
2. Drag these fields to the grid: stayID , guestID, state
(from tblStay) , date and roomID . You now have
an ordinary inner join based on the criterion
tblRoomState.stayID=tblStay.stayID
3. Change to datasheet view. You should now see a
table like the one at the lower left of Figure 4.4A.
It contains a record for each of the room states -
with some of the stay fields added.
Find the smallest date and roomID. We want to
combine all records with the same stayID into one, as
shown on the figure. For instance we have four records
with stayID = 727. They make up a bundle of records.
All the records in the bundle have the same stayID, but
they have different dates and room numbers. We want
to compress this bundle into one record - the one
shown to the right.
4. Click the sum symbol on the tool bar (
Σ
) or right-
click in the grid to find the
Σ
. You will now see a
new row in the grid, Total.
Initially, Access shows Group By everywhere in the
total-line. We want to group records by stayID. For
date and roomID we want to find the lowest value in
each group:
5. Change the total-setting for the date column to
Min. Do the same for the roomID column. If you
now change to datasheet view, you should see a
shorter table, somewhat like the one to the right.
The heading for the date column now says MinOfDate
and it correctly shows the first date for this stay. The
roomID column is similar.
You may wonder why we have left guestID and state
as Group By. Actually, when two records have the
same stayID, they also have the same guestID and
state, because these columns are fields from the stay
table. So grouping by them is alright. You may try to
change the setting for guestID to Min rather than
Group By. It makes no change in the result since all
records in the bundle have the same stayID and thus
the same guestID.
6. Save the query and give it the name qryStay-
Arrival.
Alias - renaming a column. At this stage the arrival
date is computed correctly, but we would like a differ-
ent name (an alias) for the column:
7. Open the query in design view. In the grid, change
the heading of the date column to arrival: date.
This causes Access to compute the first date as be-
fore, but give the result the name arrival.
Computed field. The last thing missing is that the first
roomID may be confusing to the user if there is more
than one room in the stay. It is particularly confusing if
John Simpson first stays in room 12, then moves to
room 11. The stay list would then indicate room 11 and
the receptionist might by mistake give him the key for
room 11 when he arrives. This is why we want to show
"more" if the stay involves more than one room. The
receptionist will then have to open the stay window to
see which rooms and when.
In order to find out whether there are more than one
room in the stay, we compute the Min and Max of
roomID and compare them:
8. Add another roomID column in the grid and let it
compute Max rather than Min (Figure 4.4A). Re-
name the two roomID columns to A and B as
shown.
9.
Use a blank column - no dragging of fields. In the
total line, indicate that it is an Expression (a com-
puted value). In the top line specify this expres-
sion:
room: IIf(A=B, A, "more")
Warning: Depending on the regional settings of
your computer, you may have to use semicolons
instead of commas (see more in section 6.6):
room: IIf(A=B; A; "more")
This expression says that we want a new field called
room. If column A (the smallest roomID) is equal to
columnB (the largest roomID), then the result shown
must be this roomID. Otherwise the result must be the
text "more". The operator IIf is called an immediate if.
See the result in datasheet mode. It should be as shown
at the lower right of Figure 4.4A. The room column is
what we need for the stay list.
Also have a look at the SQL-version:
58
4. Queries - computed tables
