Sort by more than one field

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?
 
G

Guest

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).
 
G

Guest

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.
 
G

Guest

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;
 
F

fredg

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.
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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
Access MSACCESS - Sorting report based on field calculated in the report 0
Random Function for Selecting Records 3
Parameter Query 5
Sort order by customer name not working in query 5
Crosstab query date field sorted by day instead of month 1
Retrieve "up to #" records in a select query 9
alphabetic sort problem 12

Top