QuickSort Appears to be Filtering - Please Advise

I have noticed an odd problem and I'm wondering if anyone can shed
light on it. Perhaps it is a bug, or maybe it is a characteristic of
the QuickSort feature that I wasn't aware of.

I have a query that returns 53 records. However, when I run the
QuickSort, the number of records in the datasheet changes to 50
records. If I use Remove Filter/Sort, it changes back to 53. (FYI,
this query is based on three related tables, and there are two
parameters/criteria in the design.)

So far, I can see that the three records being filtered out are
duplicate lines, so that must have something to do with it. I did try
specifying the sort in the query design window instead, and that works
just fine, returning the 53 records.

I provide training and support to Access newbies, and I know that this
issue will come up for them. Can anyone advise as to what is happening
and why?

Thanks.

Re: QuickSort Appears to be Filtering - Please Advise

If the query contains outer joins, it can be caused by a change in the order
in which the outer joins are being executed.

The behavior can also be caused by a bad index:
http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries

If neither of those apply, post the SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eric" wrote in message
news:1193083744.607581.156340@e34g2000pro.googlegroups.com.. .
>I have noticed an odd problem and I'm wondering if anyone can shed
> light on it. Perhaps it is a bug, or maybe it is a characteristic of
> the QuickSort feature that I wasn't aware of.
>
> I have a query that returns 53 records. However, when I run the
> QuickSort, the number of records in the datasheet changes to 50
> records. If I use Remove Filter/Sort, it changes back to 53. (FYI,
> this query is based on three related tables, and there are two
> parameters/criteria in the design.)
>
> So far, I can see that the three records being filtered out are
> duplicate lines, so that must have something to do with it. I did try
> specifying the sort in the query design window instead, and that works
> just fine, returning the 53 records.
>
> I provide training and support to Access newbies, and I know that this
> issue will come up for them. Can anyone advise as to what is happening
> and why?
>
> Thanks.

Re: QuickSort Appears to be Filtering - Please Advise

Thanks, Allen. I don't think the query contains any outer joins. At
least I didn't explicitly define any.

And I'm not sure if the "bad index" issue applies, since that
discusses tables. Running the QuickSort on my tables doesn't seem to
change the number of records.

Could it be that QuickSort uses the UniqueValues property, set to Yes?
That was the conclusion I was coming to.

Below is my SQL statement. Also note that I am not an Access expert,
nor a professional Access developer. Compared to many of those on this
list, I only understand the bare fundamentals. For example, I rarely
edit SQL or VBA code. Please keep this in mind when replying. Thanks!

SELECT [Patient Table].FirstName, [Patient Table].LastName, [Patient
Table].Address, [Patient Table].City, [Patient Table].State, [Patient
Table].Zip, [Procedure Table].Procedure
FROM [Procedure Table] INNER JOIN ([Patient Table] INNER JOIN [Visit
Table] ON [Patient Table].PatientID = [Visit Table].PatientID) ON
[Procedure Table].ProcedureID = [Visit Table].ProcedureID
WHERE ((([Patient Table].State) Like [Which state?]) AND (([Procedure
Table].Procedure) Like [Which procedure?]))
ORDER BY [Patient Table].LastName;

-Eric


On Oct 23, 8:29 am, "Allen Browne" wrote:
> If the query contains outer joins, it can be caused by a change in the order
> in which the outer joins are being executed.
>
> The behavior can also be caused by a bad index:
> http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries
>
> If neither of those apply, post the SQL statement.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Eric" wrote in message
>
> news:1193083744.607581.156340@e34g2000pro.googlegroups.com.. .
>
> >I have noticed an odd problem and I'm wondering if anyone can shed
> > light on it. Perhaps it is a bug, or maybe it is a characteristic of
> > the QuickSort feature that I wasn't aware of.
>
> > I have a query that returns 53 records. However, when I run the
> > QuickSort, the number of records in the datasheet changes to 50
> > records. If I use Remove Filter/Sort, it changes back to 53. (FYI,
> > this query is based on three related tables, and there are two
> > parameters/criteria in the design.)
>
> > So far, I can see that the three records being filtered out are
> > duplicate lines, so that must have something to do with it. I did try
> > specifying the sort in the query design window instead, and that works
> > just fine, returning the 53 records.
>
> > I provide training and support to Access newbies, and I know that this
> > issue will come up for them. Can anyone advise as to what is happening
> > and why?
>
> > Thanks.

