How do you make child records in one line?

[achillese]

Hi,

I need some help in making the data below to be in ONE LINE (see desired result in Result 2). The data below is already the result of query from several tables. I could not go any further--please help.

Result 1:
=========
SOPNUMBE LNITMSEQ CMPNTSEQ ITEMNMBR SERLTNUM
--------- ----- ------ -------- ----------
INV014941 32768 16384 NOK8800 8968860086 (A)
INV014941 32768 32768 250BC DA 408887776 (B)
INV014943 16384 16384 NOK6230I 8968880049 (C)
INV014943 16384 32768 75BC DA 488898844 (D)
INV014944 16384 0 MOTE398 8989880099
INV014945 16384 0 NOK5140I 8966940004
INV014951 16384 16384 MOTV3B 8968690068
INV014951 16384 32768 45BC DA 488089788


RESULT 2: (DESIRED)
=========

SOPNUMBE ITEMNMBR SERLTNUM MOBILE PLAN
--------- -------- --------- --------- -------
INV014941 NOK8800 8968860086 408887776 250BC DA
INV014943 NOK6230I 8968880049 488898844 75BC DA

comments:
==The ITEMNMBR and SERLTNUM in Line (B) becomes part of line (A)under NEW columns MOBILE and PLAN (see Result 2).

==The ITEMNMBR and SERLTNUM in Line (D) becomes part of line (C)under NEW columns MOBILE and PLAN (see Result 2).

Thank you in advance.

 

[dhookom]

If your records were sorted randomly, how would anyone know which records were for ITEMNMBR and which were for MOBILE?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[achillese]

The records that need to be converted into one line has common SOPNUMBE and LNITMSEQ. The LNITMSEQ is not unique to the overall records, it is repeated within different SOPNUMBE.

I have actually solved the problem by making several queries and saved them temporarily in different tables and finally create an update query that update the MOBILE and PLAN columns. The final table contain the desired columns (MOBILE and PLAN) that is updated from temporary tables-- repositories for the Mobile and Plan extracted separately from the source table.

Thanks anyway for your quick repy.

 

[dhookom]

You didn't answer my question but I glad to hear you solved your problem.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]