Solved

sharing data between reports

Posted on 2011-09-13
6
310 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:SMP319
  • 5
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36530662
This depends on how/when/under what circumstances these two report will be displayed.


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.)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36530703
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]")
0
 
LVL 1

Author Comment

by:SMP319
ID: 36530815
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?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36531076
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



0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36531112
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36531275
...so in your case this would be what you could use for "Test Project"

=DSum("Total","[Project Margins Details]","Project=" & 4)

Finally, (and as a side note) I strongly advise you avoid using "Lookup Fields" in your tables.
There are many reasons:
http://access.mvps.org/access/lookupfields.htm
(Use lookups in your forms only)

...But in your case specifically it just makes it that much more difficult to see what the real underlying value is (4, not "Test Project")

JeffCoachman
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now