Last date querry

[molly pup]

I have a table which contains vaccination history data. It includes animal ID, vaccination date and vaccination ID. Each animal will have several vaccinations over its life time. I need to run a querry that will only look at the last vaccination the animal has recieved to see it has been more than 6 months since it has been recieved by the animal. I am curently using the formular <Date()-168 but this returns all the animals past vaccinations over its life time that are over 6 months. I only want to querry the last one it has recieved to see it it has been more thank 6 months since its last booster.


Please help.
many thanks.

 

[Jon K]

I have attached a sample database. You can run Query1 for one animal ID and Query2b for all animal IDs.

The time elapsed shown in "? mon ? days" is based on calendar months and is calculated using the calTimeElapsed() function stored in Module1.
.

 

[Ian Mac]

Hi,

in the design view click the 'Σ' key and on the Field you want change 'Group By' to 'Max'
Keep the same criteria as you have.

 

[molly pup]

Jon you are an absolute legend. I cannot thank you enough for your help. But could I please ask you one more thing. Would you mind explaining how you created the 2a querry. This will add to my at the moment basic Access knowledge.

Many many thanks.

 

[Jon K]

You are welcome. Query2a is a Totals Query.

If you build the query in Design View, you can choose menu View, Totals (or simply click on the Totals button on the toolbar) to display the Total row in the query grid.

When you select Group By for the AnimalID field and Max for the VaccinationDate field in the Total row, Access knows that you want to find the latest VaccinationDate for each group of AnimalIDs.

We select Max, not Last, for the VaccinationDate field because a date/time field is internally stored as a number. So Max VaccinationDate will return the latest date (i.e. the maximum number) for each AnimalID.
.

 

[molly pup]

Thank you again! Most helpfull of you.