Last Record from a table

[parabox]

Hi all, im quite the access noob and I have what is hopefully a simple question to answer for the veterans .




I've created a database which currently has two tables. One called asset and the other movement. A relationship exists between these tables so that i can find out which movements have been submitted to an asset. (Movement ID is related to the Asset ID in the asset table.)

My question comes when i try to find the last movement of an asset.

I have created a query which has a movement id field and a location field. I have set the query totals to group by the movement id field and the location to last.

This query seems to pull out random records, right now it will only pull the first record or 2nd record if i set the query to first.

Can anybody help me on this?

Many Thanks!

[jzwp11]

When you say that you want to find the last or most recent movement of an asset that implies that a time is involved. So your query needs to find the maximum date of when the asset was last moved.

You said that MovementID is the foreign key relating back to the Asset table (and not the primary key of the movement table), so the query would look like this

SELECT movementID, max(datefield) as MaxOfDateField
FROM Movement
GROUP BY movementID

You would then create a second query (I'll call it Q2) that joins the above query back to the movement table and join by both the movementID fields in both as well as the datefield/maxofdatefield. You would select the fields you want from both souces. Make sure to include the movementID.

Then you will create a third query that joins the asset table to Q2 via the movementID field.

[parabox]

So far this work extremely well.

I really appreciate your help.

Thanks!!

[jzwp11]

You're welcome.