duplicates

[Guest]

I have a query that returns duplicates based on two variables.
Inmate#
Product
What I need is if the Inmate has 4 or more of the same item then it gives me
the results, it if is less than or =3 then it does not display them in the
query.

Can this be done.

Here is my code.
SELECT tblProperty.[Inmate#], tblProperty.Product, tblProperty.ID,
tblProperty.Quantity, tblProperty.Date, tblProperty.Description,
tblProperty.[Order#], tblProperty.[Serial#]
FROM tblProperty
WHERE (((tblProperty.[Inmate#]) In (SELECT [Inmate#] FROM [tblProperty] As
Tmp GROUP BY [Inmate#],[Product] HAVING Count(*)>1 And [Product] =
[tblProperty].[Product])))
ORDER BY tblProperty.[Inmate#], tblProperty.Product;

 

[John Spencer]

If I understand you correctly all you should need to do is to change the
HAVING COUNT(*) > 1 to HAVING Count(*) > 3

SELECT tblProperty.[Inmate#], tblProperty.Product, tblProperty.ID,
tblProperty.Quantity, tblProperty.Date, tblProperty.Description,
tblProperty.[Order#], tblProperty.[Serial#]
FROM tblProperty
WHERE (((tblProperty.[Inmate#]) In (SELECT [Inmate#]
FROM [tblProperty] As Tmp
GROUP BY [Inmate#],[Product]
HAVING Count(*)>3 And [Product] =
[tblProperty].[Product])))
ORDER BY tblProperty.[Inmate#], tblProperty.Product;

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

 

[Guest]

Works great thanks

If I understand you correctly all you should need to do is to change the
HAVING COUNT(*) > 1 to HAVING Count(*) > 3

SELECT tblProperty.[Inmate#], tblProperty.Product, tblProperty.ID,
tblProperty.Quantity, tblProperty.Date, tblProperty.Description,
tblProperty.[Order#], tblProperty.[Serial#]
FROM tblProperty
WHERE (((tblProperty.[Inmate#]) In (SELECT [Inmate#]
FROM [tblProperty] As Tmp
GROUP BY [Inmate#],[Product]
HAVING Count(*)>3 And [Product] =
[tblProperty].[Product])))
ORDER BY tblProperty.[Inmate#], tblProperty.Product;

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

I have a query that returns duplicates based on two variables.
Inmate#
Product
What I need is if the Inmate has 4 or more of the same item then it gives
me
the results, it if is less than or =3 then it does not display them in the
query.

Can this be done.

Here is my code.
SELECT tblProperty.[Inmate#], tblProperty.Product, tblProperty.ID,
tblProperty.Quantity, tblProperty.Date, tblProperty.Description,
tblProperty.[Order#], tblProperty.[Serial#]
FROM tblProperty
WHERE (((tblProperty.[Inmate#]) In (SELECT [Inmate#] FROM [tblProperty] As
Tmp GROUP BY [Inmate#],[Product] HAVING Count(*)>1 And [Product] =
[tblProperty].[Product])))
ORDER BY tblProperty.[Inmate#], tblProperty.Product;