I have two different tables, the "customers" tables, with "customer ID" as the primary field and a few more fields such as Name, Surname etc. (All in text format).

The second table is "Booking details", It has the primary key as "Booking ID" and then customer name, surname etc, (Again, all in text)

So, basically, in the Booking details table, each booking is given a unique Booking ID, and one customer can do several bookings, i want to create a query that sorts out only customer with more than 3 bookings ?

enter image description here

SELECT Customers.Customer_ID
FROM Customers
INNER JOIN Bookings 
    ON Customers.Customer_ID = Bookings.Customer_ID
GROUP BY Customers.Customer_ID
HAVING COUNT(Customers.Customer_ID) > 3

Visually (some terms are approximate because I have no MS Access with English interface):

  • Add both tables to Query pane
  • Join them by proper fields
  • Select fields from Customers table which you want to output, do NOT add fields from Booking table
  • Press Totals knob on the ribbon
  • Add one more Customers.Customer_ID to the fields list
  • Change its Grouping function from Grouping by to Count
  • Set the criteria for this field to >3
  • Uncheck Show if you do not need in its value
| improve this answer | |
  • @SalmanTahir Visual building added. – Akina Apr 10 '19 at 10:00

One way of solving this is joining both tables and grouping the result by Customer_ID, so you can count the number of ocurrences and filter those who appeared more than three times:

c.Customer_ID as customer
count(c.Customer_ID) as bookings
from Customers c
inner join Boockings b
on c.Customer_ID = b.Customer_ID
group by c.Customer_ID
having bookings > 3;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.