Highest Value

[Deuce Sapp]

I have two tables and a query:
table 1: equipment
table 2: workorders
query: month_release

The query evaluates the equipment table and, based on a form input,
generates a list of equipment that has to be serviced for a month. I also
have an update query that appends the workorders table with the serial
numbers returned in that query.

My problem is that my workorders table is going to have workordernumber
(which is an autonumber) and the serial number....and the serial number is
going to ultimately be in there multiple times. I want to keep it that way
so I can know what work orders applied to what serial numbers. Now...I need
to update the equipment table with the most recent workorder number. I
tried an update query with a MAX() statement, and a query using TOP Values.
It seems like these methods only want to return a single value.

Where am I going wrong?


Deuce

 

[Tom Ellison]

Dear Deuce:

It is actually a very, very good policy NOT to do this. The "most recent
workorder number" can be derived by a query at the moment you need it, any
time you need it. Such derived values should NOT be stored in your tables.
It will be a great deal less work in the long run to do it this way.

 

[Deuce Sapp]

Thanks for your post Tom. What I need to query (whether I update that field
or not...I did remove the field by the way) is the highest workorder number
for a certain serial number. I can get the highest number all day
long...but I can't figure out the highest number for a certain serial
number.

Well...actually...in typing that I think I figured it out. I am going to
try to create a query that filters the table by serial number and then query
that query to find the FIRST, LAST, TOP or MAX value.

Thanks again.

 

[Tom Ellison]

Dear Deuce:

Well, that's actually a different question. But, we have a 2 for the price
of 1 sale on today, so you're in luck.

Start with a "Totals" query with the "Serial Number" field "Group By" and
the "Workorder Number" field set to Maximum.

If you want to see any other details peculiar to that workorder, some more
advanced techniques will be needed. For example, you may want to see the
date of the largest numbered workorder, but this would not necessarily be
the maximum of the date field, but rather the date of the maximum workorder
number. This CAN be quite different from the maximum date if workorder
numbers are not assigned in the same order as the dates. Or perhaps you
want to see the date the work was completed, or who did the work.

Please supply details about the table(s) involved and what it is you need to
see and I'll give you a method to achieve this.

 

[Deuce Sapp]

Tom,

Great idea! I'm sure that will put all the pieces in place for me. If I
need to do any advanced manipulation of that info, I can write another query
that utilizes that one.

Thanks again. PS. Can I get on the mailing list for the 2-for-1 sales? What
a bargain!!


Deuce