Reporting with relational Access database

Posted on 2014-08-18
Last Modified: 2014-08-19
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.
Question by:akb
    LVL 56

    Accepted Solution

    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.

    LVL 31

    Expert Comment

    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.
    LVL 13

    Author Closing Comment

    "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

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now