Please I created a Hotel Booking Database that has a Payment query where I have Total cost field, Total paid field and balance field but I can seem to sort those whose Balance is greater than Total paid. as I need to create a debtors report based on bookings. Please help its really urgent for a project I'm on.
Attached is the SQL statement of the query I intend to sort debtors:
SELECT "Print" AS Print, "Make Payment" AS MakePayment, tblBookingPayments.BookingPaymentID, tblBookings.GuestID_FK, tblBookingPayments.Discount, CCur(DSum("[Amount]","qryCostsPaymentsUnion","[BookingID]=" & [BookingID] & " AND [Item]<>'" & "Payment" & "'")-([Discount])) AS TotalCost, CCur(DSum("[Amount]","qryCostsPaymentsUnion","[BookingID]=" & [BookingID] & " AND [Item]='" & "Payment" & "'")) AS TotalPaid, CCur([TotalPaid]-[TotalCost]) AS Balance, tblBookings.BookingID, tblBookingPayments.EmployeeID, tblBookingDetails.CheckInDate, tblBookingDetails.CheckOutDate
FROM (tblBookings INNER JOIN tblBookingDetails ON tblBookings.BookingID = tblBookingDetails.BookingID_FK) INNER JOIN tblBookingPayments ON tblBookings.BookingID = tblBookingPayments.BookingID_FK;
Thank you all for your anticipated response
Attached is the SQL statement of the query I intend to sort debtors:
SELECT "Print" AS Print, "Make Payment" AS MakePayment, tblBookingPayments.BookingPaymentID, tblBookings.GuestID_FK, tblBookingPayments.Discount, CCur(DSum("[Amount]","qryCostsPaymentsUnion","[BookingID]=" & [BookingID] & " AND [Item]<>'" & "Payment" & "'")-([Discount])) AS TotalCost, CCur(DSum("[Amount]","qryCostsPaymentsUnion","[BookingID]=" & [BookingID] & " AND [Item]='" & "Payment" & "'")) AS TotalPaid, CCur([TotalPaid]-[TotalCost]) AS Balance, tblBookings.BookingID, tblBookingPayments.EmployeeID, tblBookingDetails.CheckInDate, tblBookingDetails.CheckOutDate
FROM (tblBookings INNER JOIN tblBookingDetails ON tblBookings.BookingID = tblBookingDetails.BookingID_FK) INNER JOIN tblBookingPayments ON tblBookings.BookingID = tblBookingPayments.BookingID_FK;
Thank you all for your anticipated response