2Sort is applying filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am using Access 2003. I have created a select query using 2 tables, one
field from each table is in the grid. I am showing customers and products
ordered

When I run the query all 100 records show up as expected. Some of the values
are duplicated, for example one customer might have ordered the same product
on a different day.

However, when I sort the query by customer name, only 81 records show up.
Access has removed the duplicate orders.

The Unique Values property is set to NO, so is the Unique Records property.

Any idea on what is going on?
 
Hi

I am using Access 2003. I have created a select query using 2 tables, one
field from each table is in the grid. I am showing customers and products
ordered

When I run the query all 100 records show up as expected. Some of the values
are duplicated, for example one customer might have ordered the same product
on a different day.

However, when I sort the query by customer name, only 81 records show up.
Access has removed the duplicate orders.

The Unique Values property is set to NO, so is the Unique Records property.

Any idea on what is going on?
Click to expand...

Might you have a Group By on? Is the Greek Sigma icon selected?

If not - please post the SQL view of the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Hi John:

The query is very simple:

SELECT tblBookProject.BookTitle, tblCustomer.CustomerName
FROM tblCustomer INNER JOIN (tblBookProject INNER JOIN tblBookSales ON
tblBookProject.[Part#] = tblBookSales.PartNumber) ON tblCustomer.CustomerID
= tblBookSales.CustomerID;

If you sort in design view, you keep 100 records. If you sort in datasheet,
only 81 appear........

Hi

I am using Access 2003. I have created a select query using 2 tables, one
field from each table is in the grid. I am showing customers and products
ordered

When I run the query all 100 records show up as expected. Some of the values
are duplicated, for example one customer might have ordered the same product
on a different day.

However, when I sort the query by customer name, only 81 records show up.
Access has removed the duplicate orders.

The Unique Values property is set to NO, so is the Unique Records property.

Any idea on what is going on?
Click to expand...

Might you have a Group By on? Is the Greek Sigma icon selected?

If not - please post the SQL view of the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
By any chance do you have a filter applied in the datasheet view?

Try Clearing the sort and filters and then re-establishing the sort.
Hi John:

The query is very simple:

SELECT tblBookProject.BookTitle, tblCustomer.CustomerName
FROM tblCustomer INNER JOIN (tblBookProject INNER JOIN tblBookSales ON
tblBookProject.[Part#] = tblBookSales.PartNumber) ON tblCustomer.CustomerID
= tblBookSales.CustomerID;

If you sort in design view, you keep 100 records. If you sort in datasheet,
only 81 appear........

Hi

I am using Access 2003. I have created a select query using 2 tables, one
field from each table is in the grid. I am showing customers and products
ordered

When I run the query all 100 records show up as expected. Some of the values
are duplicated, for example one customer might have ordered the same product
on a different day.

However, when I sort the query by customer name, only 81 records show up.
Access has removed the duplicate orders.

The Unique Values property is set to NO, so is the Unique Records property.

Any idea on what is going on?
Click to expand...

Might you have a Group By on? Is the Greek Sigma icon selected?

If not - please post the SQL view of the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
;-) No such luck with the filters. I have made sure they are removed in the
query and the three underlying tables.

This is a database that I use for training purposes, it was built by the
publisher (who shall remain nameless at this point). Imagine the absolute
horror of my students when they sort a query and a filter (or something) is
apparently applied with no notification or warning.

Any other thoughts?!?!?

Thanks

By any chance do you have a filter applied in the datasheet view?

Try Clearing the sort and filters and then re-establishing the sort.
Hi John:

The query is very simple:

SELECT tblBookProject.BookTitle, tblCustomer.CustomerName
FROM tblCustomer INNER JOIN (tblBookProject INNER JOIN tblBookSales ON
tblBookProject.[Part#] = tblBookSales.PartNumber) ON tblCustomer.CustomerID
= tblBookSales.CustomerID;

If you sort in design view, you keep 100 records. If you sort in datasheet,
only 81 appear........

Hi

I am using Access 2003. I have created a select query using 2 tables, one
field from each table is in the grid. I am showing customers and products
ordered

When I run the query all 100 records show up as expected. Some of the values
are duplicated, for example one customer might have ordered the same product
on a different day.

However, when I sort the query by customer name, only 81 records show up.
Access has removed the duplicate orders.

The Unique Values property is set to NO, so is the Unique Records property.

Any idea on what is going on?
Click to expand...

Might you have a Group By on? Is the Greek Sigma icon selected?

If not - please post the SQL view of the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top