help with query

[Alex]

Hi Everybody,

We have a table with the following fields:
ItemNum LotNum and ProdDate

123 527 05/15/2009
123 527 05/15/2009
123 527 05/13/2009
123 528 05/12/2009
124 529 05/15/2009
124 529 05/15/2009
…

We’d need to get records for all items that have the same LotNum and
different Date. In our sample above it would be the following group:
123 527 05/15/2009
123 527 05/15/2009
123 527 05/13/2009

Could anybody advise what query would be for it to get all those groups of
items?

Thanks

 

[Ken Snell MVP]

You'll need two queries. The first one gives you the dataset of ItemNum and
LotNum that have differing ProdDate values:

qryGetGrouping
-----------------
SELECT DISTINCT ItemNum, LotNum
FROM YourTable
WHERE ProdDate <>
(SELECT Min(T.ProdDate)
FROM YourTable AS T
WHERE T.ItemNum = YourTable.ItemNum
AND T.LotNum = YourTable.LotNum);


Then this query will give you the desired result:
SELECT ItemNum, LotNum, ProdDate
FROM YourTable INNER JOIN qryGetGrouping
ON YourTable.ItemNum = qryGetGrouping.ItemNum
AND YourTable.LotNum = qryGetGrouping.LotNum

 

[John Spencer]

Easiest way I know is to compare the max and min values of the date for
each combination of itemnum ad lotNum. Something like the following SQL
statement

SELECT DISTINCT S.ItemNum, S.LotNum, S.ProdDate
FROM SomeTable Inner JOIN
(SELECT ItemNum, LotNum
FROM SomeTable
GROUP BY ItemNum, LotNum
HAVING Min(ProdDate)<>Max(ProdDate)) as Temp
ON SomeTable.ItemNum = Temp.ItemNum and
SomeTable.LotNum = Temp.LotNum

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Alex]

Thank you so much guys. I run it today on my DB and it’s working very well.
I used John’s more aggregated query.
Ken’s approach should work as well if I used it in MS Access but I tried it
on SQL server using a temp table to go through it and I’ve stuck with the
COLLATE Latin1_General_CI_AS vs. SQL_Latin1_General_CI_AS problem that I just
couldn’t resolve.

Thank you very much again.