Need help combining fields into single records

[Channah]

I have a table with up to 3 entries per Account Number.

Each entry has a Service Code associated with an account number.

The problem is that I need 1 account number with all Service Codes. How would I write a query that would return only 1 instance of Account Number and in one field, aal service codes associated with each account number?

 

[lespaul]

Depends on how your table is set up. Can you give some more details?

Leslie

 

[Channah]

It can be set up how ever it needs to be. Currently it has a field for the Account Number, Customer Information (multiple fields) and Service Code. for example...

AcctNumber CustName ServiceCode
12345678 Channah C
12345678 Channah T
12345678 Channah D

I need the following...

AcctNumber CustName ServiceCode
12345678 Channah C,T,D

Got it? Thanks for the help.

Chris

 

[BSman]

Do you need the query for a report? If so, just use the first list you show and place each value in the details are (which you have set visible to false). The report should have a group level based on the AcctNumber with a control I'll call txtServiceCodes.

On the group header use this code:
me.txtServiceCodes = ""

In the details you need something like this:
me.txtServiceCodes = me.txtServiceCodes & ", " & me.txtSvcCode

(txtSvcCode is the name I've given to the control in details that contains the ServiceCode value).

You will need to do some additional programming so you don't end or start with a comma.

 

[Channah]

I need it in a separate table for import into our CRM.

 

[BSman]

I don't have a chance to try this, but here's something for you to try:

Create a crosstab query based on AcctNumber and CustName. The result will probably be something like this:

AcctNumber TotalCount C T D
12345678 3 1 1 1

Then create a second query based on the cross tab query, where you use the values in the Code columns to create a column with all codes for the customer shown. And you might want to combine with a table or grouping query that has the acctnumbers and names (without duplicate entries) so you can get the names.

The Code would look something like this:
Code: Iif([C]>0,"C,","&quot & Iif([T]>0,"T,","&quot & Iif([D]>0,"D,","&quot

From this you'd get something like you're looking for, although you'd have an extra comma at the end of the code field values.