Multiple columns from one column

I track the aging of customer invoices.

The below code example returns the balance of customer invoices by customer that are between 0 and 30 days old.

However, I want to run one query that pulls separate columns into Excel for each customer’s balance aged between 0 and 30 days, 31 and 60, 61 and 90, and finally over 90 days.

I am hoping to pull this into Excel so the columns are as follows-
Customer Number, Customer Name, Balance(That is under 30 days old), Balance(30-59 days), Balance(60-90), Balance(Over 90)

vtSql = ""
vtSql = vtSql & " SELECT CUSTNUM, CUSTNAME, SUM(BALANCE) "
vtSql = vtSql & " FROM VIEWALLINVOICES "
vtSql = vtSql & " WHERE BALANCE <> '0' AND INVDATE BETWEEN #" & Application.Text(Range("TODAY") - 30, "mm/dd/yyyy") & "# AND #" & Application.Text(Range("TODAY"), "mm/dd/yyyy") & "#"
vtSql = vtSql & " GROUP BY CUSTNUM, CUSTNAME "
vtSql = vtSql & " ORDER BY SUM(BALANCE) DESC;"

I am using a MS Access database and I am pulling this directly into Excel using an ADODB Connection and “Microsoft.Jet.OLEDB.4.0”.

Answer

You seem to want conditional aggregation. In MS-Access, you should be able to phrase this as:

SELECT CUSTNUM, CUSTNAME, 
    SUM(IIF(INVDATE BETWEEN DateAdd('d', -30, date()) AND date()                   , BALANCE, 0)) AS BALANCE_0_30,
    SUM(IIF(INVDATE BETWEEN DateAdd('d', -60, date()) AND DateAdd('d', -31, date()), BALANCE, 0)) AS BALANCE_31_60,
    SUM(IIF(INVDATE BETWEEN DateAdd('d', -90, date()) AND DateAdd('d', -61, date()), BALANCE, 0)) AS BALANCE_61_90,
    SUM(IIF(INVDATE <= DateAdd('d', -91, date())                                   , BALANCE, 0)) AS BALANCE_OVER_91
FROM VIEWALLINVOICES
GROUP BY CUSTNUM, CUSTNAME

This computes the dates ranges dynamically by offseting the current date – which is how I understood your question.