How to total the Count (grand total)

[lorirobn]

Hi,

I have a query in which I use 'Count' to give me the number of rows returned for a particular data type. Now I would like to add grand total at the end, summing these counts.

For example, my query returns the following data:

Room Num Bed Size Bed Count
200 Twin 2
201 Double 1
202 Queen 1
203 Twin 10

The Bed Count column is from using 'Count' in my query, and grouping items together (for example, for Twin, there were actually 2 records and 10 records, respectively). I'd like to add 'Grand Total' now, which would be 14 in this case.

Any suggestions on how I can do this? I'd like to add it to the existing query, if possible, which I have coded within my VBA module. So far I have not been able to figure out how to add grand total to this.

In case it helps, the existing query is as follows (it is long because it joins several tables):

SELECT tblRoomItems.RoomID AS Room, tlkpSize.SizeID AS [Bed Size], Count(tblRoomItems.RoomID) AS [Bed Count], tblRoomItems.ConditionID AS Cond, tblSpaceUse.RoomName AS [Room Name], tblSpaceUse.RoomTypeID AS [Room Type], tblSpaceUse.RoomLocation AS Location, tblSpaceUse.HousingGuestracClassif AS [Guestrac Code]
FROM tblSpaceUse INNER JOIN (tlkpItemSubcategory INNER JOIN (tlkpSize INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON tlkpSize.SizeID = tblItems.ItemSizeID) ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID) ON tblSpaceUse.RoomID = tblRoomItems.RoomID
WHERE (((tlkpItemSubcategory.ItemSubcatID)='bed'))
GROUP BY tblRoomItems.RoomID, tlkpSize.SizeID, tblRoomItems.ConditionID, tblSpaceUse.RoomName, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomLocation, tblSpaceUse.HousingGuestracClassif
ORDER BY tlkpSize.SizeID, tblRoomItems.RoomID;

Thanks in advance for any help.

 

[lespaul]

easiest way is to add a totals to the end, but your query has more select fields than your example. Maybe something like will work:

SELECT tblRoomItems.RoomID AS Room, tlkpSize.SizeID AS [Bed Size], Count(tblRoomItems.RoomID) AS [Bed Count], tblRoomItems.ConditionID AS Cond, tblSpaceUse.RoomName AS [Room Name], tblSpaceUse.RoomTypeID AS [Room Type], tblSpaceUse.RoomLocation AS Location, tblSpaceUse.HousingGuestracClassif AS [Guestrac Code]
FROM tblSpaceUse INNER JOIN (tlkpItemSubcategory INNER JOIN (tlkpSize INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON tlkpSize.SizeID = tblItems.ItemSizeID) ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID) ON tblSpaceUse.RoomID = tblRoomItems.RoomID
WHERE (((tlkpItemSubcategory.ItemSubcatID)='bed'))
GROUP BY tblRoomItems.RoomID, tlkpSize.SizeID, tblRoomItems.ConditionID, tblSpaceUse.RoomName, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomLocation, tblSpaceUse.HousingGuestracClassif
UNION
SELECT "", "Total", Count(*), "", "", "", "", ""
FROM tblSpaceUse INNER JOIN (tlkpItemSubcategory INNER JOIN (tlkpSize INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON tlkpSize.SizeID = tblItems.ItemSizeID) ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID) ON tblSpaceUse.RoomID = tblRoomItems.RoomID
WHERE (((tlkpItemSubcategory.ItemSubcatID)='bed'))
GROUP BY tblRoomItems.RoomID, tlkpSize.SizeID, tblRoomItems.ConditionID, tblSpaceUse.RoomName, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomLocation, tblSpaceUse.HousingGuestracClassif
ORDER BY tlkpSize.SizeID, tblRoomItems.RoomID;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lorirobn]

Thanks, Leslie.

I am going to try that.
If I want to do it programmatically instead, and add each count to a running total, how/where would I do that? I have a form that uses this SQL query in both its Form_Load event, and when a command button is pressed. I'm not sure where I'd add a counter...I'd just need to add 1 for each row returned from the select (or have a running total for my original Bed Count).

Thanks for any suggestions.

 

[Golom]

Ugly as the SQL is, it's better than the procedural (i.e. recordset) alternative.

You can do it using something called an ADO disconnected recordset but more prosaic DAO would require that you have a field in a table that you can update. Once you do that of course then you face the much larger problem of keeping it updated whenever you add, delete or change the underlying data.

Leslie
I don't believe that the second SELECT in the UNION should have a GROUP BY clause since it's totaling ALL records.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

true enough, that's just ONE of the problems with using cut & paste, huh!

les