Microsoft Access Help?
My question is to: retrieve all customers who made their payments before the date their payment was due (even for some of the bookings). The query result should show every customer ONLY once even if he has several bookings that satisfy the criteria.
I have two tables which I would use for this.
Customer: Title, FName, LName, Address, etc.
Booking: DateBookingMade, StartDate, EndDate, AmountofPayment, DatePaymentDue, DatePaymentMade, etc.
How do I get it so that the query only returns the customer name once?
Also, I need to make get the payment made date is before the payment due date.
- Andrew LLv 71 decade agoFavorite Answer
You have missed out the two most important fields in your two tables. One is CustomerID, which is an autonumber field and the primary key in your "Customer" table. It is also a number field in your Bookings table,. Plus the "Bookings" table has its own autonumber field BookingID, which is the primary key in the table.
Edit: Single query.
SELECT Customers.CustName, Bookings.CustomerID, Count(Bookings.BookingID) AS CountOfBookingID
FROM Customers INNER JOIN Bookings ON Customers.CustomerID = Bookings.CustomerID
GROUP BY Customers.CustName, Bookings.CustomerID;