Linking fields

[naomi]

My database is a large contact database I am having troube
with. These are the crucial fields.

1.Vendor Name
2.Vend Code (numeric code)
3.Status Change (2,3, or 4 letter code)
4. Date Stamp.

Ok, this is the problem. I need a maxofdate stamp. for
each status change for each vendor. So far, when I
perform a Max in my query, it only gives me a MAX for each
day. How do I link it to my Vendor Code to give me the
last code for each Vendor. For any given project, the
code changes an average of 10 times. I only need the
last time the query changed. PLEASE HELP!!

 

[Pat Hartman]

You need to find the max date separately. Then you can find the status for that date.

query1:
Select VendorCode, VendorName, Max(DateStamp) As MaxDateStamp
From YourTable
Group by VendorCode, VendorName;

query2:
Select q.VendorCode, q.VendorName, t.DateStamp, t.StatusChange
From query1 Inner Join YourTable on q.VendorCode = t.VendorCode AND q.MaxDateStamp = t.DateStamp;