Query on Query - put results onto report?

[lorirobn]

Hi,

I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the first query
where Item is Null. I use a subreport for the details, and the
results display correctly.

However, the Report_Details event of the subreport is executed about 2
or 3 times more than I would expect (I think 3 times when I have a
footer, 2 times without). Since I think I've experienced this before,
I'm not sure if this is a problem or an Access thing.

Since the resulting report is fine, I could live with this. BUT, I
need running totals and a grand total, and these are inaccurate
because of the extra number of times the detail event is performed.

So, I created a third query, which provides totals based on Query 2.

My question is: how do I put a field on the subreport (report detail
line) using this query as its source???

Here's an example of the report.

Items Needed for Room Upgrade Report
Room 203
Item 1 Bedspread $10
Item 10 Gray Lamp $15
Item 21 Green Carpet $100
Room 203 Total $125
Room 205
Item 5 Artwork $40
....
....
Room 205 Total $200
....
All Rooms Grand Total $325

The 2 queries are:
Query 1 - selects the items IN the room. The SQL is (here's an
example with an actual room number):

SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
tblSpaceUse.RoomID
FROM tblSpaceUse INNER JOIN (tblDesignTypeItems INNER JOIN
tblRoomItems ON tblDesignTypeItems.ItemID = tblRoomItems.ItemID) ON
(tblSpaceUse.RoomID = tblRoomItems.RoomID) AND
(tblSpaceUse.DesignTypeID = tblDesignTypeItems.DesignType)
WHERE (((tblSpaceUse.RoomID)="340"));
 

Query 2 - gives items NOT in the room; references Query1, and where
Item Number is Null (this example uses specific codes for simplicity,
but user will select from lists).

SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
qryPrototypeMissingSumCost1.ItemID
FROM tblDesignTypeItems LEFT JOIN Query1 ON tblDesignTypeItems.ItemID
= Query1.ItemID
WHERE (((tblDesignTypeItems.DesignType)="c0501") AND ((Query1.ItemID)
Is Null));
 

Now I've added Query 3 to sum the Price of all items on query 2 for a
room. This will give the $10, $15, $100 price for each specific item
listed above. Otherwise I can't figure out how to get the item price
on this report, and I've been pulling my hair out.

Sooo... my main question is: how do I attach the results of Query 3
to the cost field on the detail line of my report? I have tried a
text field, a combo box... to no avail. They don't have "record
source".

Thank you so much! I'm in dire straits here and am posting to several
google MS Access groups.

Lori

[Barry Edmund Wright]

Hi Lori,

