Run a for Loop in Access Query

Question

I have a Query that generates a list of 2% finance or service charges on completed orders whose balance is overdue this works fine. I then have a second query that totals the finance charges per customer (this bit also works fine) what I would like to get in that second totals query is a comma (or semicolon or space or pipe or ...) separated list of orders in another field

so for the first query I have:

CustomerID   OrderID        ContactName             FinanceChargeAmmount
   218        31901   Joe Schmoe Construction            23.43
   218        31927   Joe Schmoe Construction            15.78
   218        31929   Joe Schmoe Construction             8.91
   231        33403   Billy Bob Construction              0.43
   258        33369   XYZ Corp                            0.77
   258        33546   XYZ Corp                            1.23

and the second I have:

CustomerID   ContactName                 SumOfFinanceChargeAmmount
   218     Joe Schmoe Construction         48.12
   231     Billy Bob Construction           0.43
   258     XYZ Corp                         2.00

I would like to add another column to the second table like:

CustomerID   ContactName                 SumOfFinanceChargeAmmount     Orders
   218     Joe Schmoe Construction         48.12                   31901, 31927, 31929
   231     Billy Bob Construction           0.43                   33403
   258     XYZ Corp                         2.00                   33369, 33546

but can't figure out a way to loop in the query or get a list of the summed values and while I understand that I should be able to do this in VBA I'm trying to avoid doing so if possible

If this was a MySQL question, I'd simply say: "Use `group_concat()`. But, as far as I know, there's no equivalent function in Access, so I'm afraid you'll have to write it "by hand" using VBA... is not hard. Which leads me to a question: Why are you trying to avoid VBA? — Barranka, Jul 09 '14 at 15:45
Im not a pull data from queries and do something with it VBA person I'm a if button is clicked set other field = X or go into subform and add a row with X data I'll spend the next 3 days messing with it in VBA but if i can do it in a query im already 3/4 of the way there — mcgyvrfan121, Jul 09 '14 at 15:50
you can create a function in vba that is callable from a SQL query... It's the simplest way to go, I think — Barranka, Jul 09 '14 at 15:50
is [this](http://stackoverflow.com/questions/9217234/concatenating-multiple-rows-into-single-line-in-ms-access), [this](http://stackoverflow.com/questions/92698/combine-rows-concatenate-rows) or [this](http://stackoverflow.com/questions/7600637/concatenate-fields-from-one-column-in-one-table-into-a-single-comma-delimited-v) what you're looking for? — serakfalcon, Jul 09 '14 at 15:51
@serakfalcon the first one looks like what I was looking for Idk how I missed it probably not wording my search right — mcgyvrfan121, Jul 09 '14 at 15:55

score 0 · Accepted Answer · answered Jul 09 '14 at 15:58

I know you said you want to avoid VBA, but I don't see another way to do it. So here is a proposal:

public function concatOrders(customerId as Integer) as String
    On Error GoTo Oops

    dim db as DAO.database, rec as DAO.recordSet
    dim ans as String : ans = ""
    dim first as Boolean : first = true
    dim strSQL = "select orderId from [yourTable] " & _
                 "where customerID=" & customerId & " order by orderId";
    set db = currentDb()
    set rec = db.openRecordset(strSQL, dbOpenDynaset, dbReadOnly)
    with rec
        .moveFirst
        do
            if first then
                first = false
            else
                strSQL = strSQL & ", "
            end if
            strSQL = strSQL & !orderId
            .moveNext
        loop until .EOF
        .close
    end with
    db.close
    function_exit:
        concatOrders = ans
        set rec = Nothing
        set db = Nothing
        exit function
    Oops:
        ' Handle the errors here
        goto function_exit
end function

your code works if you change all the strSQLs in your loop to ans or concatorders = ans to concatorders = strSQL — mcgyvrfan121, Jul 09 '14 at 16:32

Run a for Loop in Access Query

1 Answers1