Count rooms in my form

[Maarkr]

I have a 'hotel' reservation db (for troops) that I can assign and track
rooms and beds. So one room may be a single, one may be a double, or one
room may have 20 beds (I also deconflicted male/females). I finally got a
query as the recordsource for my form to return the proper records for the
current day. I now need to count the beds and rooms assigned from the
records returned to see if there is any space left for others. Since each
record accounts for a bed assigned to somone, I just had a textbox souce
=Count([roomID]) to give me the total beds assigned for the day. How do I
count the rooms assigned for the day? I may have 1 or many people in a room;
so room 201-1 person, 202-2 persons, 300-5 persons... returns 8 records
(beds), but how can I count the different rooms, 3 in this case? Give me
some ideas to get me going, or I can repost with the recordsource query.

 

[Rob Parker]

I assume that you want to display this in a textbox in your form's header or
footer. It's unlikely that you'll be able to do this in/from your existing
query which is the form's recordsource; you'll need a separate query -
either a Select Distinct query or a Totals (Group By) query. You'll then
display the count from that query using a dCount expression.

For example, you could save the following as qryRoomsUsed:
SELECT DISTINCT roomID FROM YourRecordsourceQueryName;
and then place the following expression in an unbound textbox:
=dCount("*","qryRoomsUsed")

If you're using a Date field to filter your records (perhaps from another
control on your form), you'll need that field in the new query. You could
set the criteria in the query itself using an expression such as
[Forms].[YourFormName].[YourDateControlName], or you could apply the
criteria in the dCount expression, which would become something like:
=dCount("*","qryRoomsUsed","YourDateFieldName = #" & YourDateControlName
& "#")

HTH,

Rob