Need help with query JOIN and COUNTS

[lorirobn]

Hi All,

I am wondering how to do this particular query.

I want to include 5 tables: Space Use, Room Items, Category, Subcategory, Items.

What I'd like to do is:
cycle through each Room in the building (using table Space Use as main driver for rooms), and create a query that will give me each Room Item, which I've done, but what I haven't been able to figure out is -

How can I know what category/subcategory combination has NO items on table RoomItems? And also what category/subcategory combination has more than ONE item on table RoomItems? Is this a matter of changing JOIN statements, and/or including COUNTS?

I am guessing there must be a way, but I am clueless as to how to go about doing so.

I did create a query, without Space Use table, but it only lists Room Items. Hopefully it will explain my table structure. (not the real table names, for simplicity sake). The tblItems table links category, subcategory, and room items. This query does not list Category/Subcategory combination that has no Room Items, or give the counts I want.

SELECT Category.CategoryID, Subcategory.SubcatID, RoomItems.ItemID, RoomItems.RoomID
FROM (Category INNER JOIN Subcategory ON Category.CategoryID = Subcategory.CategoryID) INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON (Subcategory.SubcatID = tblItems.SubcategoryID) AND (Category.CategoryID = tblItems.CategoryID)
ORDER BY tblRoomItems.RoomID,  Category.CategoryID, Subcategory.SubcatID;

Thanks in advance,
Lori

 

[lorirobn]

PS - I have added the table 'Space Use' to my query. Now all I need to know is how to include Category/Subcategory rows that do not have RoomItems, and also the count of Category/Subcategory within each Space Use record. Can all this be done on one query?
My new query is:
(have not changed table names this time)

SELECT tblSpaceUse.RoomID, tlkpItemCategory.ItemCategoryID, tlkpItemSubcategory.ItemSubcatID, tblRoomItems.ItemID, tblRoomItems.RoomID
FROM tblSpaceUse INNER JOIN ((tlkpItemCategory INNER JOIN tlkpItemSubcategory ON tlkpItemCategory.ItemCategoryID = tlkpItemSubcategory.ItemCategoryID) INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON (tlkpItemCategory.ItemCategoryID = tblItems.ItemCategoryID) AND (tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID)) ON tblSpaceUse.RoomID = tblRoomItems.RoomID
ORDER BY tblRoomItems.RoomID, tlkpItemCategory.ItemCategoryID, tlkpItemSubcategory.ItemSubcatID;

Thanks in advance!

 

[jedraw]

Could you provide some sample data or a list of columns in you tables? And specifics of the queries you want to do.
Thnx.

 

[lorirobn]

Hi...sure, here's some sample data.

tblSpaceUse has RoomID = 286
(room 286 is a room in this building).

Item Category Lookup Table (tlkpItemCategory) has Category = Furniture.

Item Subcategory Lookup Table (tlkpItemSubcat) has Subcategory = Nightstand.

tblRoomItems, which has rows for all items in all rooms, has two records for Nightstand. "2-drawer white wicker" (this would be room 286, category Furniture and subcategory Nightstand).

so on my report, I would like to see a line that says:
Room 286, Furniture, Nightstand, 2-drawer white wicker, Qty: 2.

Next subcategory would be, let's say, Sofa. Still on Furniture, room 286. This room has no sofa, so I would then see:
Room 286, Furniture, Sofa, Qty: 0.

After all subcategories are looped through, processing would be on the next category, let's say, Textiles. Loop through the subcategories for Textiles for room 286, and report counts.

After all categories are looped through for room 286, the next tblSpaceUse record would be processed, Room 287.

And so on...

I hope this is clear. Please keep in mind that I have always been a mainframe programmer, thus I think in terms of 'loops' when thinking of processing.

Thanks, and I hope this description helped.