Query on Query - use result on report 1

[ITlori]

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 a field on the detail line of my report? I have tried a text field, a combo box... to no avail.

Thank you so much! I'm in dire straits here....

Lori

 

[lameid]

You can put the sum of a field over a section by using the sum function.

So if I wanted to sum the ItemQtyPrice field I would have a textbox with the control source of...

= Sum([ItemQtyPrice])

I would put one in each footer that I wanted to show the sum for.

Beyond that you want to put the value you need to sum in your report somehow. You have a dollars field in your report example but nothing that looks like it in query1 or query2. And you say that query3 is not used in the report. Seems like you need to join in a field to your report's recordsource query but I don't know the schema and have no idea what query3 does.

To answer your brief question...

how do I attach the results of Query 3 to a field on the detail line of my report? I have tried a text field, a combo box... to no avail.

You could use dlookup. But again, I think you just want to add it to your query.

 

[dhookom]

I have created hundreds of reports and never used code in the On Format or On Print events to calculate totals or running sums. It is just too easy to create running sums by section or over all. Aggregate calculations in footers and headers are also simple.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]