Solved

Access 2007 - help on a complex subreport query

Posted on 2011-02-15
7
351 Views
Last Modified: 2012-05-11
In an Access 2007 application, I have a report that has two sub reports.  The parent report is opened from code and is filtered down to showing data about 1 project and therefore has 1 ProjectID.  I have that ProjectID tied to the ProjectID of the first subreport to filter it's data down to show only that data corresponding to the project of the parent report.  The second report however is giving me problems as it is a lot more complex of a query.  Below is shown a query that I have built in Access query builder that I have finally gotten to show the data correctly for 1 project.  Notice the WHERE clause...

WHERE tbContractProject.ProjectID IS NULL OR tbContractProject.ProjectID = 4

I want to use this query to build a report that will act as my second subreport and I need the 4 in the WHERE clause to change to the ProjectID of the project loaded into the parent report.  I'm not sure how to go about doing this.

Any help would be greatly appreciated.
I need the 4 to be changed to the ProjectID of the parent report
SELECT tbExpenditureType.ExpenditureType, SUM(IIf(IsNull(tbExpenditureLedger.Amount),0,tbExpenditureLedger.Amount)) AS Amount
FROM ((tbExpenditureType LEFT JOIN tbFundingSourceFundingTypeExpenditureType ON tbExpenditureType.ExpenditureTypeID = tbFundingSourceFundingTypeExpenditureType.ExpenditureTypeID) LEFT JOIN tbExpenditureLedger ON tbFundingSourceFundingTypeExpenditureType.FundingSourceFundingTypeExpenditureTypeID = tbExpenditureLedger.FundingSourceFundingTypeExpenditureTypeID) LEFT JOIN tbContractProject ON tbExpenditureLedger.ContractProjectID = tbContractProject.ContractProjectID WHERE tbContractProject.ProjectID IS NULL OR tbContractProject.ProjectID = 4
GROUP BY tbExpenditureType.ExpenditureType

Open in new window

0
Comment
Question by:JosephEricDavis
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34900990
Does the query work when run alone?

Post a sample of this database.
Post a graphic clearly showing the exact output you are expecting.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.


JeffCoachman
0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 250 total points
ID: 34901019
I would use a replaceable paramater,  hooked to a global var...  to pass the data to the query.

In a standard module... create a public var and a function to retrieve it

Public gintProjectID as Integer    (or your datatype)

Public Function GetProjectID() as Integer
    GetProjectID = gintProjectID
End Function

You criteria in your query becomes

WHERE tbContractProject.ProjectID IS NULL OR tbContractProject.ProjectID = GetProjectID()

Set gintProjectID =.... in a routine that you use to open the report  or in a section of your report

If I knew more about the app I could make a better suggestion on the timing
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 34901032
If I understand what you want, this might work:

WHERE tbContractProject.ProjectID IS NULL OR tbContractProject.ProjectID = Parent!ProjectID

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34901041
Or you could use the full syntax:

Reports![rptName]!ProjectID
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34901125
Is this subreport only run as a subreport from this report?
0
 
LVL 7

Author Closing Comment

by:JosephEricDavis
ID: 34907300
Thanks guys, these were good suggestions.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34907703
Glad to help

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Crosstab Query with Multiple Values 4 32
Criteria for Date for DCount 4 26
2 IIF's in Access query 25 32
Help with MS Access search Form 7 17
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

832 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