Furniture Report Total Quantity Question

[Cadman]

Hi Folks,
First time poster to any Access forum...nice to meet you all and look forward to working together with you all! :-)
I'm not new to Access, started using it back in 2003, but fell away from it for a number of years and just recently picked it back up and am struggling through learning 2016...love all the improvements!
I'm having an issue with one of my reports not behaving as I want it to and hope that you folks could help nudge me in the right direction.
In the attached image, Image1, you will see the furniture report as it is now. Each line represents a specific room although no room name or number is visible in the report. That is because we only want the totals of these pieces of furniture to show in this report.
In the database, there are 5 rooms that have C-01 chairs and each room has 30 chairs, 15 Ineligible and 15 Eligible, as you can see in the image. "Hide Duplicates" is set to "Yes" for ItemNo, Qty Ineligible and Qty Eligible, which is why they are not showing in the image. When I "Hide Duplicates" for the other fields, they will hide but the lines still appear.
What I would like to do his have only one line for each piece of furniture showing the totals of all items.
So for this report is should be,
ItemID=C-01- Qty Ineligible=75- Qty Eligible=75- Product Description= Poly Classroom chair....- Contract Price= $69.30- Manufacturer= KI- Model No.=IW413- State Contract=16SX0190- Cost Ineligible=$5,197.50- Cost Eligible=$5,197.50
Then 2nd line would be,
ItemID=C-02- Qty Ineligible=75- Qty Eligible=75- Product Description= Poly Classroom chair....- Contract Price= $70.40- Manufacturer= KI- Model No.=IW415- State Contract=16SX0190- Cost Ineligible=$5,280.00- Cost Eligible=$$5,280.00
Thanks in advance for your much need help!
Don
In this case the quantities happen to be the same.

[isladogs]

Welcome to the forum

Create an aggregate query using all fields from your report
Change the Group By to Sum for the 2 Cost fields
Your query will have two results
Now create your report based on the aggregate query

[Cadman]

Hi Ridders52..Thanks for the welcome.

I thought that is what I did here, but I have overlooked something?

Thanks,
Don

[June7]

What is that Expr1 field derived from?

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be ContractNum.

[Cadman]

Hi Ridder52 and June7,
Thanks for the nudge Ridders52! That pushed me to where I believe I need to be.

June7, I agree, no spaces nor punctuation etc. but I work with what I'm given and will clean it up after...for the next project.
Again, Thank you for your assistance and look forward to being some help to you or others in the future!
Have an awesome day!
Don

[June7]

Numbers showing aren't what you said should be.

[isladogs]

The report shows 10 fields but your aggregate query has/had at least 13.
Remove any items not used in the report (e.g. roomNo) as these are causing the grouping to split down further
As well as the unwanted Expr1 field, at least one of the Quantity fields appears twice as a Sum.
Once that is fixed you should have the correct sum results as well

[June7]

Alternative is not to use aggregate query but do aggregate calcs in Report design with Sorting & Grouping features. This allows display of detail as well as summary data. And the Detail section can be hidden if don't want to show.

[Cadman]

June7, The numbers aren't what I said they should be, because not all the numbers are plugged into the database yet. At first I thought it was because those lines were hidden, but they aren't.
I'm not familiar with "aggregate calcs"...can you point me where I can learn more about them please?
The image below is where we are now and looks like everything is working as expected now.

Here is another issue I'm trying to fix.
In the image below you will see that under "Phase" it is showing a "3" for room A121. The phase is not showing under room A122 because I am hiding the duplicates.

However, what I want is to show the "Phase" for every room, but not for every piece of furniture. If I say "No" to "Hide Duplicates" then every piece of furniture will have a phase listed next to it as shown in the image below.

Similarly, I am doing the same for Room# and Room Name, but on one page of the report it has two rooms that are named "Storage", but only one shows, see image below, but I would like to have them both show with the room number, but not for every piece of furniture listed under the room number.

Can you please help me understand what I'm doing wrong? Is there some kind of formatting I can do to get these reports the way I need them to be?
Again, thank you all for your much appreciated help!!!

[June7]

You are already doing 'aggregate calcs' in aggregate (GROUP BY) query. Instead of aggregate calcs in query, textbox ControlSource expression using aggregate function: =Sum([QuantityEligible])

Maybe you need to set some grouping in the report to manage that display.