How to report multiple value totals based on specific criteria in a report

[nick404]

Hello,
I am making a report and need to have two separate totals in the report. What it is is a database that tracks the yardage of material rolled by two different winders. For simplicity let's just say winder 1 and winder 2. in the report I need to have two distinct totals of yardage consumed, one total for winder 1 and one total for winder 2. However, I would still like to display my other fields (RollNumber, YardsConsumed, WinderNumber, EmployeeNumber, Shift, and EntryDate) in the report. I have made two separate queries: one that finds the information I need for winder 1 and one that finds the same information for winder 2. Any help is greatly appreciated!

[ItsMe]

I would suspect a normalized data structure would allow for both winders to be in a single query. Grouping and Sorting is an ideal tool for getting summaries and calcs.

[nick404]

However is there a way to get a total yardage for each winder to appear in the same report?

[nick404]

AH I used the "group on" option to achieve this! Thanks for the help!

[ItsMe]

... I have made two separate queries: one that finds the information I need for winder 1 and one that finds the same information for winder 2...

There should be a way to get what you need in a single report. However, the fact that you are creating two queries, one for each winder, suggests your data structure is not normalized. If the data structure is not normalized, the answer is no.

Perhaps posting the SQL for one of the two queries here will help readers understand your data structure.

[ItsMe]

AH I used the "group on" option to achieve this! Thanks for the help!

OK, I did not see this post earlier. Let us know if you get stuck again. It would not hurt to post that SQL though...

[nick404]

Here's the SQL.... I did have one query that searched everything, the reason I made two separate ones in addition is because I thought that maybe I could use those two other queries in the report but that was proving too difficult for me.
SQL for the main query:
SELECT RollConsumption.RollNumber, RollConsumption.YardsConsumed, RollConsumption.WinderNumber, RollConsumption.EmployeeNumber, RollConsumption.Shift, RollConsumption.EntryDateFROM RollConsumption
WHERE (((RollConsumption.EntryDate) Between [Start Date] And [End Date]));

SQL for query searching winder 1:
SELECT RollConsumption.RollNumber, RollConsumption.YardsConsumed, RollConsumption.WinderNumber, RollConsumption.EmployeeNumber, RollConsumption.Shift, RollConsumption.EntryDate
FROM RollConsumption
WHERE (((RollConsumption.WinderNumber)="12701") AND ((RollConsumption.EntryDate) Between [Enter Start Date] And [Enter End Date]));



SQL for query searching winder 2:
SELECT RollConsumption.RollNumber, RollConsumption.YardsConsumed, RollConsumption.WinderNumber, RollConsumption.EmployeeNumber, RollConsumption.Shift, RollConsumption.EntryDate
FROM RollConsumption
WHERE (((RollConsumption.WinderNumber)="12702") AND ((RollConsumption.EntryDate) Between [Enter Start Date] And [Enter End Date]));

[ItsMe]

OK yeah. I suspect Grouping and Sorting in the Report should get you where you need to be. Use one query without any WHERE criteria and then use the report's properties to Group, Sort, and Filter. If those properties don't get you where you want to be, build on top of that by using other tools like unbound controls in footers or headers, VBA behind the Report, and Adjusting the original query.

[nick404]

Awesome thank you for the assistance!