Select Query, next record based on date

[Sean]

Below is a sample of my data set and "Material" will be the primary key.
Now, for every "Material" you will have "101" in the "Txn" field and
multiple "311" in the "Txn" field and each has a date stamp "Date" I need a
my query to grab the Max of the "Date" for each material with a 101 "Txn" and
then show me the very next 311 "Txn" for that material and the date. Can you
help? more detail below.


Txn Material Date
311 Y407056 10/28/2008
311 Y407056 10/27/2008
101 Y407056 10/27/2008
311 Y225560 10/20/2008
311 Y225560 10/20/2008
311 Y225560 10/19/2008
311 Y225560 10/17/2008
101 Y225560 10/17/2008

The 101 Txn is the transaction used when material is received and the very
next 311 tranaction is when that material was put away to the storage
location for that receipt. I will have multiple 101 transactions for my
material as each material can arrive multiple times in a month this is whay I
want to use the max function to grab the very last receipt. I need to be
sure that I grab the 311 transaction that happens right after the 101
transaction. Thaks so much.

 

[S.Clark]

First, [Material] by itself can't be the primary key, as there are duplicate
values. Material with Txn can be, though. I think you should create an
Autonumber field for the PK.

This is how I solved it with one query, using an alias of 311 for the 2nd
instance of the table [Materials]. (That's what all the excess characters
are, at least for Access 2007)

SELECT [%$##@_Alias].Materials.Txn, [%$##@_Alias].Materials.Material,
[%$##@_Alias].[311].Txn, Min([311].[TxnDate]) AS 311Date
FROM (SELECT Materials.Txn, Materials.Material, [311].Txn, [311].TxnDate
FROM Materials, Materials AS 311
WHERE (((Materials.Txn)="101") AND (([311].Txn)="311") AND
(([311].TxnDate)>=[Materials].[TxnDate]) AND
(([311].Material)=[Materials].[Material]))) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].Materials.Txn, [%$##@_Alias].Materials.Material,
[%$##@_Alias].[311].Txn;

 

[John Spencer]

SELECT S.TXN, S.MATERIAL, Min(S.[Date]) as TheDate
FROM SomeTable as S INNER JOIN
(SELECT Temp.TXN, Temp.MATERIAL, Temp.Date
FROM SomeTable as Temp
WHERE TXN = 101) as Q
ON S.Txn= Q.Txn
AND S.Material = q.Material
AND S.Date >= q.Date
GROUP BY S.TXN, S.MATERIAL


Since you have a field named Date (a reserved word) you might have to do this
in two queries. The first query gets the records that have a txn of 101.
SELECT TXN, MATERIAL, [Date]
FROM SomeTable
WHERE TXN = 101

Then you reference this saved query in place of the subquery.
SELECT S.TXN, S.MATERIAL, Min(S.[Date]) as TheDate
FROM SomeTable as S INNER JOIN TheSavedQueryName as Q
ON S.Txn= Q.Txn
AND S.Material = q.Material
AND S.Date >= q.Date
GROUP BY S.TXN, S.MATERIAL

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