I have a database where I record the locations of objects in a musuem. I have two main tables "Artefacts" and "locations". there is a one-to-many relationship between the two as an aretfact can have many locations records as it is moved around the museum. Every artefact has at least one location. The locations table holds all the locations an artefact has had in its life, each with the date it was moved there.
My problem is that i when i query the database, i want to retreive every artefact BUT only its latest location record. Whats happening at the moment is that i am getting every record in the locations table and access is duplicating the artefact data. so for example, my query gives me:
artefact 1 location 1 date movbed there
artefact 1 location 2 date moved there
artefact 1 location 3 date moved there
etc

whereas all i want is the last one of these.

Hope that makes sense....grateful for any advice

You can search by using date parameters (between date A and date B) or you can search based on the "latest" record, assuing your location field is in date order.

Both are covered in depth using the Search function.