[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Reporting with relational Access database

I have four tables in an Access 2013 database:


The Patients are animals, so each Patient belongs to a Customer.

I'd like a report which lists the customers, followed by the Patients belonging to the Customer, then the invoices which belong to the customer and finally the Payments made by the Customer.

It would look something like:

Cust #123  Mr John Smith
123 Smith Street
Smithton  12343

#2534 Fluffy
     DOB: 01/01/2001
     Vaccinated: 01/06/2001

#8374 Spot
     DOB: 05/06/2012
     Vaccinated: 04/07/2013

#14253  02/02/2002 Fluffy
     Cut paw

#73546 03/03/2013 Fluffy

#82735 04/04/2013 Spot
     Fur ball

#73637 10/02/2002
     Inv#14253  $30.00
     Inv#14253  $5.00

#28282 05/05/2013
     Inv#82735  $37.50

<Page Break>

And so on for each Customer

I'd be really keen to get a working example of this.
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Your going to want to do this with something called "sub reports".   Sub reports appear on a main report, and the sub report data is filtered by the main report.

1. Create a report that simply gives you customers.

2. Create a report for thing that will be used as the Patients, Invoices, and Payments.  make sure you include the customer ID so you can filter the records.

Don't worry that all the records appear on the report, just that they present the data you want to see and are formatted correctly.

3. Now take the main report and in the detail section below your data, add sub report controls.   When you add a sub report control, it will want a source object.  This will be one of the reports you created in step 2.

4. Set the master/child link properties.   This is what filters the sub reports for the current customer records.  Your telling Access what field(s) to filter on, in this case, should be customer ID.

5. Now view the report.  For each customer, you will see a section with the patients, invoices, and payments for them.

Helen FeddemaCommented:
You could also do this as a grouped report, providing that the tables are correctly linked (patients linked to customers, payments to invoices, and invoices to customers).  For this approach, make a query including all the tables, and create a grouped report using the Report Wizard.
akbAuthor Commented:
"Sub Reports" are definitely the way to go. After some mucking around I managed to get this working perfectly. This is a very flexible tool, especially the ability to nest Sub Reports within Sub Reports. Now I just need to get back to it and make the report look "pretty". Thanks for your help.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now