Showing payments in diff fields made over a date range

[Madison06]

Tell me if this is possible to do one one report, I could swear I've done it before:

tOrders has three payment fields (I know, I know but hear me out). Every order gets three payments: a deposit, one installment payment, and a final balance payment. There are never more (or less) than these three payment types. I don't think a one-to-many relationship is necessary here, but correct me if I'm wrong.

Ideally, I'd like to run a report that shows all deposits received, all installment payments received, and all final balances coming due over a date range, with totals for each payment type, on one report. I.e, the client wants to know all deposits that came in, all the installments that came in, and all balances that are due over this date range.

So right now my query uses Between/And criteria for the DepositDate, InstallmentDate, and BalanceDueDate like this:

SELECT tblOrders.OrderID, tblOrders.FkCustomer, tblOrders.OrderDate, tblOrders.BalanceDate, tblOrders.DepositDate, tblOrders.InstallmentDate, tblOrders.Deposit, tblCustomer.cLname, tblCustomer.cFname, tblOrders.OrdersType, tblOrders.Installment
FROM tblCustomer INNER JOIN tblOrders ON tblCustomer.CustomerID = tblOrders.FkCustomer


WHERE (((tblOrders.OrderDate) Between [Begin Date:] And [End Date:]) OR (((tblOrders.BalanceDate) Between [Begin Date:] And [End Date:])) OR (((tblOrders.DepositDate) Between [Begin Date:] And [End Date:])) OR (((tblOrders.InstallmentDate) Between [Begin Date:] And [End Date:]))
ORDER BY tblOrders.OrderDate;

How can I then group deposits,installments, etc on the report? The issue of course is that orders that are coming due also have values in the deposit and installment fields that don't necessarily fit into the date range entered so I don't want those as part of the totals for that group. Do I need 3 separate queries? And if so, how to combine? I don't know why my brain isn't making this one work it lol Thx!

[CJ_London]

but correct me if I'm wrong.

you are wrong because what you now require becomes much more difficult to achieve

[Bulzie]

Maybe 3 subreports based on each of the 3 amount fields(use their specific date fields for the criteria), then put them on a main report.

[Micron]

tOrders has three payment fields (I know, I know but hear me out).

I have yet to see a case of where this is purported to be necessary or the only way it can be done and to have that be true. This case doesn't seem to be any different. It certainly does seem to be the root of your problem though.

[June7]

I don't see a field for Balance payment in your SQL.

Maybe you do need 3 queries with filter criteria and 3 subreports.

Or maybe UNION will serve.

SELECT OrderID, FkCustomer, OrderDate, OrdersType, DepositDate AS TransDate, Deposit AS TransAmt, "Deposit" AS TransType FROM tblOrders
UNION SELECT OrderID, FkCustomer, OrderDate, OrdersType, InstallmentDate, Installment, "Install" FROM tblOrders
UNION SELECT OrderID, FkCustomer, OrderDate, OrdersType, BalanceDate, Balance, "Balance" FROM tblOrders;

Save that query object then use it in report as if it were a table (like the one possibly should have). Join it to tblCustomer, apply filter criteria, build group on TransType field.

How do you determine balance due? I don't see a field for order amount.

[Madison06]

you are wrong because what you now require becomes much more difficult to achieve

Not what I wanted to hear because these numbers require so many additional calculations in other places (admin fees, processing fees, taxes, etc that come with plenty of IF/THEN situations) that need to be shown on forms and reports which I am dreading having to do from a related table. But it is what it is! Thank you.

[Madison06]

Maybe 3 subreports based on each of the 3 amount fields(use their specific date fields for the criteria), then put them on a main report.

This actually worked beautifully! But... I think I'm going to still going to have to normalize properly to prevent future issues.

[CJ_London]

(admin fees, processing fees, taxes, etc that come with plenty of IF/THEN situations)

these can often be handled with joins