>Until those duplicates are fixed you will never get the proper results. try the following to prove it to yourself:
>
>Table1: (no primary key)
>ID - number
>DateStamp - Date/Time
>Desc - Text
>
>Populate Table1 with the following:
>1 01/jan/2002 texta
>1 02/jan/2002 textb
>2 01/feb/2002 textc
>2 02/feb/2002 textd
>2 03/feb/2002 texte
>
>
>Create a query named qryTable1 with SQL of:
>
>SELECT Table1.ID, Max(Table1.DateStamp) AS MaxOfDateStamp
>FROM Table1
>GROUP BY Table1.ID;
>
>Create a query named qryTable2 with SQL of:
>
>SELECT Table1.*
>FROM qryTable1 INNER JOIN Tabel1 ON (qryTable1.ID = Table1.ID AND
>qryTable1.MaxOfDateStamp = Table1.DateStamp);
>
>Run qryTable2. You should have 2 rows returned showing the full record where the record is the highest date for that ID. Now open up Table1 and change the first record's dateStamp from 01/jan/2002 to 02/jan/2002. execute qryTable2 again. You now have 3 records returned even though qryTable1 only returns 2 records. This is because you are getting 1 to many results due to the duplicate dates. So until you get uniqueness in your first query, your results will be screwed in the second. Hope this helps.
Jason,
I already explained. I ran an additional query to exclude duplicates from the original table. My table now has no duplicates, but some records have empty date field. They were unique MapRef+Date field was PK. But I still could not select those empty date records into my final result.
If it's not broken, fix it until it is.
My Blog