Query Problem

[Geoff Codd]

I have the following Query

SELECT Points.Id, Points.Location, [All Electricity Data].Present_Date, [All Electricity Data].Units
FROM Points INNER JOIN [All Electricity Data] ON Points.Id = [All Electricity Data].Point_Id
WHERE ((([All Electricity Data].Direct)="D"))
ORDER BY Points.Location, [All Electricity Data].Present_Date;

This Produces a table which looks like this

Id Location Present_Date Units
9716 Academy 11/03/2002 32032
9716 Academy 30/04/2002 26183
9716 Academy 10/06/2002 24147
9716 Academy 02/07/2002 11089
9719 Humanit 31/07/2000 4495
9719 Humanit 04/09/2000 11005
9719 Humanit 02/10/2000 15070
9719 Humanit 09/11/2000 21795

What I need to do is to insert another field which will contain the Max of present date which is less than the present date I should then end up with a table like so

Id Location Previous_Date Present_Date Units
9716 Academy 11/03/2002 32032
9716 Academy 11/03/2002 30/04/2002 26183
9716 Academy 30/04/2002 10/06/2002 24147
9716 Academy 10/06/2002 02/07/2002 11089
9719 Humanit 31/07/2000 4495
9719 Humanit 31/07/2000 04/09/2000 11005
9719 Humanit 04/09/2000 02/10/2000 15070
9719 Humanit 02/10/2000 09/11/2000 21795

I know that I Need to use Dmax, but I can never get the syntax right. All help is appreciated

Thanks
Geoff

 

[Geoff Codd]

I've sort of got it to work using

Previous_Date: DMax("[Present_Date]","All Electricity Data","[Point_Id] = " & [Points].[Id] & " And [Present_Date] < #" & [Present_Date] & "#")

About 60% of the records returned are correct but there are a few which aren't and I can't see a pattern to it

I have attached a stripped down copy of my dB.

Any ideas

Thanks
Geoff

 

[Geoff Codd]

Here's the file

 

[Geoff Codd]

I've done it

DMax("[Present_Date]","All Electricity Data","[Point_Id] = " & [Points].[Id] & " And [Present_Date] < #" & Format([Present_Date],"mm/dd/yyyy") & "#")

Geoff