Find duplicates one one field, unmatching on another

[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

 

[John Spencer]

Since your table name has spaces in it, you are going to have to do this in
a series of queries.

Query One: qSoldCat
SELECT DISTINCT INV_ID , SOLD
FROM [CREATE FW PKG TABLE]

QueryTwo: Uses the above saved query.
SELECT [CP].INV_ID
, [CP].SOLD
, [CP].SITE_ID_1
, [CP].SITE_ID
, [CP].APPLICABLE_DATE
, [CP].POINTS
FROM [CREATE FW PKG TABLE] As CP
WHERE [CP].INV_ID In
(SELECT INV_ID
FROM qSoldCat
GROUP BY Inv_ID
HAVING Count(Sold) >1)

I aliased your table name ([CREATE FW PKG TABLE]) to make it easier for me
to read your query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tmwilkin]

That worked great! Thanks John!

Since your table name has spaces in it, you are going to have to do this in
a series of queries.

Query One: qSoldCat
SELECT DISTINCT INV_ID , SOLD
FROM [CREATE FW PKG TABLE]

QueryTwo: Uses the above saved query.
SELECT [CP].INV_ID
, [CP].SOLD
, [CP].SITE_ID_1
, [CP].SITE_ID
, [CP].APPLICABLE_DATE
, [CP].POINTS
FROM [CREATE FW PKG TABLE] As CP
WHERE [CP].INV_ID In
(SELECT INV_ID
FROM qSoldCat
GROUP BY Inv_ID
HAVING Count(Sold) >1)

I aliased your table name ([CREATE FW PKG TABLE]) to make it easier for me
to read your query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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