ssblue
asked on
Access report groups with sums
I need to make groups and sums based on the 'cost element'
Example: I would like to show the following ** Some 'cost elements' may not always be available and or have negative numbers
Materials & Supplies = (601000 + 601002 + 601100 + 601105 + 601200)
Rentals = (632000 + 670002 + 670004)
Per Diem = (622000)
Labor = (800100 + 800110)
Total Cost = Sum of All
Project Savings = ( Sum of All ) - ( 800110 )
Estimated Cost = (I would like this to be a user defined field that is entered when the report is opened)
Real Savings = ( Estimated Cost ) - ( Total Cost )
See Attachment
ExportTest.accdb
Example: I would like to show the following ** Some 'cost elements' may not always be available and or have negative numbers
Materials & Supplies = (601000 + 601002 + 601100 + 601105 + 601200)
Rentals = (632000 + 670002 + 670004)
Per Diem = (622000)
Labor = (800100 + 800110)
Total Cost = Sum of All
Project Savings = ( Sum of All ) - ( 800110 )
Estimated Cost = (I would like this to be a user defined field that is entered when the report is opened)
Real Savings = ( Estimated Cost ) - ( Total Cost )
See Attachment
ExportTest.accdb
If your table is not normalized then you will have to hard code the calculations as you have described above. If your table is normalized, then you would add a grouping table that lets you collect expenses into groups.
ASKER
I don't know what you mean by normalized. What you see in the attachment is what I have.
Sorry, I didn't notice the attachment.
You need to create a table where you can define the groups. I did what I could with your posted data. You need to fill in the blanks and fix the description. for the lines I added that were mentioned in your task desc but not included in the data.
You need to create a table where you can define the groups. I did what I could with your posted data. You need to fill in the blanks and fix the description. for the lines I added that were mentioned in your task desc but not included in the data.
CostElement CostElementDescr CostGroup
601000 Materials & Supplies-Returns (CR) Materials & Supplies
601002 Materials & Supplies - Stock Materials & Supplies
601100 Material & Supplies-Non Stock Materials & Supplies
632000 Outside Services-Sys & Equip Maint & Rep Rentals
640030 Empl Exp-Personal Auto Use
640050 Empl Exp-Airfare
670002 Equipment Rentals Rentals
800100 PwrSupply-Internal Labor Activity Labor
800110 Pwr Supply-Internal Labor Activity OT Labor
800200 Per Diem Activity
601105 601105 Materials & Supplies
601200 601200 Materials & Supplies
670004 670004 Rentals
622000 62000 Per Diem
ASKER
Sorry but I gonna need a little more info or an example please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.