Sort by more than one field

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

Guest

I have specified sort ascending in two different fields. (Report Date and
Report Number.)
The query is only sorting by the first field and ignoring the second.
What to do?
 
It's not a SQL database, it's a mdb.
In the query, I have sort ascending on two different fields.
it sorts by the first (date field) but does not sort by the second (report
number field).
 
Open the query in design view. Click on menu VIEW - SQL View. Highlight the
SQL statement that is in the query, copy and paste in a post.
 
SELECT Customer.FacilityName, Customer.CityStateZip, AccountTime.DATE,
AccountTime.SOR, ServiceEngineers.Initials, AccountTime.[Service Requested],
AccountTime.[Service Performed], AccountTime.CUSTOMER, AccountTime.[BILLED
REG HRS], AccountTime.[BILLED OT HRS], AccountTime.[BILLED REG TRA],
AccountTime.[BILLED OT TRA], AccountTime.[OC BILLED PARTS], AccountTime.[LIST
NC PARTS], AccountTime.[BILLED FREIGHT], AccountTime.[LABOR RATE],
AccountTime.[NC REG HRS], AccountTime.[NC OT HRS], AccountTime.[NC OT TRA],
AccountTime.[OC NC PARTS], AccountTime.[NC FREIGHT], AccountTime.[NC REG
TRA], AccountTime.RM, AccountTime.*
FROM ServiceEngineers INNER JOIN (Customer INNER JOIN AccountTime ON
Customer.CustomerID = AccountTime.CUSTOMER) ON ServiceEngineers.EngineerID =
AccountTime.ENGINEER
WHERE (((Customer.FacilityName)=[Customer?]) AND ((AccountTime.DATE) Between
[Start Date (Use 0/0/00 format)] And [Stop Date]))
ORDER BY AccountTime.DATE, AccountTime.SOR;
 
SELECT Customer.FacilityName, Customer.CityStateZip, AccountTime.DATE,
AccountTime.SOR, ServiceEngineers.Initials, AccountTime.[Service Requested],
AccountTime.[Service Performed], AccountTime.CUSTOMER, AccountTime.[BILLED
REG HRS], AccountTime.[BILLED OT HRS], AccountTime.[BILLED REG TRA],
AccountTime.[BILLED OT TRA], AccountTime.[OC BILLED PARTS], AccountTime.[LIST
NC PARTS], AccountTime.[BILLED FREIGHT], AccountTime.[LABOR RATE],
AccountTime.[NC REG HRS], AccountTime.[NC OT HRS], AccountTime.[NC OT TRA],
AccountTime.[OC NC PARTS], AccountTime.[NC FREIGHT], AccountTime.[NC REG
TRA], AccountTime.RM, AccountTime.*
FROM ServiceEngineers INNER JOIN (Customer INNER JOIN AccountTime ON
Customer.CustomerID = AccountTime.CUSTOMER) ON ServiceEngineers.EngineerID =
AccountTime.ENGINEER
WHERE (((Customer.FacilityName)=[Customer?]) AND ((AccountTime.DATE) Between
[Start Date (Use 0/0/00 format)] And [Stop Date]))
ORDER BY AccountTime.DATE, AccountTime.SOR;
Click to expand...

Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

After you change the Date field name in your table to something else,
correct your query, then try again. That may resolve the sort problem.
 
I changed the field name to DTE in the table and the query. No change in
sort problem.

SELECT AccountTm.*, Customer.FacilityName, Customer.CityStateZip,
AccountTm.DTE, AccountTm.SOR, ServiceEngineers.Initials
FROM ServiceEngineers INNER JOIN (Customer INNER JOIN AccountTm ON
Customer.CustomerID = AccountTm.CUSTOMER) ON ServiceEngineers.EngineerID =
AccountTm.ENGINEER
WHERE (((Customer.FacilityName)=[Customer?]) AND ((AccountTm.DTE) Between
[Start Date (Use 0/0/00 format)] And [Stop Date]))
ORDER BY AccountTm.DTE, AccountTm.SOR;

--
Radman


fredg said:
SELECT Customer.FacilityName, Customer.CityStateZip, AccountTime.DATE,
AccountTime.SOR, ServiceEngineers.Initials, AccountTime.[Service Requested],
AccountTime.[Service Performed], AccountTime.CUSTOMER, AccountTime.[BILLED
REG HRS], AccountTime.[BILLED OT HRS], AccountTime.[BILLED REG TRA],
AccountTime.[BILLED OT TRA], AccountTime.[OC BILLED PARTS], AccountTime.[LIST
NC PARTS], AccountTime.[BILLED FREIGHT], AccountTime.[LABOR RATE],
AccountTime.[NC REG HRS], AccountTime.[NC OT HRS], AccountTime.[NC OT TRA],
AccountTime.[OC NC PARTS], AccountTime.[NC FREIGHT], AccountTime.[NC REG
TRA], AccountTime.RM, AccountTime.*
FROM ServiceEngineers INNER JOIN (Customer INNER JOIN AccountTime ON
Customer.CustomerID = AccountTime.CUSTOMER) ON ServiceEngineers.EngineerID =
AccountTime.ENGINEER
WHERE (((Customer.FacilityName)=[Customer?]) AND ((AccountTime.DATE) Between
[Start Date (Use 0/0/00 format)] And [Stop Date]))
ORDER BY AccountTime.DATE, AccountTime.SOR;
Click to expand...

Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

