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.
Thanks in advance,
Lori
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.
Code:
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