Query based on date

[LouisPat]

Hi!

I'm making a data base on fire hydrants of a city. I have a table with
characteristics of the hydrant and another table with the history of
interventions of employees on these hydrants.

In the intervention table, I have the fields [ hydrant id],[date
intervention], [functional]. the functional field is a yes/no field.

I want to make a query that will give me the non functional hydrants at the
latest intervention.

I have tried to do this, but it doesn't work. I made a query with these
three field: [date interv] is set on max, and [functional] is set on where,
with the criteria NO. Access gives me the dates of their last non
functionality , but I that's the contrary of what I want. (For example,
hydrant 102 was not functional in 1999, but it is now in 2005 but that
record appears in my query)

Can someone please help me?
Thank you very much
Louis Pat

 

[John Spencer (MVP)]

Try using a coordinated subquery

SELECT [Hydrant ID], [Date Intervention], [Functional]
FROM YourTable
WHERE [Date Intervention] =
(SELECT MAX (tmp.[Date Intervention])
FROM YourTable as tmp
WHERE tmp.[Hydrant Id] = YourTable.[Hydrant Id])
AND Functional = False

Another way to do this is to use TWO queries
QueryA
SELECT [Hydrant Id], MAX([Date Intervention]) as LastVisit
FROM YourTable
GROUP BY [Hydrant Id]

Save that as queryA and then use it in a second query.
SELECT [Hydrant ID], [Date Intervention], [Functional]
FROM YourTable INNER JOIN QueryA

 

[LouisPat]

Thanks!!

it works perfectly

You rock!

Louis Pat