T
tmwilkin
I have data in a table such as the following:
Date INV_ID SOLD
12/2/07 00021 Y
12/3/07 00021 N
12/2/07 00058 Y
12/3/07 00058 Y
I want a make-table query that will show records that are duplicates on the
INV_ID field AND different on the SOLD field (which would show only the first
two records in the example above). Below is the SQL for showing duplicates
on both INV_ID and SOLD, which I would like to adjust if possible...
SELECT [CREATE FW PKG TABLE].INV_ID, [CREATE FW PKG TABLE].SOLD, [CREATE FW
PKG TABLE].SITE_ID_1, [CREATE FW PKG TABLE].SITE_ID, [CREATE FW PKG
TABLE].APPLICABLE_DATE, [CREATE FW PKG TABLE].POINTS
FROM [CREATE FW PKG TABLE]
WHERE ((([CREATE FW PKG TABLE].INV_ID) In (SELECT [INV_ID] FROM [CREATE FW
PKG TABLE] As Tmp GROUP BY [INV_ID],[SOLD] HAVING Count(*)>1 And [SOLD] =
[CREATE FW PKG TABLE].[SOLD])))
ORDER BY [CREATE FW PKG TABLE].INV_ID, [CREATE FW PKG TABLE].SOLD;
Any assistance would be greatly appreciated.
Thanks,
Todd
Date INV_ID SOLD
12/2/07 00021 Y
12/3/07 00021 N
12/2/07 00058 Y
12/3/07 00058 Y
I want a make-table query that will show records that are duplicates on the
INV_ID field AND different on the SOLD field (which would show only the first
two records in the example above). Below is the SQL for showing duplicates
on both INV_ID and SOLD, which I would like to adjust if possible...
SELECT [CREATE FW PKG TABLE].INV_ID, [CREATE FW PKG TABLE].SOLD, [CREATE FW
PKG TABLE].SITE_ID_1, [CREATE FW PKG TABLE].SITE_ID, [CREATE FW PKG
TABLE].APPLICABLE_DATE, [CREATE FW PKG TABLE].POINTS
FROM [CREATE FW PKG TABLE]
WHERE ((([CREATE FW PKG TABLE].INV_ID) In (SELECT [INV_ID] FROM [CREATE FW
PKG TABLE] As Tmp GROUP BY [INV_ID],[SOLD] HAVING Count(*)>1 And [SOLD] =
[CREATE FW PKG TABLE].[SOLD])))
ORDER BY [CREATE FW PKG TABLE].INV_ID, [CREATE FW PKG TABLE].SOLD;
Any assistance would be greatly appreciated.
Thanks,
Todd