Combining Multiple Related Records Into One Field

[Hendro623]

 SELECT tblOrderInfo.date_to_ship, tblCategories.CategoryName, tblOrderInfo.client, tblOrderInfo.Ord_num, Count(tblOrderDetails.ID) AS OrderRollCount
FROM (tblCategories INNER JOIN tblCustomers ON tblCategories.CategoryID = tblCustomers.CategoryID) INNER JOIN (tblOrderInfo INNER JOIN tblOrderDetails ON tblOrderInfo.Ord_num = tblOrderDetails.ord_num) ON tblCustomers.CustomerID = tblOrderInfo.shp_to
GROUP BY tblOrderInfo.date_to_ship, tblCategories.CategoryName, tblOrderInfo.client, tblOrderInfo.Ord_num;

I'm new to SQL and VBA and trying to find a way to combine multiple similar records into one field, I've included the SQL for the query I have set up. I'm trying to figure out how to concatenate the ord_num (separated by commas) when the fields "date_to_ship", "CategoryName", & "Client" all match up.

I searched tons of similar forums but I'm having a hard time finding something I can use. Somebody please help!! if you need more information please let me know!

[clindner.iv]

Hi

it would look something like this:

Dim Rs as recordset
Dim db as dao.database
Dim Strsql as string
Dim result as string

strsql = "SELECT tblOrderInfo.date_to_ship, " _
& "tblCategories.CategoryName, tblOrderInfo.client, " _
& "tblOrderInfo.Ord_num, Count(tblOrderDetails.ID) AS OrderRollCount" _
& " FROM (tblCategories INNER JOIN tblCustomers ON tblCategories.CategoryID = tblCustomers.CategoryID) INNER JOIN (tblOrderInfo INNER JOIN tblOrderDetails ON tblOrderInfo.Ord_num = tblOrderDetails.ord_num) ON tblCustomers.CustomerID = tblOrderInfo.shp_to " _
& "GROUP BY tblOrderInfo.date_to_ship, tblCategories.CategoryName, tblOrderInfo.client, tblOrderInfo.Ord_num;"

Set db = CurrentDb
Set rs = db.OpenRecordset(Strsql)


Result = rs.fields(0) & ", " & Rs.fields(1) 'do this until you have all the fields you want included

debug.print result 'this is if you want to test the result in the immediate window.

A couple of things. the Rs.fields(0) will be the the first column in your SQl string. it always starts with 0
The snytax of the SQL string is really important. make sure if you partition your sting into smaller portions you open and close each line as i did in rewriting your example.

[clindner.iv]

Also if you just want to combine the id into a single field you will need a loping statement

that would look like this

do until rs.eof
result = rs.fields(0) & ", "
rs.movenext
loop

debug.print result ' if you want to test your code in the immediate window.

[Hendro623]

So i'm a complete rookie when it comes to Access VBA...Where would this code be entered to generate the QRY? I created a module and I see it populated in the immediate window but looks like its just combining fields together vs records... Maybe I did it wrong or may not have explained it well the first time, I've attached an image of my query and then a report I grouped off of that query to form a visual. The orders I circled would be the ones I would need to be grouped in a single field ("order1, Order2") along with the other highlighted fields but in one row/record.

[John_G]

Access queries cannot do this on their own - you will need a VBA function; I would approach this with two queries.

First, make a query that gives you the first 4 columns, using the Group By to eliminate duplicates (I'll call it Qry1).

Now make another query similar to the first, but without the Group By, and with the Ord_Num. This will give you one record for each order (I'll call it Qry2).

Here's where the VBA comes in. Each row in Qry1 has 4 fields. For each of those Qry1 rows, you want to find all the rows in Qry2 which match on those 4 fields, and concatenate all the order numbers into a string.

A VBA function to do that might look something like this:

Function Order_List (A as date, B as string, C as string, D as string)
  '
  ' A = Date_to_Ship
  ' B = CategoryName
  ' C = CustomerName
  ' D = Client

  Dim OrderList as String, rst as RecordSet, SQL as String
  '
  ' SQL is a select statement from Qry2
  '
  SQL = "Select * from Qry2 WHERE " & _
            "Date_to_Ship = #" & A & "#" & _
            " AND CategoryName = '" & B & "'" & _
            " AND CustomerName = '" & C & "'" & _
            " AND Client = '" & D & "'"
  set rst = Currentdb.OpenRecordset(SQL)
  orderlist = ""
  while not rst.EOF
    if len(orderlist) > 0 then orderlist = orderlist & ", "
    orderlist = orderlist & len(trim(str(rst!ord_num)))    ' Convert to string and remove blanks
  wend
  Order_list = orderlist
end function

Now, write a third query that has all the fields in Qry1, plus a calculated field that calls the function, with the 4 fields in Qry1 as parameters:

Orders:Order_List([qry1].[date_to_ship], [qry1].[categoryName], [qry1].[CustomerName], [qry1].[Client])

I don't know if you really need all those square brackets or not, but it doesn't hurt.

I cannot guarantee I have all the syntax right, but I it's close (!)

Let us know how it goes.