Returning only the last number in a field

[stanley721]

OK, I've read many solutions and used several methods for similar situations, but I keep getting odd results, of which are not what I want. I will try to explain in detail so that I can get the most customized results.
I have a training database with a table that has the following fields:
"Job Area" "Duty Area Number" "Task Number" "Task Name"



as well as a few other fields, but they all just contain extraneous info. Now I run a query that asks what "Duty Area" I want. I can input the Duty Area # and it returns all the Tasks from that Duty Area. Works great, all's good. Now I would like for the results to only return the last Task Number from that Duty Area, so after running the query, I should have only one record returned to me. I have tried using the DMAX function as well as several other ways using Macros and others. Maybe I'm not putting the code in right or in the right place. This seems soooo simple but it is kicking my rear in a bad way! Can someone please help?! Let me know if you need any more info to clear things up.

[jzwp11]

It sounds like you need a totals query that uses the Max() function not the DMax() function

Assuming that you want to group by duty area number within job area, the query may look something like this:

SELECT [Job Area],[Duty Area Number], Max([Task Number]) AS MaxOfTaskNumber
FROM yourtable
GROUP BY [Job Area],[Duty Area Number]

[stanley721]

I've tried that and it doesn't work. As a matter of fact, it returns all of the Task Numbers in a weird random order. I'll attach a picture of the screen capture.

[stanley721]

Here is a view of the query.

[jzwp11]

You cannot include any other fields in the totals query, so it must be as follows

SELECT [Job Area],[Duty Area Number], Max([Task Number]) AS MaxOfTaskNumber
FROM yourtable
GROUP BY [Job Area],[Duty Area Number]

You will need to create another query that joins the totals query above with the table that has the other fields you want to display. You will have to make a join between the 3 fields of totals query and the corresponding fields in the other table.

[stanley721]

Sweet! That did it. You don't know how happy that makes me. I have been working on this for a couple of days and by getting this done, it also helps fix a few others things I was trying to accomplish.
Thanks!!!!

[jzwp11]

You're welcome. Glad that worked out for you.