Help with SQL join results

[ynotaz]

This should be simple but I'm at a loss. Here are 2 SQL views of 2 queries. The first is for testing/verification the second is what I'm trying to get working. I've added the "where invno=14168" to segregate the results down to the one failing item. The first query returns 2 rows which is correct.

SELECT InvLine.ID, InvLine.InvNo, InvLine.LineNo, InvLine.ItemDesc, InvLine.ItemSize, InvLine.Item, InvLine.Qty, InvLine.UnitPrice, InvLine.UOM, InvLine.ExtPrice, InvLine.ACCTClas
FROM InvLine
WHERE (((InvLine.InvNo)=14168));

The second which matches back to the Item file to insure the current value of "ACCTclas" is retrieved returns 3 rows, one of which is a duplicate.

SELECT InvLine.ID, InvLine.InvNo, InvLine.LineNo, InvLine.ItemDesc, InvLine.ItemSize, InvLine.Item, InvLine.Qty, InvLine.UnitPrice, InvLine.UOM, InvLine.ExtPrice, Item.ACCTClas
FROM InvLine LEFT JOIN Item ON InvLine.Item = Item.ITEM
WHERE (((InvLine.InvNo)=14168))
ORDER BY InvLine.InvNo DESC;

[ssanfu]

Not much to go on, but maybe:

Do the duplicate records EXACTLY match?
Every field in both records are identical?
Try

SELECT Distinct InvLine.ID, InvLine.InvNo, InvLine.LineNo, InvLine.ItemDesc, InvLine.ItemSize, InvLine.Item, InvLine.Qty, InvLine.UnitPrice, InvLine.UOM, InvLine.ExtPrice, Item.ACCTClas
FROM InvLine LEFT JOIN Item ON InvLine.Item = Item.ITEM
WHERE (((InvLine.InvNo)=14168))
ORDER BY InvLine.InvNo DESC;

[ynotaz]

Thanks for the reply.

Yes they do match exactly.

The "ID" field is an autonumber field from the invline table : Here is a cut and paste of the 3 record output from the production query that incorporates item file to get the current AcctClass and then a test query selecting the same invoice line items without the join to the item table. The problem is the duplication of ID 15446 in the production query.

ID	InvNo	LineNo	ItemDesc	ItemSize	Item	Qty	UnitPrice	UOM	ExtPrice	ACCTClas
15447	14168	0	Overpayments and Donations	Overpayments and Donations	9905	20	$1.00	EA	$20.00	F
15446	14168	0	Round Tubing 0.120 wall	1 1/2	2218	7	$1.78	FT	$12.46	M
15446	14168	0	Round Tubing 0.120 wall	1 1/2	2218	7	$1.78	FT	$12.46	M

ID	InvNo	LineNo	ItemDesc	ItemSize	Item	Qty	UnitPrice	UOM	ExtPrice	ACCTClas
15446	14168	0	Round Tubing 0.120 wall	1 1/2	2218	7	$1.78	FT	$12.46	M
15447	14168	0	Overpayments and Donations	Overpayments and Donations	9905	20	$1.00	EA	$20.00	F

[June7]

Are you sure there aren't two records in Item table with ITEM number 2218? Suggest you double check by doing a filter on that value in Item table just to make sure.

[ynotaz]

Well wouldn't you know it. I was assured that the item table was correct and had just been checked. Well seems not so much.

Thanks... I was pulling my hair out on this.....

SOLVED

[June7]

Might want to set ITEM field at least as Index Yes (no duplicates) even if it isn't the primary key.

[ynotaz]

Good idea. That will save future headaches.