Query for reports (1 Viewer)

slharman1

Member
Local time
Today, 09:43
Joined
Mar 8, 2021
Messages
467
Do you “throw” everything possible in your query for a report or use multiple sub-reports? My report has the main report , from orders table, also, order details, and order detail accessories, so far so good but each order detail AND accessories group will have a sub report to show the custom fabricated shop materials and labor time spent on them.
I hope this is enough info. Thanks
 

June7

AWF VIP
Local time
Today, 06:43
Joined
Mar 9, 2014
Messages
5,423
Not really "enough". Whether or not you "throw" everything into a single query depends on the data and what you want to present. Sometimes subreports are essential to the correct presentation of output.
 

slharman1

Member
Local time
Today, 09:43
Joined
Mar 8, 2021
Messages
467
Not really "enough". Whether or not you "throw" everything into a single query depends on the data and what you want to present. Sometimes subreports are essential to the correct presentation of output.
Thanks, I am in the process of laying out the report so I will know how I want the data presented. Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:43
Joined
Oct 29, 2018
Messages
21,358
And even if you throw everything in one query, that wouldn't stop you from still using subreports based on the same query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 19, 2002
Messages
42,970
If you want subtotals, it is best to use subreports rather than a single query that joins the 1-side table to the many-side table.
 

June7

AWF VIP
Local time
Today, 06:43
Joined
Mar 9, 2014
Messages
5,423
Subtotals can be achieved without subreport by using Sorting & Grouping with aggregate calcs in textboxes, as long as there is not more than one "many side" table involved.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 19, 2002
Messages
42,970
Yes, June, they can.

So you join the order to the order details and you want to sum/avg the shipping amount. How's that going to work for you if you are not using a subform for the details?
 

slharman1

Member
Local time
Today, 09:43
Joined
Mar 8, 2021
Messages
467
If you want subtotals, it is best to use subreports rather than a single query that joins the 1-side table to the many-side table.
Ok here is where it gets complicated. Each of the order detail records AND each of the accessories records will have a sub report that shows the time spent in the shop for labor and material. The order details and accessories just need to list the name and price. The shop times are from a related table, this table relates to both details and accessories.
What would you suggest for this?
 

June7

AWF VIP
Local time
Today, 06:43
Joined
Mar 9, 2014
Messages
5,423
Perhaps you should provide db for analysis. Follow instructions at bottom of my post.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 19, 2002
Messages
42,970
I'm having trouble determining if the subreports are hierarchical or siblings.. Is orderDetailAccessories related to OrderDetails or to Orders? So, is the structure:

Orders-->Details-->Accessories

OR

Orders --> Details
Orders --> Accessories
 

slharman1

Member
Local time
Today, 09:43
Joined
Mar 8, 2021
Messages
467
I'm having trouble determining if the subreports are hierarchical or siblings.. Is orderDetailAccessories related to OrderDetails or to Orders? So, is the structure:

Orders-->Details-->Accessories

OR

Orders --> Details
Orders --> Accessories
Orders-->Details-->Accessories
1 detail can have many or none accessories.
They both pull from the same shop detail table for their price because a custom fabricated part can be either a detail or accessory.
The accessory adds to the detail price for its sell price.
Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 19, 2002
Messages
42,970
So, when a detail has accessories, why isn't the price of the accessories rolled up into the price for the detail? Won't the sum of the price be out of whack if you get both?

To get the price, it seems like you just need a left join from details to shopdetail table as the recordsource for the details subform and a left join from accessories to shop detail for the accessories subsubform.
 

slharman1

Member
Local time
Today, 09:43
Joined
Mar 8, 2021
Messages
467
So, when a detail has accessories, why isn't the price of the accessories rolled up into the price for the detail? Won't the sum of the price be out of whack if you get both?

To get the price, it seems like you just need a left join from details to shopdetail table as the recordsource for the details subform and a left join from accessories to shop detail for the accessories subsubform.
Oops sorry the detail is rolled up on the order form so the customer doesn’t see the cost of the accessory.
I would like the price on this report to roll up as well for so we can just look at the sale price of the detail.
I really only need the accessory to show on the report so during analysis of the estimated time to actual time spent in the shop, the owner will know that the time includes the order detail item and all of the accessories involved in it. So since the time is kept in the system for the order detail along with the accessories (as if they were all one item) I also need to add up all of the time spent on both of the parts to get to the total estimated time. (Which is not a problem for me to do) with all of this discussion, I am starting to think I need to remove my grouping level for the accessories and just list them below the order detail item with the totals hours for both added and shown below each order detail item.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 19, 2002
Messages
42,970
Do whatever you want. If you use a single report bound t a query that joins to the two child tables, you have to be very careful with what you are summing if either of the two higher levels has an amount field the sum, you will be multiplying that sum by the number of child records. I use subreports to avoid summation issues.

The example I used above was shipping cost on an order. Shipping is stored at the order level but if you join to the order details, each order header is going to be repeated for each order detail. If the shipping amount for the whole order is 10 and you have 5 items, the sum of the shipping cost will be 50 so you won't bet an accurate sum of the shipping amount if you have a report that includes multiple orders that you want totaled.
 

slharman1

Member
Local time
Today, 09:43
Joined
Mar 8, 2021
Messages
467
Do whatever you want. If you use a single report bound t a query that joins to the two child tables, you have to be very careful with what you are summing if either of the two higher levels has an amount field the sum, you will be multiplying that sum by the number of child records. I use subreports to avoid summation issues.

The example I used above was shipping cost on an order. Shipping is stored at the order level but if you join to the order details, each order header is going to be repeated for each order detail. If the shipping amount for the whole order is 10 and you have 5 items, the sum of the shipping cost will be 50 so you won't bet an accurate sum of the shipping amount if you have a report that includes multiple orders that you want totaled.
Got it. ThanksPat
 

Users who are viewing this thread

Top Bottom