How do I combine reports?

[MNNovice]

I have a report (rptAPDetails) that is based on qryAPDetails shows grant expenses for all invoice payments. Similarly I have a report (rptPayDetails) that is based on qryPayDetails which shows payroll expenses for grants.

I would like to generate a report combining these two reports. Where I will be able to combine expenses for all grants by types. For example,

Grant 9500215
A/P Expenses $xx,xxx.00
Payroll Expense $xx,xxx.00
TOTAL $xx,xxx.00

GRAND TOTAL (All grants) $xxx,xxx.00

I made a copy of rptAPDetails and called it srptAP and similarly created srptPay. Used these two reports as sub reports. But I didn’t succeed. My main form is based on tblECHO where ECHOID is the PK (auto) and ties all these queries.

How do I combine these reports? Can someone please help me with directions?

Thanks.

[Denburt]

The main report should contain a query with tblGrants and tblFundGrants so you can tie them together on the child/master fields using tblFundgrantID. Try that and let me know how it goes.

[MNNovice]

Thanks for the tip. I will try. This is what I am planning to do. See if I am on right track.

1. Create a query using tblECHO, tblGrants, tblGrantFunds. Let's name it qryECHO

2. Create a main report: rptECHO based on qryECHO

3. Question: Do I add srptAP and srptPay on to rptECHO? or Do I add qryAPDetails and qryPayDetails?

I am looking forward to fruitful Thursday. It will be nice to accomplish something before I leave for the long weekend.

Thanks.

[Denburt]

Thanks for the tip. I will try. This is what I am planning to do. See if I am on right track.

1. Create a query using tblECHO, tblGrants, tblGrantFunds. Let's name it qryECHO

2. Create a main report: rptECHO based on qryECHO

Sounds good

3. Question: Do I add srptAP and srptPay on to rptECHO? or Do I add qryAPDetails and qryPayDetails?

I am looking forward to fruitful Thursday. It will be nice to accomplish something before I leave for the long weekend.

Thanks.

Add your two reports to the main report make sure the child/master fields have their relations and you should be set. Good luck, I do hope today is fruitfull for you.

[MNNovice]

Add your two reports to the main report make sure the child/master fields have their relations and you should be set.

I am assuming you are referring to rptApDetails and rptPayDetails (Post #1). Am I correct?

[NeoPa]

Are you dealing with forms or reports (or maybe both)?

[MNNovice]

NeoPa:

I am dealing with reports. Trying to create a report that will combine info from two other reports. Report 1 is rptAPDetails (has all invoice expense data for grant expenses) and Report 2 is called rptPayDetails which has all payroll expenses for grants.

The process to claim reimbursements for these grant expenses is called ECHO. I have a tblECHO with a PK (auto) ECHOID. Based on Denburt's suggestion, I created qryECHO to include tblECHO and tblGrantFund. GrantFundID is the field that ties these tables.

I was successful in creating reports on AP expenses and Payroll expenses. I am trying to combine the two and create ECHO reports by grant expenses. It should list ECHO No, Grant No and associated AP and payroll expenses for these Grants.

ECHO: 1
Grannt 1xxxxx
AP $00000
Pay $00000
Sub Total $0000000

Grant 2xxxxx
....
Total ECHO 1 $000000

You get the idea.

But I didn't succeed doing this, YET. As I tried to use rptAPDetails as a sub report to rptECHO and selected: " Show tblAP for each record in qryEHO using GrantFundID" I got this error message:

"The link you selected cannot be used. This link was created based on relationships betwee source tables. To use this link you must add the missing field to the form or report's record source."

I am not sure I understand what is the solution here. When I select: "show qryECHOAPDetail for each record in qryEcho using ECHOID" each ECHO is repeated several times (16 -18 times). Of course I need to use the GrantFundID but have not been successful.

Can you give me some direction?

Thanks.

[MNNovice]

Denburt / NeoPa
I think I figured it out a little bit more. So don't respond to my help until I come back and report on my progress. Thanks.

[NeoPa]

Add your two reports to the main report make sure the child/master fields have their relations and you should be set. Good luck, I do hope today is fruitfull for you.

My advice would be very much along the lines of what DenBurt suggested. This isn't an area I have a lot of experience in, but the quoted approach should work, assuming the links between all the items are logically organised.

[Denburt]

Make sure GrantFundID is in the query that you are using in the record source for the main report and the subreports. It sounds like that is or could be the problem there.

[MNNovice]

Denburt/ NeoPa

I was successful in pulling the report together.

1. Created qryECHO (tblECHO, tblAP & tblGrantFunds). Used this for the main form frmECHO
2. Used srpAP and srpPay for the two sub reports.
3. Used ECHOID for the joining fields.

It's working. Thanks for your help.

[Denburt]

That's great glad you got it. I guess I was off on the ID that tied them together but it sounds like your on the mark now.

[MNNovice]

Denburt:

I have been trying to develop a report that will be based on the Fund number. So basically it should look like this:

FUND NO

A/P expenses
Payroll Expenses

ECHO No
TOTAL

How do I approach this? Thanks.

[Denburt]

Well according to the last DB you sent out you would create a main report based on a query with tblFunds and tblGrantFund your subreports it appears that you have an 1 echoID for tblAP and a separate echoID for tblPayroll correct? If so then create 1 subreport on tblAP and tblEcho then the second subreport would be based on tblPayroll and tblecho then it can all be joined using grantfundID and you can show the echo number next to each Payroll no. echo no. in the other you would show tblap expenses and next to the the echo no. (hope that helps)

[MNNovice]

Denburt:

I am trying to do a Fund Report not and ECHO Report. I will explain further tomorrow. I must leave now to catch my train.

Thanks.