Re: QuickSort Appears to be Filtering - Please Advise

The query statement you posted contains inner joins only (no outer joins.)

By "QuickSort", I assume you mean that you view the query results, and
right-click a column to sort on it. This process should not de-duplicate the
query (i.e. it should not affect the Unique Values property.)

The "bad index" could still apply. Just to a compact/repair on your database
in case.

Other than that, you query looks quite standard. The word Procedure is
actuall a reserved word, so you might try adding square brackets around that
name. You could also try using "=" instead of "Like" since you are not using
wildcards.

The query would be:
SELECT [Patient Table].FirstName,
[Patient Table].LastName,
[Patient Table].Address,
[Patient Table].City,
[Patient Table].State,
[Patient Table].Zip,
[Procedure Table].[Procedure]
FROM [Procedure Table]
INNER JOIN ([Patient Table]
INNER JOIN [Visit Table]
ON [Patient Table].PatientID = [Visit Table].PatientID)
ON [Procedure Table].ProcedureID = [Visit Table].ProcedureID
WHERE (([Patient Table].State = [Which state?])
AND ([Procedure Table].[Procedure] = [Which procedure?]))
ORDER BY [Patient Table].LastName;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eric" wrote in message
news:1193161918.822627.124400@v23g2000prn.googlegroups.com.. .
> Thanks, Allen. I don't think the query contains any outer joins. At
> least I didn't explicitly define any.
>
> And I'm not sure if the "bad index" issue applies, since that
> discusses tables. Running the QuickSort on my tables doesn't seem to
> change the number of records.
>
> Could it be that QuickSort uses the UniqueValues property, set to Yes?
> That was the conclusion I was coming to.
>
> Below is my SQL statement. Also note that I am not an Access expert,
> nor a professional Access developer. Compared to many of those on this
> list, I only understand the bare fundamentals. For example, I rarely
> edit SQL or VBA code. Please keep this in mind when replying. Thanks!
>
> SELECT [Patient Table].FirstName, [Patient Table].LastName, [Patient
> Table].Address, [Patient Table].City, [Patient Table].State, [Patient
> Table].Zip, [Procedure Table].Procedure
> FROM [Procedure Table] INNER JOIN ([Patient Table] INNER JOIN [Visit
> Table] ON [Patient Table].PatientID = [Visit Table].PatientID) ON
> [Procedure Table].ProcedureID = [Visit Table].ProcedureID
> WHERE ((([Patient Table].State) Like [Which state?]) AND (([Procedure
> Table].Procedure) Like [Which procedure?]))
> ORDER BY [Patient Table].LastName;
>
> -Eric
>
>
> On Oct 23, 8:29 am, "Allen Browne" wrote:
>> If the query contains outer joins, it can be caused by a change in the
>> order
>> in which the outer joins are being executed.
>>
>> The behavior can also be caused by a bad index:
>> http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries
>>
>> If neither of those apply, post the SQL statement.
>>
>> --
>> "Eric" wrote in message
>>
>> news:1193083744.607581.156340@e34g2000pro.googlegroups.com.. .
>>
>> >I have noticed an odd problem and I'm wondering if anyone can shed
>> > light on it. Perhaps it is a bug, or maybe it is a characteristic of
>> > the QuickSort feature that I wasn't aware of.
>>
>> > I have a query that returns 53 records. However, when I run the
>> > QuickSort, the number of records in the datasheet changes to 50
>> > records. If I use Remove Filter/Sort, it changes back to 53. (FYI,
>> > this query is based on three related tables, and there are two
>> > parameters/criteria in the design.)
>>
>> > So far, I can see that the three records being filtered out are
>> > duplicate lines, so that must have something to do with it. I did try
>> > specifying the sort in the query design window instead, and that works
>> > just fine, returning the 53 records.
>>
>> > I provide training and support to Access newbies, and I know that this
>> > issue will come up for them. Can anyone advise as to what is happening
>> > and why?