Unexpected results when sorting multi-table query using toolbar button

[JulieS]

Good day,

When creating a running multi-table select query (details below) and
then using the toolbar buttons to sort data in the datasheet, records
returned by query are removed.

Tables:

tblEmployees
EmpID (PK) (Autonumber)
LastName

tblManufacturers
MfgID (PK) (Autonumber)
MfgName

tblComputers
CompID (PK) (Autonumber)
EmpID (FK)
MfgID (FK)
PurchaseDate

I've added records where an employee has multiple computers with
different purchase dates but several of the computers are by the same
manufacturer.

Original query:
SELECT tblEmployees.LastName, tblManufacturers.MfgName
FROM tblManufacturers INNER JOIN (tblEmployees INNER JOIN tblComputers
ON tblEmployees.EmpID=tblComputers.EmpID) ON
tblManufacturers.MfgID=tblComputers.MfgID;

The query correctly returns the records of employees with computers
and the name of the manufacturer of the computer. The last name -
manufacturer name combination is duplicated as expected due to an
employee having more than one computer made by the same manufacturer.

After sorting by LastName using the A-Z or Z-A buttons in the toolbar
in datasheet view of the query, records where the LastName and
Manufacturer name are duplicated disappear from the datasheet view.

Query after sorting using toolbar button:
SELECT tblEmployees.LastName, tblManufacturers.MfgName
FROM tblManufacturers INNER JOIN (tblEmployees INNER JOIN tblComputers
ON tblEmployees.EmpID=tblComputers.EmpID) ON
tblManufacturers.MfgID=tblComputers.MfgID;

If I copy the SQL of the query and create a new query, all records are
returned.

If I return to the original query and remove the Order By property in
the query's property sheet, all records are returned once again. If I
sort using the query design grid, all records are returned.

Environment:
Windows XP Pro SP-2
Access 2003 SP-3

I also get the same result using

Windows SP Pro SP-2
Access 2007 SP-1
running in VPC

I've searched google groups and have found one or two posts with the
same question but I haven't seen an answer. Does anyone have a
possible explanation?

Thank you,
Julie

 

[Allen Browne]

Julie that's a straightforward query. I can't see how JET would get that
wrong.

It would be worth doing a compact/repair, just in case there is a damaged
index that gets used when you resort the query.

If the LastName field is indexed on tblEmployees, open the table in design
view and remove that index temporaraly, just to double-check the issue is
not to do with indexes (since this is what you are sorting on.)

If that doesn't solve it, please post the count of records in the 3 table,
and the count of records from the original query and the resorted query.
Also details of the data types: are the foreign keys Number (size Long
Integer)?

If none of those ideas lead you a useful direction, post back. Especially as
you say others have reported a similiar issue, and you have a simple
reproducable scenario, this is important to track down.

 

[JulieS]

Hello Allen,

Thanks very much for responding.

I have compacted and repaired the database, created a new query as
below and the same issue recurs.

The tblEmployees is was not indexed on LastName. I indexed the table
on LastName and created another query with the same definition. Same
issue after sorting using the toolbar button.

The databases are simple test databases I created in both 2003 and
2007 with the following record counts:

tblEmployees - 7 records (EmpID LastName)
1 Greene
2 Browne
3 Smith
4 White
5 Reed
6 Blue
7 Black

tblManufacturers - 5 records (MfgID MfgName)
1 Dell
2 Compac
3 Lenovo
4 IBM
5 HP

tblComputers - 6 records (CompID EmpID MfgID PurchaseDate)
1 1 1 7/12/2008
2 2 1 6/15/2008
3 1 3 8/22/2007
4 2 1 6/22/2008
5 4 2 1/1/2007
6 4 2 1/16/2008


The original query returns 6 records. (LastName MfgName)

Green Dell
Brown Dell
Green Lenovo
Browne Dell
White Compac
White Compac

The sorted query returns 4 records. (LastName MfgName)

Browne Dell
Green Lenovo
Green Dell
White Compac

Sorting the query has removed the duplicate records. If I add the
PurchaseDate from the tblComputers to the query, all 6 records return
as expected.

The foreign key fields (EmpID and MfgID) in the tblComputers are
number long integer. Referential integrity is set on all table joins
and they are all inner joins.

Needless to say, not what I expected and given what I know about
Access (not a great deal), I couldn't come up with plausible reasons
for the results.

Thank you,
Julie

 

[JulieS]

Some additional information to add to the existing oddities:

I created a new query named qryBadToo with the exact same definition.
Before saving the query, I sorted by LastName and checked the Order By
property of the query -- it reads "Query1.LastName". After saving the
query, I expected to see the Order By property change to
qryBadToo.LastName. It remained Query1.LastName. I exited the db,
re-opened the query in design view -- no change.

I tried again creating another query (qryBadAlso), this time with Name
AutoCorrect disabled. Same result, the Order By property reads
"Query1.LastName". If I delete the value in the Order By property all
records are returned in the recordset. If I sort the query using the
toolbar buttons, the duplicate records are removed and the Order By
property now includes the correct query name. Again, the odd thing is
the SQL of the query does not include the expected Order By syntax.

The same issues occur if I sort by MfgName using the toolbar button.

Julie

 

[Allen Browne]

Julie, would you like to zip the database and email it to me? I would be
interested in seeing what's going on. Might take me a couple of days to get
to it, but I would like to see the problem.

My email address is munged so don't just hit reply: deduce it from the
signature below.

 

[JulieS]

It's on its way. Thank you Allen.

Julie

"Allen Browne" wrote in message

Julie, would you like to zip the database and email it to me?

<snip>