473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Query on Query - put results onto report?

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):
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
  2. tblSpaceUse.RoomID
  3. FROM tblSpaceUse INNER JOIN (tblDesignTypeItems INNER JOIN
  4. tblRoomItems ON tblDesignTypeItems.ItemID = tblRoomItems.ItemID) ON
  5. (tblSpaceUse.RoomID = tblRoomItems.RoomID) AND
  6. (tblSpaceUse.DesignTypeID = tblDesignTypeItems.DesignType)
  7. WHERE (((tblSpaceUse.RoomID)="340"));
  8.  
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
  2. qryPrototypeMissingSumCost1.ItemID
  3. FROM tblDesignTypeItems LEFT JOIN Query1 ON tblDesignTypeItems.ItemID
  4. = Query1.ItemID
  5. WHERE (((tblDesignTypeItems.DesignType)="c0501") AND ((Query1.ItemID)
  6. Is Null));
  7.  
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

Jun 18 '07 #1
4 3109
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 <lorir...@yahoo.comwrote:
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):
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
  2. tblSpaceUse.RoomID
  3. FROM tblSpaceUse INNER JOIN (tblDesignTypeItems INNER JOIN
  4. tblRoomItems ON tblDesignTypeItems.ItemID = tblRoomItems.ItemID) ON
  5. (tblSpaceUse.RoomID = tblRoomItems.RoomID) AND
  6. (tblSpaceUse.DesignTypeID = tblDesignTypeItems.DesignType)
  7. WHERE (((tblSpaceUse.RoomID)="340"));
  8.  
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDesignTypeItems.DesignType, tblDesignTypeItems.ItemID,
  2. qryPrototypeMissingSumCost1.ItemID
  3. FROM tblDesignTypeItems LEFT JOIN Query1 ON tblDesignTypeItems.ItemID
  4. = Query1.ItemID
  5. WHERE (((tblDesignTypeItems.DesignType)="c0501") AND ((Query1.ItemID)
  6. Is Null));
  7.  
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

Jun 19 '07 #2
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
Jun 19 '07 #3
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 <lorir...@yahoo.comwrote:
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

Jun 19 '07 #4
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
Jun 19 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Phin | last post by:
I need your HELP! I've seen all the posts on using Crystal Reports within vs.net (vb.net) and changing a SQL query at runtime. When I tried to pass in a dataset into the crystal report at...
6
by: B Love | last post by:
I am wondering if I can have a dynamically generated report based upon the results of a query (in Access2000). Any ideas? Related to that (if that cannot be done) can the output of a query...
4
by: Richard Hollenbeck | last post by:
The following query takes about one second to execute with less than 1,000 records, but the report that's based on it takes from 15-30 seconds to format and display. That's frustrating for both me...
3
by: Chuck | last post by:
Hi, I have a somewhat unique problem (at least unique to me, and I've been doing this for longer than I care to admit). I have a client that needs to print cards onto perforated card stock (so...
2
by: jasenpeters | last post by:
I'm not sure I understand the use of parameter queries and reports. I have a query that returns calculations for Categories. There are 6 Categories (A,B,C,D,E,F). There are also two Types of...
4
by: dancole42 | last post by:
So I have an invoicing database based on two main forms: Orders and OrderLines. Orders has fields like: OrderID BillingMethod OrderDate CreditCard CCExp OrdSubTotal ShippingCharge
5
by: deaconj999 | last post by:
Hi, I have nearly finished my database and I would like to add a query that uses a combo box to get the results, not the usual paramater style input. I suppose it would need a form and a query...
0
by: RCapps | last post by:
When running the below SQL Query I keep getting the following error: Server: Msg 4924, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table...
0
by: Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+ | last post by:
I am running Access 2007. I have a report that I want to filter. I can go into Advanced...Advanced Filter/Sort... and setup a filter that works fine on the report when I apply it. When in this...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.