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

 

[Ken Sheridan]

Lori:

I'd have thought you could simplify this considerably by using a single
query with a subquery, and a grouped report rather than a subreport. You
don't say where the unit cost per item comes from, but I'm assuming that the
tblDesignTypeItems table has a column such as UnitCost. The query would be
along these lines:

SELECT ItemID, DesignType, UnitCost, RoomID
FROM tblDesignTypeItems, tblSpaceUse
WHERE NOT EXISTS
(SELECT *
FROM tblRoomItems
WHERE tblRoomItems.RoomID = tblSpaceUse.RoomID
AND tblRoomItems.ItemID = tblDesignTypeItems.ItemID);

The way it works is that the outer query returns the Cartesian Product of
the two tables, i.e. it joins every row in tblSpaceUse (which I assume has
one row per room) to every row in tblDesignTypeItems (which I assume has one
row per item). So the result would be a list of every room with every
possible item in each. This is restricted by the subquery however to show
only those rows where there is no row in tblRoomItems (which I take to be the
table modelling the many-to-many relationship between rooms and items) where
the RoomID and ItemID matches the values of those in the outer query's
current row. The result, therefore, should be a list of all rooms with, for
each room, only those items which the room does not contain; which I think is
what's required.

The report would then be grouped on RoomID with RoomID showing in the group
header and the other fields in the detail section. In the group footer you'd
have an unbound text box to show the total cost per room with a ControlSource
property of:

=Sum([UnitCost])

and an unbound text box in the report footer with exactly the same
ControlSource property to show the grand total.

If you wish to restrict the report to particular rooms and/or items at
runtime you can do so by means of the WhereCondition setting of the
OpenReport method, building the string expression for this from the user
input. As an example, here is some code which opens a report of those
employees selected by the user in a multi-select list box on a form:

Dim varItem As Variant
Dim strEmployeeIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstEmployees

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strEmployeeIDList = strEmployeeIDList & "," &
ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strEmployeeIDList = Mid(strEmployeeIDList, 2)

strCriteria = "[EmployeeID] In(" & strEmployeeIDList & ")"

DoCmd.OpenReport "[rptEmployees]", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No Employees Selected", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

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

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

 

[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/micr...ccfb1342c78/9a506403a255cc41#9a506403a255cc41

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