consolidating records to one field 1

[dobe1]

I am working with a table where customers have multiple days of service.
For example, a customer may be serviced 1, 3, and 5 (Monday, Wednesday, Friday). The problem is that there may be three records for that customer and in the "day" field, there will be a 1 or 2, etc. Of course, in some cases, there will be as many as six records per customer (123456), Monday - Saturday service.

What I need is a routine that will populate each of the three records with "135". This will let me know that the customer is service 3 x week and the service is Monday, Wednesday, and Friday.

The fields envolved are Custid (unique to the customer, but not to the table). There is the same custid for Monday, Wednesday, and Friday service.

Example

Custid day
10001 1
10001 3
10001 5

12001 1
12001 3
12001 4
12001 5

The results that I wish are as follows, whidh each line item representing a record:
custid day service_days
10001 1 135
10001 2 135
10001 3 135

12001 1 1345
12001 3 1345
12001 4 1345
12001 5 1345


Thanks in advance for your help,

Dobe

 

[Golom]

Check out Duane Hookum's Generic Function to Concatenate Child Records

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[dobe1]

Thanks,
This looks like just what I am looking for.

Dobe

 

[dobe1]

I have been runing the SQL code. It works fine within the demo, but when I try to use my tables, it doesn't seem to work. Perhaps if I understood what all of the code meant, I could make it work.

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

I am not sure what the ampersand is for and just how this is working.

Also, the resuls from the demo returns a set with commas. I was wondering where the commas are commng from. If at all possible, I would like for the commas not to be there.
see below:
Duane, Laura, Jake, Chelsey, Dakota, Josie, Jackson


Any help would be appreciated.

Dobe

 

[dobe1]

Nevermind, as Rosann-Rosannadan would say. I understand now. The author has a userdefined functins....

Dobe