Add details to aggregate query...

[ITlori]

Hi,

I have a report that displays summary information, summing prices for all records for a RoomID meeting certain criteria, and printing the roomID and sum on a detail line. Now I want to add lookup-table info onto the detail line.

The report’s query determines what items are missing for a Room’s Design Type, and it’s pretty complicated for me, using an EXISTS and matching on NULLS.

What I want to know is how I can add the LEFT JOIN I need for my lookup tables to this query. Or do I need two queries or maybe there’s another way to do the aggregate info and then the lookup info??

Currently I have only Room and Sum, but I want to add descriptions, like Location, and View.
For Example:
Room Item Sum View Location RoomType
140 $1200 Back Southwest Guest
210 $800 Lake North Guest
305 $1100 Back Southwest Guest

total..... $3100

If it would help, here is my query (note: I plugged in a specific room, just for this example):

SELECT Sum(Price), tblSpaceUse.RoomID
FROM tblSpaceUse, tbldesigntypeitems, tblitems
WHERE (((tbldesigntypeitems.DesignType)=[tblspaceuse].[designtypeid]) AND ((tblitems.ItemID)=[tbldesigntypeitems].[itemid]) AND (([tblSpaceUse.RoomID]) In ('340')) AND ((Exists (Select tblroomitems.roomid, tblroomitems.itemid  From tblroomitems  WHERE tblroomitems.ItemID = tbldesigntypeitems.ItemID  and tblroomitems.roomid = tblspaceuse.roomid))=False))
GROUP BY tblSpaceUse.RoomID;

The query I'd like to incorporate is:

SELECT tblSpaceUse.RoomID, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomName, tblSpaceUse.RoomLocation, tlkpRoomCategory.RoomCategoryDesc, tlkpRoomType.RoomTypeDesc, tlkpDesignType.DesignDescription
FROM ((tblSpaceUse INNER JOIN tlkpRoomCategory ON tblSpaceUse.RoomCategoryID = tlkpRoomCategory.RoomCategoryID) LEFT JOIN tlkpRoomType ON tblSpaceUse.RoomTypeID = tlkpRoomType.RoomTypeID) INNER JOIN tlkpDesignType ON tblSpaceUse.DesignTypeID = tlkpDesignType.DesignType
WHERE (((tblSpaceUse.RoomID)="340"));

I tried incorporating the 2nd query into the first, but haven't had much luck.

Any help greatly appreciated!

Thanks!
Lori

 

[lameid]

The first thing I had to do for my sake was to rewrite your first query putting the join criteria in the From clause and not in your where clause. I also added the fields you want to add from your second query to this query.

SELECT Sum(Price), tblSpaceUse.RoomID, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomName, tblSpaceUse.RoomLocation

FROM tblSpaceUse Inner Join tbldesigntypeitems ON  tbldesigntypeitems.DesignType = [tblspaceuse].[designtypeid]
Inner Join tblitems ON tblitems.ItemID = tbldesigntypeitems].[itemid]

WHERE [tblSpaceUse.RoomID] In ("340") 
 AND Not Exists 
  (Select tblroomitems.roomid, tblroomitems.itemid 
  From tblroomitems 
  WHERE tblroomitems.ItemID = tbldesigntypeitems.ItemID AND
  tblroomitems.roomid = tblspaceuse.roomid)

GROUP BY tblSpaceUse.RoomID, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomName, tblSpaceUse.RoomLocation;

Based on the apparent Foreign keys in your second query I am going to assume that the added joins will not add undesired repeating elements. That said you only need to add the additional tables to your first query with appropriate joins and group by the added fields as well...

SELECT Sum(Price), tblSpaceUse.RoomID, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomName, tblSpaceUse.RoomLocation, tlkpRoomCategory.RoomCategoryDesc, tlkpRoomType.RoomTypeDesc, tlkpDesignType.DesignDescription

FROM tblSpaceUse Inner Join tbldesigntypeitems ON  tbldesigntypeitems.DesignType = [tblspaceuse].[designtypeid]
Inner Join tblitems ON tblitems.ItemID = tbldesigntypeitems].[itemid] INNER JOIN tlkpRoomCategory ON tblSpaceUse.RoomCategoryID = tlkpRoomCategory.RoomCategoryID INNER JOIN tlkpDesignType ON tblSpaceUse.DesignTypeID = tlkpDesignType.DesignType LEFT JOIN tlkpRoomType ON tblSpaceUse.RoomTypeID = tlkpRoomType.RoomTypeID

WHERE [tblSpaceUse.RoomID] In ("340") 
 AND Not Exists 
  (Select tblroomitems.roomid, tblroomitems.itemid 
  From tblroomitems 
  WHERE tblroomitems.ItemID = tbldesigntypeitems.ItemID AND
  tblroomitems.roomid = tblspaceuse.roomid)

GROUP BY tblSpaceUse.RoomID, tblSpaceUse.RoomTypeID, tblSpaceUse.RoomName, tblSpaceUse.RoomLocation, tlkpRoomCategory.RoomCategoryDesc, tlkpRoomType.RoomTypeDesc, tlkpDesignType.DesignDescription;

 

[ITlori]

Hi Lameid,
This is great. I did not know about grouping for multiple fields that way.
In using your SQL with the Inner Joins, I am struggling with where to put the parentheses! (now I know why I resist using Joins!). I am getting all sorts of syntax errors.
Help greatly appreciated!
Lori

 

[lameid]

I took out parenthesis because I did not think they should be necessary and I find them hard to read when you get a bunch of nested parenthesis... can you switch a query to design view using the first SQL statement I provided? What is the error message?

If you need parenthesis I would think adding them around the criteria piece of the join or the on segment...

I.e.

tblSpaceUse Inner Join tbldesigntypeitems ON (tbldesigntypeitems.DesignType = [tblspaceuse].[designtypeid])

 

[PHV]

The JetSQL syntax is:
FROM [highlight]([/highlight][!]([/!]table1
INNER JOIN table2 ON ....[!])[/!]
INNER JOIN table3 ON ....[highlight])[/highlight]
INNER JOIN table4 ON ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ITlori]

YES! Thank you, I've got it working now. What's more, I actually understand what I did!
PHV, thank you for answering succinctly what I was trying to figure out all morning.
Lameid, thank you for helping me to understand how to do this query...
very much appreciated.

 

[lameid]

Your welcome. I'm just glad you figured it out before I got around to a follow up post. I never noticed the parenthesis requirement in the from clause before. I honestly usually develop the entire from clause if not the whole query in the Query designer when using Access (SQL server and ANSI-92 SQL do not require the Parenthesis which lead to my error). Thanks PHV!