maximum record

A

at

Is there a way to get a maximum record based off of certain criteria? I have
a table that holds information for the weight of loads for a large container.
So my fields are containerID, loadID(auto#), and weight. Some containers
may have more than one weight so my information may look like this:

containerID loadID weight
ABC123 1 500
ABC123 2 75
CDE456 3 450
EFG111 4 700
GHI222 5 550
GHI222 6 50

I would like to narrow it down to where it always shows me the last weight
entered. I need a maxium on the loadID for each containerID. Most max
functions just give me loadID # 6, but I actually need to see loads 2, 3, 4,
& 6.

Any help is appreciated!!

at
 
C

Conan Kelly

at,

I think this will get you what you are looking for:

SELECT Table1.ContainerID,Table1.Weight
FROM (SELECT TempTbl.ContainerID, Max(TempTbl.LoadID) AS MaxOfLoadID
FROM Table1 as TempTbl
GROUP BY TempTbl.ContainerID) AS tblMaxLoadID LEFT JOIN Table1 ON
tblMaxLoadID.MaxOfLoadID = Table1.LoadID
GROUP BY Table1.ContainerID, Table1.LoadID, Table1.Weight;


Just change "Table1" to the name of your table everywhere you see it.

HTH,

Conan
 
J

John W. Vinson

I would like to narrow it down to where it always shows me the last weight
entered.

Access does NOT keep track of the order in which records are entered. A table
should be considered an unordered "heap" of data; what you ask is like asking
which was the last potato tossed into the bin.

If you need to keep track of data entry order, put either a sequential
Autonumber or a date/time value defaulting to Now() into the table, and use
Max() on this field.

John W. Vinson [MVP]
 
A

at

That worked great, thanks! I did use it on the auto number field in response
to John's reply.

Thanks for all your help!

at
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top