Conflict between Order By and Group By/Distinct

[jburger9]

Hi all,

I am relatively new to Access, and have only made a few databases so far.

The one I am stuck on currently involves 2 tables via an ODBC link.

There is a unique field in both tables (ID) however it is giving me an error that the field is found in both tables. When looking at the data in the tables, there are duplicate IDs which I believe is causing the error.

I have attempted to remove the duplicates using DISTINCT which works, however it sorts from the oldest first.

SELECT DISTINCT Table1.ID
FROM Table1
ORDER BY Table1.ID DESC;

The problem is that it will only ever give me 12000 records at a time with the duplicates. If I sort these descending, I see the top half, if I sort them ascending, I see the bottom half. Because the above SQL code sorts after removing the duplicates, I always see the bottom half. I am unsure if this restriction is due to the ODBC link

I believe I need to find a way of sorting descending first, and then using the DISTINCT or GROUP BY function after. I only really need to see the top half (most recent records) but I keep getting a syntax error when attempting this.

Any help would be greatly appreciated.

Regards,
Gareth

 

[Guus2005]

Using the distinct keyword in combination with the sort command gives you undesired results. If you are using the group by clause, the results won't be different i guess.

How can you tell which are the old records and which are the new records?
Do you want to keep the old records?
Obviously there is a flaw in your database design. There are duplicate "unique" ID's?

HTH