Sorting Debtors from a Payment Query

[delexy]

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

 

[theDBguy]

Hi. Welcome to AWF!

What do you mean? What happens when you try to sort the query?

 

[arnelgp]

maybe:

...
ORDER BY (DSum("[Amount]","qryCostsPaymentsUnion","[BookingID]=" & [BookingID] & " AND [Item]<>'" & "Payment" & "'") - [Discount] - DSum("[Amount]","qryCostsPaymentsUnion","[BookingID]=" & [BookingID] & " AND [Item]='" & "Payment" & "'") DESC;

 

[delexy]

Hi. Welcome to AWF!

What do you mean? What happens when you try to sort the query?

It requests for Total paid and Total cost or Fields that are already present on the Query

 

[arnelgp]

use the Original Expression where you derived Total Paid and Total Cost (the whole DSum().. expression).

 

[Dreamweaver]

I'm working on a project at the moment where I had to show the Balance so I did this

SELECT tblLeases.LeaseID, Nz([TotalPaid],0) AS Paid, Nz([TotalScheduled],0) AS Scheduled, [Scheduled]-[Paid] AS Balance
FROM (QryLeaseTotalPayments RIGHT JOIN tblLeases ON QryLeaseTotalPayments.LeaseID = tblLeases.LeaseID) LEFT JOIN QryLeaseTotalScheduled ON tblLeases.LeaseID = QryLeaseTotalScheduled.LeaseID;

You should note [Scheduled]-[Paid] will show a positive value if in arrears you could switch it

I use a 2 Totals Queries
QryLeaseTotalPayments
QryLeaseTotalScheduled

Plus the main table if I only wanted to show rent in arrears I would use an expression like Balance>0

hope it helps

mick