Combining 1 to many into one record 1

[MrBillSC]

I have a vendor table and a vendorCommodity table that has a one to many relationship. I need to export the data with some of the vendor table fields and one new field that contains all of the vendor's commodityCodes (from multiple records) into one field separated by a comma.

I thought I would create a new table and load the information into it.

Example of data:
Vendor Name: Commodity Code:
Bob's Services 12-323
12-330
14-120
John's Goods 01-331
03-424
Barry's Repairs 15-222
Will's Rentals 13-565
11-444
02-112

Desired output:
Vendor Name Commodity Codes
Bob's Services 12-323, 12-330, 14-120
John's Goods 01-331, 03-424
Barry's Repairs 15-222
Will's Rentals 13,565, 11-444, 02-112

My approach was to load the vendor table data into the new table with an append query, then update the new single commCode field with the concatenated commodity records from the vendorCommodity table. I can't seem to get an expression to concatenate the fields for each vendor.
I have tried this expression:
ExpCombinedComcodes: [ExpCombinedComcodes]+", "+[TblVendCommodity].[ComCode]

I get an error message with it:
"Circular reference cause by alias 'ExpCombinedComcodes'in query definition select list.

Perhaps my whole approach is wrong.

Any suggestions are greately appreciated.

Thanks,
MrBill

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrBillSC]

PHV,
Thanks for responding. I have opened a new module and copied in the code from the link as follows:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
' Dim rs As New ADODB.Recordset
' rs.Open pstrSQL, CurrentProject.Connection, _
' adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

I tried to use the fuction in an expression in an Access query, but apparently I am not using the function correctly.
My expression is passing the commodity code field that I want to concatenate:

ExpComCode: Concatenate([TblVendCommodity].[ComCode])

The error message says:
"The Microsoft Jet Database engine cannot find the table or query '406-12'. Make sure it exists or that it is spelled correctly."
The "406-12" is the ComCode value of the first record.


Can you give me an example of how to use the new Concatenate function in a query expression? I've not created a function in Access before. This is all new to me.

thank you for your help,

MrBill

 

[PHV]

I'd try something like this:
ExpComCode: Concatenate("SELECT ComCode FROM TblVendCommodity WHERE VendorName='" & [VendorName] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrBillSC]

Thank you PHV. You've helped me immensely. My final code for the expression (which works perfectly) is as follows:

ExpComCode: Concatenate("SELECT ComCode FROM TblVendCommodity WHERE VendorId=" & [AgencyVendorNum] & "and (statusCode = 'A' or statusCode = 'K')")

A star for you.

MrBill