sharing data between reports
I am trying to have the totals from one report show up on another report. Cant seem to get it.
I would like the "Forecast total" from the "Project Margins Details" report to show up on the "Project Dashboard" report under the forecast cost for each project. I have attached a sample file.
Also can you tell me where the item is that is causing the error message when you open the file Projects.accdb
I would like the "Forecast total" from the "Project Margins Details" report to show up on the "Project Dashboard" report under the forecast cost for each project. I have attached a sample file.
Also can you tell me where the item is that is causing the error message when you open the file Projects.accdb
In your case you can display this total in a control anywhere you like by using the following formula as a textbox Control source
=DSum("Total","[Project Margins Details]")
=DSum("Total","[Project Margins Details]")
ASKER
this only partiallyt solved my issue. It is pulling the total from the project margin report. I want the total for each individual project. can this be modified to add that criteria?
Sure, but you logic for doing this is flawed.
Your ultimate goal here is to include (at some level) the same source into the other report.
Meaning you really should not have to rely on "grabbing" a value form one report and inserting it into another.
For example, ...you need Total sales per employee for New York in one report.
Great, you create a report to show that.
Now you need this same data in a Nationwide Report (All States).
In this case your goal should not be to "get" the data from the first report and insert it into the second report.
Instead the same source or "RecordSource", (again at some level) should be used for both reports.
This way you create the report the same way
Make sense?
But to answer your question directly;...Yes, you can add criteria to the Dsum like so:
=DSum("Total","[Project Margins Details]", "State=" & "'" & New York & "'")
...for a sum from NY (text)
Or
=DSum("Total","[Project Margins Details]", "EmpID=" & me.empID)
...for the sum for a selected EmployeeID (Numeric)
You can see the help files on "Aggregate Functions", for more in depth information.
Some issues with using aggregate functions are that:
1. They use more resources that doing this through the Recordsource
2. They can get very complex to write as you include multiple criterion that include Numbers, Text and dates)
JeffCoachman
Your ultimate goal here is to include (at some level) the same source into the other report.
Meaning you really should not have to rely on "grabbing" a value form one report and inserting it into another.
For example, ...you need Total sales per employee for New York in one report.
Great, you create a report to show that.
Now you need this same data in a Nationwide Report (All States).
In this case your goal should not be to "get" the data from the first report and insert it into the second report.
Instead the same source or "RecordSource", (again at some level) should be used for both reports.
This way you create the report the same way
Make sense?
But to answer your question directly;...Yes, you can add criteria to the Dsum like so:
=DSum("Total","[Project Margins Details]", "State=" & "'" & New York & "'")
...for a sum from NY (text)
Or
=DSum("Total","[Project Margins Details]", "EmpID=" & me.empID)
...for the sum for a selected EmployeeID (Numeric)
You can see the help files on "Aggregate Functions", for more in depth information.
Some issues with using aggregate functions are that:
1. They use more resources that doing this through the Recordsource
2. They can get very complex to write as you include multiple criterion that include Numbers, Text and dates)
JeffCoachman
BTW, the error is caused because you set an opening form ("Project List") in the Access option, but that form has either been renamed or deleted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In a Brute force scenario you could store the first value in a Public variable.
This simply insert this variable into the second report (via a function.)