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!