Access Report: Invoice and Invoice Detail


I have two tables in Access. One called "Invoices" and the other called "Invoice Detail". The two tables are joined as shown in the image below. I want to create an Access report that shows the "Invoice" data at the top for a certain Invoice No and then shows the "Invoice Detail"  data for this invoice below. What is the best way to do this?. Do I use forms with subforms?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>>> Do I use forms with subforms?

Your image did not show up, but...

You're headed in the right direction. For a report, it would be report/subreports with the Master Child Links set to the InvoiceID (or whatever field defines the relationship between the two tables).

This is Microsoft's reference for subforms.  The same approach can be applied to subreports.
Subform Tutorial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can use a main report and a subreport to show related records (in this case the detail), but in general you will find that when doing a report, it's best to "flatten" the data (one row of combined data with all the fields needed from the parent table and detail table).

The reason is that the reporting engine can then look at like fields between the rows and perform grouping and other operations where it could not if the data was separate.

So create a query that joins the header and detail fields needed, then base the report on that.

You can then do a group header on the invoice number and print the header info, and in the detail section of the report, show the fields associated with the invoice detail.

 A group footer on the invoice number is where you would show invoice totals.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Murray,

adding on to Jim's comment to flatten the data, here is an EE video that would be helpful to show how to do that:

Create a Query and Grouped Report and Modify Design using Access

a followup video to polish this example up a bit is here:

Polish Reports in Access

have an awesome day,
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much for the help
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Murray ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.