Solved

Sort 1-100 correctly

Posted on 2008-10-10
9
290 Views
Last Modified: 2013-11-27
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!
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;

Open in new window

0
Comment
Question by:TTCLIVE
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 22691181
Can you provide your sample table data and yoru desired result.
In your query, the usage of string 'CO' in GROUP BY and ORDER BY clauses is wrong.
Better explain your problem in detail.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 22691197
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
0
 

Author Comment

by:TTCLIVE
ID: 22691232
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
0
 
LVL 32

Expert Comment

by:aleghart
ID: 22691353
Your field type is 'text'.
Open in Access and change to 'number'.
Now it will sort correctly as a number.
Need sample MDB file?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 32

Expert Comment

by:aleghart
ID: 22691384
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
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22691546
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.

0
 

Author Comment

by:TTCLIVE
ID: 22702399
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?
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);

Open in new window

0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 250 total points
ID: 22702524
yes you dont have group by clausule and you use aggragete function so your query should be:


SELECT [CO] AS Trunk, Count([Extension]) AS [Total Calls]

FROM AllInfo

WHERE (((AllInfo.IO)='Out') AND ((AllInfo.Date)<#10/10/2008#))

GROUP BY [CO]

ORDER BY RIGHT('0000' & AllInfo.CO,5)

Open in new window

0
 

Author Closing Comment

by:TTCLIVE
ID: 31505204
Thank you so much!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now