Group by pulling incorrect results

[jpawson74]

Hi all, i'm struggling with the simplest of queries, i'm using one table and grouping on two fields.

Table
ID Term


123 84
123 84
123 84

Query returns
ID Term
123 84
123 12

How on earth is it possible to return two records and one with 12 as the term when it doesn't exist against ID 123

[pbaldy]

I don't see how it could. Can you post a sample db here that does this?

[jpawson74]

Unfortunately the tables in question are odbc linked so i'm unable to post a sample db at present. My colleague has tried the same query on his pc and the same occurs. Select queries without groupings produce the correct results but as soon as you add the groupings this strange result occurs, same happens if you change the query to a make table query. Absolutely stumped.

[pbaldy]

Does this return anything?

SELECT ID, Term
FROM TableName
WHERE ID = 123 And Term = 12

Replacing with the appropriate names/values of course.

[jpawson74]

Yes it returns one record but strangely enough it brings back the term 36

[pbaldy]

That seems impossible. I suspected there was a record with 12, but I can't imagine how a record with 36 could be returned by that query. Can you import the relevant table into a db and post it here? We don't want any sensitive data of course, but something is wacky in the data here.

[jpawson74]

I've signed off for the day so will try and upload a file tomorrow, many thanks for your help.

[jpawson74]

Update, when importing the table in question to SQL Server this displays the correct data (2 records with term of 36 and 1 record with 12) but if I link to this newly created table in SQL Server from access odbc this reverts back to displaying all 3 records terms of 36 so it must be an odbc issue and not a data issue.