Reporting with relational Access database

I have four tables in an Access 2013 database:

Customers
Patients
Invoices
Payments

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

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

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

Invoices:
#14253  02/02/2002 Fluffy
     Cut paw
     $35.00

#73546 03/03/2013 Fluffy
     Headache
     $40.00

#82735 04/04/2013 Spot
     Fur ball
     $37.50

Payments:
#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.
LVL 13
akbAsked:
Who is Participating?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Jim.
0

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
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.
0
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.