[AccessD] Access / Oracle Distinct queries - Distinct not working!

[Heenan, Lambert]

Cross Posted to Access-l and Access-d.

Here's a very strange thing. I have a pass-through query that connects to an Oracle Db and returns a set of rows with Applicant IDs, and Medical Review IDs (MUR). There can be, and are multiple MUR IDs per Applicant ID.

When I run the query in Access 2010 I get 8,429 rows back. When I use the Pass-through as the source for a new query and run SELECT DISTINCT APPLICANT_ID FROM MyQuery, to find out the number of unique applicants in the dataset then that query also returns 8,429 rows, and I see Applicant IDs repeated in the results. Distinct has had no effect.

On the other hand, if I run the exact same select distinct query in Toad, then I get back the expected result: 3,829 unique applicants. Likewise, if I use the first pass-through query as toe source for a make-table query and the run SELECT DISTINCT on the temporary table just created, then I get 3,829 rows.

What gives?

Lambert