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

[Bill Benson]

Never a waste of time. I learned about the difference between Access's
Unique Records and Unique Values relative to DISTINCTROW and DISTINCT,
respectively, properties in the design window which I had forgotten about.
And that DISTINCTROW is not for single tables but for joined table queries
at least in the Access environment.
Brain improved.

On Thu, Oct 1, 2015 at 10:39 AM, Heenan, Lambert <Lambert.Heenan at aig.com>
wrote:

> OK. All bets are off. Today when I run the 'problem' query it returns the
> correct result! I am 99.99% sure that yesterday I ran
>
> SELECT DISTINCT APPLICANT_ID FROM MyQuery
>
> And not
>
> SELECT DISTINCTROW APPLICANT_ID FROM MyQuery
>
> But today DISTINCT is doing what it's supposed to do!!!
>
> Sorry if I wasted anyone's time.
>
>
>
> -----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
>