Reduce to rows to one, putting some values together

[imendi]

Hi,

This is a complicated.
I have two Tables:
Client (fields: ClientCode, ClientAddress...)
Vendor (fields: VendorCode, VendorName...)

Then, doing
-------------------------------
select VendorCode, ClientCode where Client.VendorCode = Vendor.VendorCode
-------------------------------
I may have:
VendorCode ClientCode
VC1 CL1
VC5 CL45
VC1 CL24


I want to put on a new table, that information, but in the following way:
Vendor Client
VC1 CL1, CL24
VC5 CL1

So I have concatenated two values in the same field, where the VendorCode is the same.

I have to do it using an SQL statement.

Any ideas?

Many thanks,

im

 

[geaker]

im,
Depends on how many steps you want to do it in. You could first put all the data in a new table and then run a query against that table using the SQL 'select distinct' to get all the unique vendor IDs and put those in a separate table, loop through until you have no more vendor numbers. Then use a query to select customers based on the unique entry and concatenate the customer values in a string to write to you final table that will contain unique vendor numbers but multiple customer numbers.

Here is some SQL and VB code I used:

'Run Query to Make Table for All Service Centers

strSCQuery = "SELECT Payment.*, ServiceCenters.Contact1, ServiceCenters.Contact2," _
& " ServiceCenters.Contact3, ServiceCenters.Contact4, ServiceCenters.Contact5" _
& " INTO AllServiceCentersEmail FROM Payment LEFT JOIN ServiceCenters ON" _
& " Payment.ServiceCenter = ServiceCenters.ServiceCenter WHERE" _
& " (((Payment.PostDate) Between Forms![EmailReports]![StartDate]" _
& " And Forms![EmailReports]![EndDate])) ORDER BY Payment.ServiceCenter," _
& " Payment.CheckNumber, Payment.InvoiceKey;"

DoCmd.RunSQL strSCQuery
On Error Resume Next

strSCQuery = ""

' Run Query to Make a Table of Unique Service Centers included in CheckRegisterEmail Table

strSCQuery = "SELECT DISTINCT AllServiceCentersEmail.ServiceCenter INTO" _
& " SCforEmail FROM AllServiceCentersEmail;"

DoCmd.RunSQL strSCQuery
On Error Resume Next

strSCQuery = ""

Set rsSC = dbs.OpenRecordset("SCforEmail", dbOpenDynaset)
rsSC.MoveFirst

Do Until rsSC.EOF = True

'Delete the Table from the Last Loop

DoCmd.DeleteObject acTable, "EmailTemp"
On Error Resume Next

strSCQuery = "SELECT AllServiceCentersEmail.* INTO EmailTemp" _
& " FROM AllServiceCentersEmail WHERE (((AllServiceCentersEmail.ServiceCenter)" _
& " =" & "'" & rsSC!ServiceCenter & "'" & &quot);"

DoCmd.RunSQL strSCQuery
On Error Resume Next

.
.
.
strContact = ""
Set rs = dbs.OpenRecordset("EmailTemp", dbOpenDynaset)
rs.MoveFirst
.
.
.
Err_Next_Loop:

'Clean Up Tables

Set fso = CreateObject("Scripting.FileSystemObject&quot
fso.DeleteFile (strPath)
On Error Resume Next

'Clean Up Strings

rs.Close
Set rs = Nothing
Set MailOutLook = Nothing
appOutLook.Quit

'Next Record

rsSC.MoveNext

'Loop until No More Records

Loop


Hope this help,
Gerald