TTCLIVE
asked on
Sort 1-100 correctly
I have the following query that returns the total number of calls that occurred on a single date and which phone trunk those calls were placed on (CO).
I have been Googling this for about an hour and am just more confused. I simply need to sort the results by trunk number (CO) correctly.
When I run the query, it returns results with the trunk numbers like this:
Expr1
1
2
3
39
4
40
41
42
43
44
45
46
47
48
49
5
etc...
and I would like it to be like this:
1
2
3
4
5
6
7
8
9
10
11
12
etc...
I would also like the date to be a variable if it requires rewriting this query completely, can you add that in? If it's just adding some sort of sorting line than I can figure out how to make the date a variable on my own.
Thanks in advance!
I have been Googling this for about an hour and am just more confused. I simply need to sort the results by trunk number (CO) correctly.
When I run the query, it returns results with the trunk numbers like this:
Expr1
1
2
3
39
4
40
41
42
43
44
45
46
47
48
49
5
etc...
and I would like it to be like this:
1
2
3
4
5
6
7
8
9
10
11
12
etc...
I would also like the date to be a variable if it requires rewriting this query completely, can you add that in? If it's just adding some sort of sorting line than I can figure out how to make the date a variable on my own.
Thanks in advance!
SELECT `CO` AS Expr1, Count(`Extension`) AS [count]
FROM AllInfo
WHERE (((AllInfo.Date)=#10/10/2008#) AND ((AllInfo.IO)='OUT'))
GROUP BY `CO`
ORDER BY CO;
Format your number for a 3 digit number using 0's to fill the rest of the number. This will give you number like:
001
002
....
010
...
099
100
This way, they will sort correctly.
jppinto
001
002
....
010
...
099
100
This way, they will sort correctly.
jppinto
ASKER
The table is generated by a program called Panalog and I don't have access to modify the table without breaking the program, so I just query it. The trunk number is automatically generated by my phone switch with single digit numbers.
Here is a sample database.
http://senduit.com/68ad95
Here is a sample database.
http://senduit.com/68ad95
Your field type is 'text'.
Open in Access and change to 'number'.
Now it will sort correctly as a number.
Need sample MDB file?
Open in Access and change to 'number'.
Now it will sort correctly as a number.
Need sample MDB file?
BTW, you should compact & repair database first. Your ZIP file is over 8.5MB. After compact and repair, the MDB is only 1.2MB.
db1.mdb
db1.mdb
if your field need sore some reason to stay text then you can use jppinto suggestion to use in where clausule in your query ... something like this:
SELECT AllInfo.IO, AllInfo.Date, AllInfo.CO
FROM AllInfo
WHERE (((AllInfo.IO)="in") AND ((AllInfo.Date)=#5/1/2008# ))
ORDER BY Right("0000" & AllInfo.CO, 5)
ORDER BY Right("0000" & AllInfo.CO, 5) will do the trick .. we add "0000" leading zeros and then order it by last 5 chars wich is good sort for you now.
SELECT AllInfo.IO, AllInfo.Date, AllInfo.CO
FROM AllInfo
WHERE (((AllInfo.IO)="in") AND ((AllInfo.Date)=#5/1/2008#
ORDER BY Right("0000" & AllInfo.CO, 5)
ORDER BY Right("0000" & AllInfo.CO, 5) will do the trick .. we add "0000" leading zeros and then order it by last 5 chars wich is good sort for you now.
ASKER
aleghart: I can't change the field types without breaking the program. Thanks for the tip on compressing the database first, I will remember that next time!
Cvijo123: I understand what you are saying, but I don't need to see all the results I need a count of all calls on each trunk! Whan I add ORDER BY Right("0000" & AllInfo.CO, 5) to the end of my query it gives me an error that says The name of the query is not a valid name. Make sure it does not include invalid punctuation or is not too long. So I renamed it to "1" and it gived me the same error.
I'm guessing it's a syntax problem, any ideas?
Cvijo123: I understand what you are saying, but I don't need to see all the results I need a count of all calls on each trunk! Whan I add ORDER BY Right("0000" & AllInfo.CO, 5) to the end of my query it gives me an error that says The name of the query is not a valid name. Make sure it does not include invalid punctuation or is not too long. So I renamed it to "1" and it gived me the same error.
I'm guessing it's a syntax problem, any ideas?
SELECT [`CO`] AS Trunk, Count([`Extension`]) AS [Total Calls]
FROM AllInfo
WHERE (((AllInfo.IO)="Out") AND ((AllInfo.Date)=#10/10/2008#))
ORDER BY Right("0000" & AllInfo.CO,5);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much!!
In your query, the usage of string 'CO' in GROUP BY and ORDER BY clauses is wrong.
Better explain your problem in detail.