Count of the number of appointments that each customer has made

I am trying to create a SQL query in Microsoft access that is able to count of the number of appointments that each customer has made but have been unsuccessful so far. I have two separate tables, one for appointment and one for customers as listed below.

Appointment

Customer

CodePudding user response：

You will need to do some aggregation:

SELECT Customer.Customer_ID, Customer.Name, Customer.Lastname, COUNT(*)
FROM Customer
JOIN Appointment
ON Customer.Customer_ID = Appointment.Customer_ID
WHERE Attended <> 'Yes'
GROUP BY Customer.Customer_ID, Customer.Name, Customer.Lastname;

I have no MS Access in front of me, so if there is a typo, let me know!