Joining records

[dhanson]

I'm looking for a way to combine an Acct# column for all records that have the same ID#.

Record ID# Acct#
1 7 0001
2 7 0020
3 8 0032
4 8 1111

It doesn't matter if the Acct#'s get put together in one field

Record ID# Acct#
1 7 0001 0020

or if a new field is created for each instance

Record ID# Acct# Acct#1
1 7 0001 0020

Any suggestions would be appreciated greatly.
Have a good one.

 

[SmokingCrater]

Eesh. I don't know that you can do that in a query. I had to do something similar and just wrote myself a simple VB program that looped through the original data and converted. It would probably be easiest to write yourself a simple VB program that will convert it.

A simple algorith as follows:

accounts = '' (null string)
set originalData = table data sorted by ID
for each record in OriginalData
if thisRecordID = NextRecordID
accounts = accounts & ',' & currentAcct#
else
accounts = accounts & ',' & currentAcct#
insert record
accounts = ''
end if
next original record

Hope that helps.

Crater