Sorting Debtors from a Payment Query (1 Viewer)

delexy

New member
Local time
Today, 12:45
Joined
Jun 26, 2020
Messages
4
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

I’m here to help
Staff member
Local time
Today, 04:45
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:45
Joined
May 7, 2009
Messages
19,169
maybe:

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

delexy

New member
Local time
Today, 12:45
Joined
Jun 26, 2020
Messages
4
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:45
Joined
May 7, 2009
Messages
19,169
use the Original Expression where you derived Total Paid and Total Cost (the whole DSum().. expression).
 

Dreamweaver

Well-known member
Local time
Today, 11:45
Joined
Nov 28, 2005
Messages
2,466
I'm working on a project at the moment where I had to show the Balance so I did this

SQL:
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
 

Users who are viewing this thread

Top Bottom