how to merge mult records into a single record

[McSteve]

I am trying to merge multiple records into a single record. Records that need
to be merged have a common account number. The data of each multiple record
(repeats up to 5 times) is basically the same with only the last 3 fields
being different. An example of the data is as follows: Acct #, Name, Phone,
Note, call date, call time, call result.
What I am looking to do is to have merged record that looks like the
following:
acct#, Name, Phone, Note, 1st call date, 1st call time, 1st call result, 2nd
call date, 2nd call time, 2nd call result, 3rd call date, 3rd call time, 3rd
call result, 4th call date, 4th call time 4th call result, 5th call date, 5th
call time, 5th call result. In the event there are only 3 mults then call 4
& 5 would return null.

 

[John W. Vinson]

I am trying to merge multiple records into a single record. Records that need
to be merged have a common account number. The data of each multiple record
(repeats up to 5 times) is basically the same with only the last 3 fields
being different. An example of the data is as follows: Acct #, Name, Phone,
Note, call date, call time, call result.
What I am looking to do is to have merged record that looks like the
following:
acct#, Name, Phone, Note, 1st call date, 1st call time, 1st call result, 2nd
call date, 2nd call time, 2nd call result, 3rd call date, 3rd call time, 3rd
call result, 4th call date, 4th call time 4th call result, 5th call date, 5th
call time, 5th call result. In the event there are only 3 mults then call 4
& 5 would return null.

Why?

What will you be doing with this result? I hope not storing it in another
table!

What you can do is to create a Query by adding the table to the query grid
five times. Include the account number, name, phone, and note from the first
instance, and the call date, call time, and call result from all five. Join
them on Acct#, first to second, second to third, and so on. Access will assign
aliases table_1, table_2, table_3, table_4 to the extra instances.

Put criteria on each instance's call date and call time to be greater than the
previous instance. You may need to combine the call date and call time into a
single date/time field (call date + call time), and might need subqueries to
limit it to the next chronological call.

 

[McSteve]

Thanks for the input!!! The reason I need results set up this way is a
tempoary workaround due to lacking an interface between two systems I'll
give this a try and let you know how it works.