I have a table which holds information on the status of a job. As the job travels through it's various stages, the user up dates the status accordingly. I want a report which shows the current status of a job. I thought it was working using a query with the following SQL:

SELECT tblJobStatus.JSJobNo, Last(tblJobStatus.JSIDNo) AS LastOfJSIDNo
FROM tblJobStatus
GROUP BY tblJobStatus.JSJobNo
ORDER BY tblJobStatus.JSJobNo;


But it's not correct. It's showing status number three out of the nine steps recorded at the present. If I change it from last of to Max, the report shows me, what appears to be, the current status of each job, ie, step 9, but now I'm worried if the user makes more changes to the status, it won't update again.

Can anybody explain why the Last of isn't updating and will Max work instead?

Thanks.

DBL

The order by clause is applied to the output recordset, not the input. Relational tables are unordered sets. Therefore any time a table is read, the rows could be retrieved in a different order. Since you want the maximum value of a field rather than the value that happened to be retrieved last, use the Max() function.

To validate this answer, create a select query that includes JSJobNo and JSIDNo and include an order by clause that specifies both fields. Then use that query as the basis for your current query rather than the table. Once you have forced the records into a particular order, the Last() and Max() functions will produce the same results.

query1:
Select JSJobNo, JSIDNo
From tblJobStatus
Order by JSJobNo, JSIDNo;

yourpresentquery:
SELECT JSJobNo, Last(JSIDNo) AS LastOfJSIDNo
FROM query1
GROUP BY JSJobNo
ORDER BY JSJobNo;

Thanks Pat, made the changes you suggested. The Max() is working but the Last() is still giving me false data. If I stick with Max() will it automatically update along with the data entry?

Did you not understand my answer? In summary - Max() will ALWAYS return the value you want, Last() will SOMETIMES return the value you want.

Last() is NOT giving you false data. It is doing what it is supposed to do. You are using it to do something it does not do. It finds the physically last record of a set. It does not find the maximumn value except in the rare instances when the two values happen to be the same.

Hi Pat, yes, I did understand your answer but your last sentance "Once you have forced the records into a particular order, the Last() and Max() functions will produce the same results", had led me to think that after I had forced the sort order, I would get the result I wanted regardless of whether I used Last or Max.

I've changed everything to Max and it's working.

Thanks again.