You don't make queries to sum information for a report. Just get the
query to display all the fields you want then use the query as the
recordsource for the report. In the report you do the sorts and totals
by using "Sorting and Grouping". In your case you will want to sort by
RoomID so in the Sorting and Grouping you choose "RoomID" and select
Group Footer = Yes. Then in the Group Footer on the report you create
a textbox and the the Control Source for that textbox will be
=Sum([ItemPrice] Substitute the field you used to hold the item's
price for "ItemPrice"

For an item total for all rooms you can copy the textbox into the
report footer, it will copy with the formula intact.

Control Source is found on the Data tab on the property sheet for the
textbox. If this isn't clear then do a search in this group for:
Report Sorting Group totals

Cheers,
Barry
On Jun 18, 8:23 am, lorirobn <[e-mail]:

Hi,

I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the first query
where Item is Null. I use a subreport for the details, and the
results display correctly.

However, the Report_Details event of the subreport is executed about 2
or 3 times more than I would expect (I think 3 times when I have a
footer, 2 times without). Since I think I've experienced this before,
I'm not sure if this is a problem or an Access thing.

Since the resulting report is fine, I could live with this. BUT, I
need running totals and a grand total, and these are inaccurate
because of the extra number of times the detail event is performed.

So, I created a third query, which provides totals based on Query 2.

My question is: how do I put a field on the subreport (report detail
line) using this query as its source???

Here's an example of the report.

Items Needed for Room Upgrade Report
Room 203
Item 1 Bedspread $10
Item 10 Gray Lamp $15
Item 21 Green Carpet $100
Room 203 Total $125
Room 205
Item 5 Artwork $40
...
...
Room 205 Total $200
...
All Rooms Grand Total $325

The 2 queries are:
Query 1 - selects the items IN the room. The SQL is (here's an
example with an actual room number):

SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
tblSpaceUse.RoomID
FROM tblSpaceUse INNER JOIN (tblDesignTypeItems INNER JOIN
tblRoomItems ON tblDesignTypeItems.ItemID = tblRoomItems.ItemID) ON
(tblSpaceUse.RoomID = tblRoomItems.RoomID) AND
(tblSpaceUse.DesignTypeID = tblDesignTypeItems.DesignType)
WHERE (((tblSpaceUse.RoomID)="340"));
 

Query 2 - gives items NOT in the room; references Query1, and where
Item Number is Null (this example uses specific codes for simplicity,
but user will select from lists).

SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
qryPrototypeMissingSumCost1.ItemID
FROM tblDesignTypeItems LEFT JOIN Query1 ON tblDesignTypeItems.ItemID
= Query1.ItemID
WHERE (((tblDesignTypeItems.DesignType)="c0501") AND ((Query1.ItemID)
Is Null));
 

Now I've added Query 3 to sum the Price of all items on query 2 for a
room. This will give the $10, $15, $100 price for each specific item
listed above. Otherwise I can't figure out how to get the item price
on this report, and I've been pulling my hair out.

Sooo... my main question is: how do I attach the results of Query 3
to the cost field on the detail line of my report? I have tried a
text field, a combo box... to no avail. They don't have "record
source".

Thank you so much! I'm in dire straits here and am posting to several
google MS Access groups.

Lori

[lorirobn]

Thank you sooooo much, Ken. This is brilliant. I have been working
on these queries for weeks, and have been so frustrated.
Thank you, Barry, too, for your explanation as well. Once I get this
query working correctly, then I will put in the grouping onto the
report.

I've spent this morning taking your advice, Ken, and following your
example of 1 query (I didn't know about the "not exists" option of the
WHERE clause!).
I've finally got it working, but now I need to add lookup tables for
item descriptions, if I am going to have it in one query. I am a
little stuck on this piece, as I need a "LEFT JOIN" on the lookups.
Can you help with that?

My SQL so far is (I am using a specific room, and will tweak that
later on):

SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
tblSpaceUse.RoomID, tblitems.ItemID, tblitems.Price,
tblitems.ItemCategoryID, tblitems.MakeID, tblitems.ItemSizeID,
tblitems.ItemSubcategoryID, tblitems.ItemColorID, tblitems.ItemDesc
FROM tblSpaceUse, tblDesignTypeItems, tblitems
WHERE (((tblDesignTypeItems.DesignType)=[tblspaceuse].[designtypeid])
AND ((tblSpaceUse.RoomID)="340") AND
((tblitems.ItemID)=[tbldesigntypeitems].[itemid]) AND ((Exists (Select
tblroomitems.roomid, tblroomitems.itemid
from tblroomitems
WHERE tblroomitems.itemid = tbldesigntypeitems.itemid
and tblroomitems.roomid = tblspaceuse.roomid))=False));

I want to add a left join to tlkpItemCategory (and some other
tables). How/where would I add this left join to this SQL?
By the way, I find the elimination of the INNER and LEFT joins a LOT
simpler to read and understand (as a former mainframe programmer).

Thanks again, Ken. I'm on my way to being done with this! (hopefully
SOON).
cheers,
Lori
Massachusetts, USA

[Barry Edmund Wright]

To Ken,
Ken, please post your reply to the group. I would like to read it too!
Barry

On Jun 19, 9:46 am, lorirobn <[e-mail]:

Thank you sooooo much, Ken. This is brilliant. I have been working
on these queries for weeks, and have been so frustrated.
Thank you, Barry, too, for your explanation as well. Once I get this
query working correctly, then I will put in the grouping onto the
report.

I've spent this morning taking your advice, Ken, and following your
example of 1 query (I didn't know about the "not exists" option of the
WHERE clause!).
I've finally got it working, but now I need to add lookup tables for
item descriptions, if I am going to have it in one query. I am a
little stuck on this piece, as I need a "LEFT JOIN" on the lookups.
Can you help with that?

My SQL so far is (I am using a specific room, and will tweak that
later on):

SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
tblSpaceUse.RoomID, tblitems.ItemID, tblitems.Price,
tblitems.ItemCategoryID, tblitems.MakeID, tblitems.ItemSizeID,
tblitems.ItemSubcategoryID, tblitems.ItemColorID, tblitems.ItemDesc
FROM tblSpaceUse, tblDesignTypeItems, tblitems
WHERE (((tblDesignTypeItems.DesignType)=[tblspaceuse].[designtypeid])
AND ((tblSpaceUse.RoomID)="340") AND
((tblitems.ItemID)=[tbldesigntypeitems].[itemid]) AND ((Exists (Select
tblroomitems.roomid, tblroomitems.itemid
from tblroomitems
WHERE tblroomitems.itemid = tbldesigntypeitems.itemid
and tblroomitems.roomid = tblspaceuse.roomid))=False));

I want to add a left join to tlkpItemCategory (and some other
tables). How/where would I add this left join to this SQL?
By the way, I find the elimination of the INNER and LEFT joins a LOT
simpler to read and understand (as a former mainframe programmer).

Thanks again, Ken. I'm on my way to being done with this! (hopefully
SOON).
cheers,
Lori
Massachusetts, USA

[lorirobn]

Barry - check out google groups forum microsoft.public.access.reports
- I had posted my question to 3 groups, and I guess somehow Ken's
response only showed up on that one (yours showed up on all).
The link is:
http://groups.google.com/group/micro...506403a255cc41

I am moving along on my reports... thanks!
hope this helps,
Lori