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.

A couple of truths about drupal:
1. Drupal as a CMS NEEDS and idiot proof content editing box. And don't tell me to use tinyMCE. We need a good one. So many times, a page has tanked because of misunderstood tags. The problem with tinyMCE or FCK is that you really can't have native php or html code in your body text because the text editor will screw it up.

2. Drupal needs javascript for a lot of its good stuff.

3. Drupal is very fun, but I'm a little worried that I'm missing out on some of the really advanced stuff. but that is why I am hacking away at this site.