Query Duplicate Records into one line

[achillese]

Please help! I am having trouble with this.

I am trying to query records with duplicate Invoice Number and combine the other fields in duplicate records to the first record. Sounds confusing but I will illustrate below the source table and the desired result required:

In the source table, all records have the same Invoice Reference. Line 1 and 2 have the same LINESEQ (114688). I want to make a query that combines the Serial in line 2 in line 1 under a different column (Phone).

For line 4, I want it added as another column in line 3 (same LINESEQ as line 4).

SOURCE TABLE:
=============
LN INV ITEMNBR DATE SERIAL CUST LINESEQ
1. 14247 NOK6230i 03/07/05 3569937795666 James 114688
2. 14247 BI95 03/07/05 97918983 James 114688
3. 14247 NOK6230i 03/07/05 3569937795777 James 180224
4. 14247 BI55 03/07/05 97799922 James 180224
5. 14247 SAMD500 03/07/05 3569937795193 James 212992
6. 14247 BI55 03/07/05 97989433 James 212992

DESIRED QUERY RESULT:
=====================
INV ITEMNBR DATE SERIAL PHONE CUST LINESEQ
14247 NOK6230i 03/07/05 3569937795666 97918983 James 114688
14247 NOK6230i 03/07/05 3569937795777 97799922 James 180224
14247 SAMD500 03/07/05 3569937795193 97989433 James 212992

Your help is greatly appreciated.
Many thanks in advance.

Cheers
bokasen

 

[BNPMike]

If you join SourceTable to SourceTable where LineSeq=LineSeq and Serial<>Serial you'll get the Desired Query Result, only you'll get it twice. You can then remove the redundant lines by something like Serial_First>Serial_Second or some other predictable feature of your data.

 

[achillese]

The Source Table is actually the result of another query with join properties.

At this stage, I want to have another query that results to the desired query in 1 step rather than passing it again to another table.

I am sure a sophisticated query can address this one. Also, the sample shown only showed some field but actually there are other fields that need to be align with the first records.

I just don't get the join thing with the same table.

Thanks +++

 

[BNPMike]

I didn't mean you have to execute it in stages. All this can be done in one query. I was just explaining the logic.

Joining a table to itself is an important strategy you need to grasp. For example if you have employees who are married to each other, or work for each other, you'll need to do recursive joins. Take your example data above and just try it. You'll soon see what's happening.

In Access/Jet its doesn't matter whether you are joining tables or queries, so don't worry about that angle. Indeed one of my cardinal rules is never write application code that directly refers to a table. Always go via views (ie queries).