Query- combine two lines into 1

[Number11]

So i have a table that contains

ID
AccountNumber
SerialNo
code


data is stored like this..

ID ¦ AccountNumber ¦ SerialNo ¦ Code
========================
2010 ¦ G1155854122 ¦ DHHT144744 ¦ 002GH112
2011 ¦ G1155854122 ¦ DHHT144744 ¦ 066577

And i need the query to show like this:

AccountNumber ¦ SerialNo ¦ CodeA ¦ CodeB
G1155854122 ¦ DHHT144744 ¦ 002GH112 ¦ 066577

duno how to do this please can you hlelp?

 

[theDBguy]

Try using a Crosstab query.

 

[Number11]

think i got this, created two queries max and min of the ID then joined them together

 

[CJ_London]

for a crosstab the trick is being able to define a column heading - and there is a limit of 255 columns. At the moment I'm not seeing what that might be other than using the ID in some way - using dsum to calculate a row number for example

 

[CJ_London]

created two queries max and min of the ID then joined them together

so there are never more than two records for any account/serial number?

 

[Number11]

so there are never more than two records for any account/serial number?

ummm good point dam yes can be upto 6. mmmmm help?

 

[The_Doc_Man]

Past a certain point, when merging lines that have some but not all parts in common, you might consider a function that takes a GROUP BY of the parts that ARE in common.

Be aware that in such cases, the resultant query will not be updateable and might be difficult to use as a .RecordSource for something else. If this query is used as the base for another query, that next query might also become impossible to update. Therefore, any such query will have to be limited in usage.

Do you actually need a query? Because it might be possible to do this kind of thing on a form or report.