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]

@MNNovice
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]

@Denburt
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.

[MNNovice]

Denburt: Good morning.

ECHOID is same for all tables. I didn’t quite understand how do you find two separate ECHOIDs? tblAP and tblPayroll (and other similar expense tables – all will have to be connected to tblECHO with ECHOID.

I am trying to create a detailed report by Fund. Funds are a 3-digit numbers starting with 8 (872, 833, 819 etc). A fund can have multiple project number which is a 5-digit numbers usually starts with 6. (63740 , 64690, 65651 etc). A project can be further separated by sub class which is a combination of numbers and digits (T217, PJ08, 0999 etc).

My questions are:

1. What would be the correct way to do a report by Fund for each ECHO?
2.How do I do a summary report for all funds?
3. How do I arrange the report in an ascending order by fund number?

The latest version of my DB is attached. Any help is much appreciated. Thanks.

[NeoPa]

I'll try to get to downloading this tonight M.

I may need to clarify the question somewhat when I have done, as I admit to finding the requirement quite difficult to understand in the terms presented.

Maybe I'll have a clearer understanding when I get the db.

@Den, I'll post back when I have the database so it can be deleted. You do the same if you beat me to it.

[Denburt]

I downloaded it yesterday and managed to look it over to some degree. Before we move on to this second report request I would like to confirm the name of the report you are using for your Echo report. The one I see is a little off so I am not sure if I am looking at the right one. I think if we verify that everything is in order on that one it will help us with your fund report since I think they will be very close to the same thing with just a few minor changes. Thanks

[MNNovice]

Denburt:

Not sure which report you are referring to. I found one inconsistency on posting 11. The correct names for the sub reports are srptEchoAP and srptEchoPay. Usually I add "Detail" or "Sum" to indicate whether it's a detailed report or a summary report.

For the fund report I attempted with srptEchoAPFund and srptEchoPayFund. For the main report I used rptECHOFund. None of these worked.

Hope this answers your questions. If not, let me know. Thanks for your help.

[MNNovice]

NeoPa:

Feel free to ask for further clarifications. I know often I struggle to pose my questions. Many thanks for your help.

[NeoPa]

I found a report called rptAPDetail (rptAPDetails).

I saw no subreports on it at all.

Am I missing something here as I'm a bit stuck on how to proceed.

[NeoPa]

Forget that. I read through the thread again and saw that I was looking for rptECHO instead. I've found that & will have a look as time allows.