Last Entry

[rivergum_23]

Hi Guys,



I'm having trouble with one of my queries. (Very simple or so I thought)

I have 3 columns

ID (Autonumber) | Part (Text) | Name (Text)

All I want my query to do is go to the highest "ID" (last entry) and return
the that row (ID | Part | Name)

In the query I have the "Total" set to "last" on all columns, however the query returns not always the last entry or highest ID.

Can anybody suggest why this happens???

And how to fix it.

Thanks Heaps,
River

[alansidman]

"Last" sometimes gives irregular results. Try "Max" instead.

BTW, the term Name is a reserved word in Access. Suggest you change the fieldname to something else, as it may cause issues in the future.

Alan

[rivergum_23]

Hi Alansidman,
Thanks for your reply. I have made both changes (Max & Name to Op_Name). It now queries and finds the last ID, however it finds the max in every column rather than the max ID and that row.

How do I get the query to find the max ID and then display it corresponding "Part" & "OP_Name" what am I missing.

I probably seems like an easy answer, however I not overly good with query's.

Thanks in advance.

River

[alansidman]

Had a minute so I developed an SQL that will give you the record with only the max ID.

SELECT Table3.ID, Table3.PartNr, Table3.PName
FROM Table3
WHERE (((Table3.ID)=(Select Max(ID) from Table3)));

I mislead you in the earlier answer. You need to have a subquery as shown here. Change the table and field names appropriately and open your query in SQL and paste in the revised statement. Then look at the design grid to see what it look like.
Alan

[rivergum_23]

I have attached a image of the screen. I have not used any SQL, perhaps that's the problem.

Just to recap, I require the query to show max ID and its row of data

Cheers,
River

[pbaldy]

This may be what you want:

http://www.baldyweb.com/LastValue.htm

[boblarson]

I have attached a image of the screen. I have not used any SQL, perhaps that's the problem.

Just to recap, I require the query to show max ID and its row of data

Cheers,
River

Your screenshot shows you have MAX in EVERY COLUMN. It should ONLY be in the ID column if you want the last ID. The others should show GROUP.

[alansidman]

Did you try the instructions I gave you in my last post?

[rivergum_23]

Hi Alansidman,
I tried you code, however it gave me parentheses error. I'm guessing that it was something to do with what I was doing.

boblarson, I tried using the "Group By" in the other columns, however the query would return all entries.

To make it easy for you guys I've attached the access file so you can see what I'm doing or not doing

All I want to do is for the report to display the last entry (in this case 66) and it corresponding entries.

And a big thanks for all of you giving me a hand.

River.

[alansidman]

See attached.

Edit: Just noticed that there was a report also. Change the Record Source from Max of ID in the properties to ID for the report.

Alan

[rivergum_23]

Hi Alan,
Thanks heaps for you help.

Very much appreciated.

River