If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unexpected results when sorting multi-table query using toolbar button
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 |
#2
|
|||
|
|||
Unexpected results when sorting multi-table query using toolbar button
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. -- 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. "JulieS" wrote in message ... 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 |
#3
|
|||
|
|||
Unexpected results when sorting multi-table query using toolbar button
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 "Allen Browne" wrote in message ... 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. -- 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. "JulieS" wrote in message ... 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 |
#4
|
|||
|
|||
Unexpected results when sorting multi-table query using toolbar button
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" wrote in message ... 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. -- 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. "JulieS" wrote in message ... 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 |
#5
|
|||
|
|||
Unexpected results when sorting multi-table query using toolbar button
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at iinet dot net dot au. "JulieS" wrote in message ... 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 "Allen Browne" wrote in message ... 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. -- 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. "JulieS" wrote in message ... 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 |
#6
|
|||
|
|||
Unexpected results when sorting multi-table query using toolbar button
It's on its way. Thank you Allen.
Julie "Allen Browne" wrote in message snip Julie, would you like to zip the database and email it to me? snip |
Thread Tools | |
Display Modes | |
|
|