Query Question

[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.

 

[tkelley via AccessMonster.com]

My first instinct is to suggest an autonumber ID field as a primary key.
Then you can use Max(ID) WHERE [Txn]=311.

Does that help?