Add details to aggregate query?

[lorirobn]

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 Location, Floor,
and View.
For Example:
Room Item Sum View Location Floor
140 $1200 Back SW 1
210 $800 Front NW 2
305 $1100 Back SW 3

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!

(I am posting this onto microsoft.public.access and comp.databases.ms-
access.)

Thanks!
Lori

 

[Guest]

Can you create a third query that uses the first two as input?

 

[lorirobn]

Thanks, I got my answer via another forum, and was able to put it all
into one query.