Help needed with Unmatched Query

[lorirobn]

Hi,

I have created an unmatched query (with the help of this forum) but need to tweak it and can't figure out how.

I needed to create 2 queries, and then match the results.
The first query joins 3 tables, the 2nd query has one table.

Here's an explanation of what I am doing. I have a main table, called tblSpaceUse, which contains records for all rooms in a building. All Items that can be in the rooms are on tblItems. Rooms and Items are linked by tblRoomItems.

I wanted the unmatched query to look at all possible subcategories of items (tlkpSubcategory, the table in the 2nd query) and tell me what is missing from tblRoomItems for EACH ROOM. I've gotten this to work for one room. But, when I eliminate room number from the first query, it tells me what subcategories are missing from the COMPLETE table tblRoomItems. I need this information on a room-by-room basis, not overall table. How would I go about doing so? (I could either use tblSpaceUse or tblRoomItems as my room-by-room 'driver').

Here is my sql.
First query:

SELECT tblRoomItems.RoomID, tblSpaceUse.RoomTypeID, tblItems.ItemSubcategoryID
FROM tblSpaceUse INNER JOIN (tblRoomItems INNER JOIN tblItems ON tblRoomItems.ItemID = tblItems.ItemID) ON tblSpaceUse.RoomID = tblRoomItems.RoomID
WHERE (((tblRoomItems.RoomID)="286"));

(I need tblSpaceUse in the query because I will need records by Room Type eventually).
Second query (this is the query I actually run):

SELECT tlkpItemSubcategory.ItemSubcatID, tlkpItemSubcategory.ItemSubcatDesc, tlkpItemSubcategory.ItemCategoryID, [#Unmatched1].RoomID
FROM tlkpItemSubcategory LEFT JOIN [#Unmatched1] ON tlkpItemSubcategory.ItemSubcatID = [#Unmatched1].ItemSubcategoryID
WHERE ((([#Unmatched1].ItemSubcategoryID) Is Null));

Also, room number column is showing up blank in my current unmatched query.

Would appreciate any suggestions on how to get roomid to show up, and how to do this query on a room-by-room basis (possibly one solution for both questions).

Thanks in advance. I am completely new to this type of query.

Lori

 

[Golom]

Just a question

Is the [#Unmatched1] that you are referencing in the second query, a reference to the first query that you presented? If not, what is [#Unmatched1]?

In the second query you have used the constraint

WHERE [#Unmatched1].ItemSubcategoryID Is Null

In a LEFT JOIN, that is equivalent to saying

"... where there is no matching record in [#Unmatched1]...".

Because of that, a reference to [#Unmatched1].RoomID in the select clause will be blank because no record was found so SQL supplies NULLs for the fields from [#Unmatched1].

 

[lorirobn]

Hi Golom,

Ohhhhh, so that explains why RoomID is null (and yes, #Unmatched1 is a reference to the first query I presented - sorry for not being more clear). I created the unmatched query using the Unmatched Query wizard, which put the IsNull clause in, but now I have a better of understanding why.

Is there a way I can get room number to be displayed? For instance, is there a way I could use tblSpaceUse as driver? (read through rooms from there, then lookup rooms/categories on tblRoomItems??). I'm pretty confused/clueless on how to work with this type of query.

Thanks for the quick response, and any other suggestions you might have,
Lori

 

[lespaul]

could you provide some sample data from the tables and the results you are expecting?


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]

OK. Here is sample data I will make up for simplicity sake.

tblSpaceUse:
RoomIDs
-------
286
287
288
289
290

tblItems:
ItemID Description Subcategory
50 Blue Tweed Carpet
51 Green couch Couch
52 Sage Paint
53 White Wicker Desk

tblRoomItems
RoomID Item
286 50
286 53
287 50
288 50...

tlkpSubcategory
Subcategory
CARPET
COUCH
DESK
PAINT


Room 286, on tblRoomItems, has Carpet and a Desk, but not Couch or Paint. I would like to see Couch and Paint, then, on my query results. The same for all rooms.

Example of Query Results:
Items Missing:
Subcategory RoomID
Couch 286
Paint 286
etc... 287

Hope this helps.