Generate Access report from multiple queries

Question

I have browsed a few answers on this topic but they all suggest using UNION queries and I'm pretty sure that's not the way to go in my case as the tables I'm trying to extract data from are fundamentally different. I'll try to explain to the best of my abilities.

I have three tables:

Customers

Appointments

Purchases

I wish for the user to be able to select a single customer and be presented with a report with the following information:

All the customer information (Name, email, address etc.)

All the appointments booked by the customer

All the purchases made by the customer

The problem arises when a customer does not have an appointmet or a purchase in the database as the single query I'm using at the moment does the following:

SELECT (info needed)
FROM (Customer INNER JOIN Purchase ON Customer.ID = Purchase.customerID)
INNER JOIN Appointment ON Customer.ID = Appointment.customerID;

If a customer does not have their customerID in the Purchase or Appointment table the resulting query will return an empty table since the JOIN won't be able to see any shared data between the tables.

I'm not sure what the best course of action would be and I hope someone here can shed some light on what the best practices are in this kind of situation, thanks in advance.

Suggested edit to add some sample data and desired results.

Customers

ID   Name  email           address
 1   Bob   [email protected]   70 bowman st.
 2   Tom   [email protected]   44 shirley av.
 3   Mary  [email protected]  4 goldfield rd.

Purchases

customerID itemID price
1          23     100
1          24     150
3          34     85

Appointments

customerID date
1          2/10
2          3/10
3          3/10

Now if I query with customerID = 2 I want the following info

Customer Info

name   email          address
Tom    [email protected]  44 shirley av.

Purchases

empty

Appointments

date
3/10

Edit your question and provide sample data and desired results. — Gordon Linoff

Valli Valli · Accepted Answer · 2017-10-30T21:12:33

Use a left join instead of inner join. So that you will get customer details even if he has not made an appointment or purchase

SELECT (info needed)
  FROM Customer Left 
  JOIN Purchase 
    ON Customer.ID = Purchase.customerID
Left JOIN Appointment 
  ON Customer.ID = Appointment.customerID;

Generate Access report from multiple queries

1 Answers