After you change the Date field name in your table to something else,
correct your query, then try again. That may resolve the sort problem.
Click to expand...
 
It sorted for me - see below.
AccountTime.DATE AccountTime.SOR
1/1/2005 2
1/1/2005 5
2/2/2005 2
2/2/2005 5
3/3/2005 1
3/3/2005 3
12/5/2006 1
12/5/2006 3

Radman said:
SELECT Customer.FacilityName, Customer.CityStateZip, AccountTime.DATE,
AccountTime.SOR, ServiceEngineers.Initials, AccountTime.[Service Requested],
AccountTime.[Service Performed], AccountTime.CUSTOMER, AccountTime.[BILLED
REG HRS], AccountTime.[BILLED OT HRS], AccountTime.[BILLED REG TRA],
AccountTime.[BILLED OT TRA], AccountTime.[OC BILLED PARTS], AccountTime.[LIST
NC PARTS], AccountTime.[BILLED FREIGHT], AccountTime.[LABOR RATE],
AccountTime.[NC REG HRS], AccountTime.[NC OT HRS], AccountTime.[NC OT TRA],
AccountTime.[OC NC PARTS], AccountTime.[NC FREIGHT], AccountTime.[NC REG
TRA], AccountTime.RM, AccountTime.*
FROM ServiceEngineers INNER JOIN (Customer INNER JOIN AccountTime ON
Customer.CustomerID = AccountTime.CUSTOMER) ON ServiceEngineers.EngineerID =
AccountTime.ENGINEER
WHERE (((Customer.FacilityName)=[Customer?]) AND ((AccountTime.DATE) Between
[Start Date (Use 0/0/00 format)] And [Stop Date]))
ORDER BY AccountTime.DATE, AccountTime.SOR;

--
Radman


KARL DEWEY said:
Open the query in design view. Click on menu VIEW - SQL View. Highlight the
SQL statement that is in the query, copy and paste in a post.
Click to expand...
Click to expand...
 
It works now for me as well.
Issue resolved.
Thank you for your assistance.
Steve
--
Radman


KARL DEWEY said:
It sorted for me - see below.
AccountTime.DATE AccountTime.SOR
1/1/2005 2
1/1/2005 5
2/2/2005 2
2/2/2005 5
3/3/2005 1
3/3/2005 3
12/5/2006 1
12/5/2006 3

Radman said:
SELECT Customer.FacilityName, Customer.CityStateZip, AccountTime.DATE,
AccountTime.SOR, ServiceEngineers.Initials, AccountTime.[Service Requested],
AccountTime.[Service Performed], AccountTime.CUSTOMER, AccountTime.[BILLED
REG HRS], AccountTime.[BILLED OT HRS], AccountTime.[BILLED REG TRA],
AccountTime.[BILLED OT TRA], AccountTime.[OC BILLED PARTS], AccountTime.[LIST
NC PARTS], AccountTime.[BILLED FREIGHT], AccountTime.[LABOR RATE],
AccountTime.[NC REG HRS], AccountTime.[NC OT HRS], AccountTime.[NC OT TRA],
AccountTime.[OC NC PARTS], AccountTime.[NC FREIGHT], AccountTime.[NC REG
TRA], AccountTime.RM, AccountTime.*
FROM ServiceEngineers INNER JOIN (Customer INNER JOIN AccountTime ON
Customer.CustomerID = AccountTime.CUSTOMER) ON ServiceEngineers.EngineerID =
AccountTime.ENGINEER
WHERE (((Customer.FacilityName)=[Customer?]) AND ((AccountTime.DATE) Between
[Start Date (Use 0/0/00 format)] And [Stop Date]))
ORDER BY AccountTime.DATE, AccountTime.SOR;

--
Radman


KARL DEWEY said:
Open the query in design view. Click on menu VIEW - SQL View. Highlight the
SQL statement that is in the query, copy and paste in a post.

:

It's not a SQL database, it's a mdb.
In the query, I have sort ascending on two different fields.
it sorts by the first (date field) but does not sort by the second (report
number field).
--
Radman


:

Post your SQL statement.

:

I have specified sort ascending in two different fields. (Report Date and
Report Number.)
The query is only sorting by the first field and ignoring the second.
What to do?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help sorting a query 2
Sorting 0
Random Function for Selecting Records 3
Retrieve "up to #" records in a select query 9
Sorting in select query. manually 1
Preferred way to do sort order - column in table, or specified in query? 3
Access Dcount (multiple criteria) 3
complex query to pull unique values 6

Back
Top