SQL finding lowest date and oldest date from two duplicate records

[JeroenMioch]

Right,



I have a table that contains some duplicate records.
These records are duplicate because they share the same personell number.

These records contain more fields however. A name field and two date fields.
the name field is not to be altered. But the date fields i need to range from the lowest to the higest.

Lets say we have these records :

Personell number Name Validfrom Validto

997865 JeroenMioch 23-03-2012 30-05-2012
997865 JeroenMioch 27-04-2012 02-07-2012

So this is what i want the output to be :

997865 JeroenMioch 23-03-2012 02-07-2012

I figuered that the easiest way is to make an update SQL that spots the lowest date of the Validfrom field and updates the higest, and do the same with the validto field but then find the higest date.

This way i end up with two identical records that contain data that i want, then run a delete duplicates query and im done.
But im really really lost on how to code the SQL string.

Anyone have any clue ?

Thanks for thinking with me.

Greetings : Jeroen

[June7]

Do you really need to remove the records?

A GROUP BY (Totals) aggregate query will give you the dates, unfortunately cannot be used in UPDATE action:
SELECT [Personell Number], [Name], Min(Validfrom) As MinFrom, Max(Validto) As MaxTo GROUP BY [Personell Number], [Name];

This UPDATE works:
UPDATE Table1 SET Table1.Validfrom = DMin("Validfrom","Table1","[Personell Number]='" & [Personell Number] & "'"), Table1.Validto = DMax("Validto","Table1","[Personell Number]='" & [Personell Number] & "'");

[JeroenMioch]

That indeed works good.

I read on another forum that running a totals query will work too.
You need to run two querys, one for the Validto and one for the Validfrom field.
You put two fields in the validto query and aggregate the Min for the one field and one for the max. Do likewise for the validfrom and it gives the desired results

Thanks for the help June !