So I wrote this monster query that I'm pretty proud of. The data is in a MS access database so that explains some stuff, but the challenge was like this:

• AR Data is in three tables: ARCustomers, ARSummary, and ARDetail. ARcustomers is self-explanatory, ARSummary is a table with the invoice data (total, date, invoice number, etc.), and ARdetail is a payment table with amount, posting date, etc. The tables are linked by indexed fields in each one.
• I needed a report that broke down invoice date and amount outstanding as of a "cutoffdate' so the parameter of this query is cutoffdate. Essentially I needed to see what the current billing looked like on any given date.
• every "summary" record gets one corresponding customer record. But customers can have multiple summary records (multiple invoices)
• Every detail record has one corresponding summary record but each summary record can have multiple detail records. Why they didn't use the invoice number is beyond me...

So I made an ado connection called ADOCONN and connected to the database. Then I created an ADO Recordset called ADORS and I opened it using this query:

SELECT s.customer, s.customername, s.invoice, s.summaryid, s.total, s.invoicedate, d.totalpaid, s.balanceowed, s.lastpaymentdate FROM (SELECT a.balanceowed, a.lastpaymentdate, a.customer, a.invoice, a.summaryid, a.total, a.invoicedate, c.customername from arsummary as a INNER JOIN arcustomers as c ON a.customer = c.customer where invoicedate <= #" & Cutoffdate & "# and total <> 0 AND invoicedate >= (SELECT MIN(postingdate) from ardetail)) AS s LEFT JOIN [SELECT sum(amount) as totalpaid, summaryid from ardetail WHERE PostingDate <= #" & Cutoffdate & "# GROUP BY summaryid]. AS d ON s.summaryid = d.summaryid WHERE (s.total <> d.totalpaid OR isnull(d.totalpaid))

It returns 9 fields: customer, customername, invoice, summaryid, total, invoicedate, totalpaid, balanceowed, and invoicedate. The total paid field basically gives me the payment information as of a cutoff date. Also, no invoices printed after the cutoff date are included. Works pretty well, but it goes a bit slowly on big tables.