Anonymous
Anonymous asked in Computers & InternetSoftware · 10 years ago

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?

Update:

Also, I need to make get the payment made date is before the payment due date.

1 Answer

Relevance
  • 10 years ago
    Best 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

    WHERE [DatePaymentDue]>[datepaymentmade]))

    GROUP BY Customers.CustName, Bookings.CustomerID;

Still have questions? Get your answers by asking now.