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

Heenan, Lambert Lambert.Heenan at aig.com
Thu Oct 1 08:06:09 CDT 2015


That's "SELECT DISTINCT" of course. Too early in the morning for my spelling neurons to function! 



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, October 01, 2015 8:57 AM
To: 'Access-D Email\aaccessd at databaseadvisors.com''
Subject: Re: [AccessD] Access / Oracle Distinct queries - Distinct not working!

No. It's just a plain vanilla DELECT DISTINCT FROM... query.

If I do 

Select APLICANT_ID from MyQuery Group By Applicant_ID

Then I get the right number back.

Lambert


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, September 30, 2015 6:50 PM
To: 'Access-D Email\aaccessd at databaseadvisors.com''
Subject: Re: [AccessD] Access / Oracle Distinct queries - Distinct not working!

Do you have GROUP BY APPLICANT_ID in the new query?

On 30 Sep 2015 at 16:04, Heenan, Lambert wrote:

> 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
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list