Conflict between Order By and Group By/Distinct

[Gareth Jones]

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 believe I need to find a way of sorting descending first, and then using the DISTINCT or GROUP BY function after. I keep getting a syntax error when attempting this.

Any help would be greatly appreciated.

Regards,
Gareth

[orangeCat]

Distinct does NOT remove anything. It really brings back only 1 records for the field involved.

If you want to count the duplicates/replicates you could

SELect count(possible dup field) from Table

If the field is intended to be the primary key, you appear to have some structure issues.

[Gareth Jones]

Thanks for the reply.

The DISTINCT is doing exactly what I need in the query except it is doing the bottom half of my table instead of the top. I believe this is because this is listed before the sort in the SQL as below.

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

I believe I need to be able to sort descending, and then use DISTINCT, I may be wrong though.

Thanks
Gareth

[Rabbit]

What do you mean top half and bottom half of the table? There's nothing in the query limiting the number of records being returned so it should return everything that is distinct.

[Gareth Jones]

For some reason I can only ever see 12000 records at any one time.

If I sort descending, I see rows 142000 down to 130000. If I sort ascending, I see rows 90000 to 102000

I can never see 142000 to 90000. Luckily however this is not a major problem as the top 12000 would be fine for me.

I have tried querying using different fields from the table, and it only returns 12000 records at any one time. When I use DISTINCT, it reduces this down to approx 1900 which is correct. Its just the bottom half of the table.

[Gareth Jones]

Thanks all for your help. I managed to solve this one by creating static tables from the ODBC link. Using Append and make table queries it is now working.

[NeoPa]

Indeed Gareth. I suspect this issue is related to the ODBC link itself. It is certainly not standard behaviour (as you found when using it with your static table). The properties of your query could also have had a limit set, but that generally displays in the SQL seen as the TOP predicate (which was absent from your posted code so I doubt that's the case here). All things point to an issue with the specific ODBC link (which can come in all shapes and sizes of course).