Help on joining two tables with duplicate keys.

[#1]

Hi guys please se my sample date below:

Amount:
Document No.	Item No..	Description	Amount
201101290	44050	P/N:A Screw	200.00
201101290	44050	P/N:B Screw	300.00
201101290	44050	P/N:B Screw	350.00

Quantity:
Document No.	Item No.	Description	Quantity
201101290	44050	P/N:A Screw	1
201101290	44050	P/N:B Screw	5
201101290	44050	P/N:B Screw	20

Output:
Document No.	Item No.	Description	Amount	Quantity
201101290	44050	P/N:A Screw	200.00	1
201101290	44050	P/N:B Screw	300.00	5
201101290	44050	P/N:B Screw	350.00	20

My problem is I can't find a unique key to use so that I can put the quantity only once, since the Item No. is the same for all lines and there are two same Description.

When I load the two tables the amount and quantity are being take up twice.

Hope you can give me idea on this so that table B will be link to Table A only once.

Regards,

~Skip

[CELAMBARASAN]

Hi,

    Table1:

    Load

           RecNo() as Key,

           Document No,

           Item No,

          Description,

          Quantity

     From.. Order By [Document No],[Item No],Description;

     Table2:

     Load

           RowNo() as Key,

           Document No,

           Item No,

          Description,

          Amount

      From..Order By [Document No],[Item No],Description;

Inner Join(Table1)

Load

     Key,

     Amount

Resident

     Table2;

Drop Table Table2;

Hope it helps

Celambarasan

Previously I wrongly Deleted the table1.Now i updated the correct one. Message was edited by: Celambarasan Adhimulam

[#3]

Hi,

I will look into this sample.. thanks for your reply.

regards,

~skip