Beginner Total Query Question

[Guest]

I have a table with two fields. .... DATE and ROOM.

I would like to total the number of hospital rooms completed on each
floor on a specified date. I need to generate one query which gives the
total number of rooms completed on EACH floor on a specified date. For
example the SQL view I have included here should give me a total ,( not an
indivdual count) of 5 rooms completed on the 6 North floor for the month of
august. Any help would be greatly appreciated as I am just learning.

SELECT Count([Log table].Log) AS [6 North], [Log table].Date
FROM [Log table]
WHERE ((([Log table].Room) Between "603" And "641"))
GROUP BY [Log table].Date;

 

[Roger Carlson]

You said there are two fields, but I see 3: Date, Room, and Log.

The best way to do this would be to have a "Floor" field as well. Then your
SQL would look something like this:
SELECT Count([Log table].Room) AS RoomCount, [Log table].Date, [Log
table].Floor
FROM [Log table]
GROUP BY [Log table].Date,[Log table].Floor;

I don't really see a way to do it based on your existing table. Perhaps
someone else does.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thank you for the speedy reply.