query criteria

[Nic]

Hi,
I would like to run a query on one of my tables.
In my table column labelled 'location' each record will have a 'location' allocate to it. This could be a number between 1 & 100, with a prefix 'a' or 'b', therefore a location could be somthing such as 'a25'.
Within my table, there will be lots of records with this location allocated to it, but what i want to do is to run a query which only displays the last (or latest time/dated) record. Therefore it would show all the latest records for all locations listed in the table.

Hope this makes sense, and any suggestions greatly appreciated,

Many thanks,

 

[pbaldy]

Does this help?

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

 

[Nic]

Thanks for this,
Is there no way i could do this with somthing in the criteria of just one query??

 

[pbaldy]

Not that I can think of, but it wouldn't be the first time I was wrong.

 

[pbaldy]

Here's my suggestion.

Create a query with only the location and the date/time field from the table with the data and have it be a grouping query with the Date/time set as MAX.

So, like

Select Location, Max([DateTimeFieldHere])
From TableNameHere
Group By Location

And then save that query. Then create a new query with the original table in it and this new query you just saved. LINK BOTH of the fields Location and DateField. Then bring the fields down you want from the main table into the query.

Like the link I posted then?

 

[Nic]

Hi, thanks for the suggestions!
Struggling with this though and am not sure where im going wrong (beginner at this stuff)!
Iv sorted my first query. when opened this simply displays a list of locations, nothing else.
Could you guide me a little further on what to do (and how) with the second query??

Many thanks,

 

[Nic]

Ok,
Got it.....Query 1 sorted, with locations AND latest date.
Can you please explain next steps a little more?

Many thanks,

 

[Nic]

Worry not!
Sorted it.

Thanks for the help!