INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access 2010 - Problem sorting and averaging

Access 2010 - Problem sorting and averaging

(OP)
Hi,
I've sometimes broused my way to an answer here, but this time I had to join and ask the question myself.

I'm using Access2010 on a Windows7 PC. The data is from an attached (via ODBC) SQL Server 2005 table,
named "Logbook", which contains some 1/4 million records.

I have a query which calculates a field for AverageWeight,
i.e. [TotalWeight]/[NumberItems] --> [AverageWeight]
It looks like this:
SELECT Weight, NumCount, [Weight]/[NumCount] AS WgtPer
FROM Logbook
WHERE ((Weight>0) AND (NumCount>0) AND (Area=15) AND (SubArea=3) AND (Code=1) AND (SubCode)=1) AND (Year=2012) AND (Counted=Yes))
ORDER BY [Weight]/[NumCount];

The query result produces 104 records, with WgtPer ranging from 1.000 to 3.441

Problem 1) When I ask it to Sort (Order By) on field [WgtPer], it doesn't do so correctly.
It appears to only sort on the Integer portion of the number, so the 1.xxx will appear first,
then the 2.xxx and finally the 3.xxx, but in all cases, the .xxx part is NOT sorted.
Is this normal expected behaviour ? If so, I don't understand why.
Also, if I take the above query and turn it into a MakeTable query, and create a little temp table,
then ask a new query to use that and Sort on WgtPer column, THEN it does it correctly.

Problem 2) If I use the (original) above query as the input to a new query, and ask for the Average value,
(ie select "Avg" in the Total option for field [WgtPer]), it gives the result 1.144 which is clearly Wrong. Why ?
If I again make the first query a MakeTable query and output the results to a Table,
and then base the second query on that table and ask for the "Avg", it returns the very reasonable
looking 1.734.

If Access is looking at the same 104 values in a Query as in a Table, WHY are the results different ?

My faith in Access has taken a beating today.

Thank You in advance to any Access gurus with answers.

RE: Access 2010 - Problem sorting and averaging

Try convert the numerator or denominator to a double:

CODE --> sql

SELECT Weight, NumCount, CDbl([Weight])/[NumCount] AS WgtPer
FROM Logbook
WHERE ((Weight>0) AND (NumCount>0) AND (Area=15) AND (SubArea=3) AND (Code=1) 
  AND (SubCode)=1) AND (Year=2012) AND (Counted=Yes))
ORDER BY CDbl([Weight])/[NumCount]; 

Duane
Hook'D on Access
MS Access MVP

RE: Access 2010 - Problem sorting and averaging

(OP)
Hi,
Interesting suggestion re: CDbl(), one that I hadn't tried.
Unfortunately, it made no discernible difference.

RE: Access 2010 - Problem sorting and averaging

Is there a report involved in your question?
When you view the query datasheet, is the WgtPer column left or right aligned? Do you see the full decimal places?

Duane
Hook'D on Access
MS Access MVP

RE: Access 2010 - Problem sorting and averaging

Could the original table have the weight formatted as text? Not sure if it would cause the exact problem but it would explain the sorting 1s first the 2s etc.

RE: Access 2010 - Problem sorting and averaging

The 1s sort before the 2s? Hmmm, very interesting captain.

RE: Access 2010 - Problem sorting and averaging

...but if 11 sorts before 2, you have text.

RE: Access 2010 - Problem sorting and averaging

(OP)
Hi,
Report ? Nope, no report involved, just a raw query.
Text ? Nope, nothing involved is formatted text.
Yes, I can see the decimal places.
Results look like this:

Weight NumCount WgtPer
297 165 1.800000
355 198 1.792929
304 162 1.876543
152 92 1.652174
363 152 2.388158
570 247 2.307692
209 93 2.247312
302 139 2.172662
252 123 2.048780
353 162 2.179012
151 66 2.287879
591 258 2.290698
542 221 2.452489
595 292 2.037671
347 165 2.103030

...etc. It "sorts" on the "integer" part, but the floating point part is pretty random looking.

It also doesn't appear to be a quirk of my PC, as I tried the query on a co-workers laptop,
and it did the same thing there too.

Does anyone know if, when Access forwards the request to the SQL-Server back end (via ODBC)
if the "sort" is requested, and Access just displays what is returned, or does Access
"sort" the returning records itself ??

RE: Access 2010 - Problem sorting and averaging

I have seen issues with decimal data types. What are the data types in SQL and how does Access map them when linked?

Duane
Hook'D on Access
MS Access MVP

RE: Access 2010 - Problem sorting and averaging

(OP)
Well, I see my little query output looks a lot more squishy when actually posted
than when I put it in here to begin with.
Anyhow,
The [Weight] and [NumCount] are both Integers, in both Access and SQL Server.
The [WgtPer] is a calculated field created from them,
[WgtPer] = [Weight]/[NumCount]
as shown at the top in my original post.

RE: Access 2010 - Problem sorting and averaging

Try:

CODE --> SQL

SELECT Weight, NumCount, CDbl([Weight])/CDbl([NumCount]) AS WgtPer
FROM Logbook
WHERE ((Weight>0) AND (NumCount>0) AND (Area=15) AND (SubArea=3) AND (Code=1) 
  AND (SubCode)=1) AND (Year=2012) AND (Counted=Yes))
ORDER BY CDbl([Weight])/CDbl([NumCount]); 


Use the TGML Pre tag to align columns and then always Preview.
Weight NumCount     WgtPer
 297    165       1.800000
 355    198       1.792929
 304    162       1.876543
 152    92        1.652174
 363    152       2.388158
 570    247       2.307692
 209    93        2.247312
 302    139       2.172662
 252    123       2.048780
 353    162       2.179012
 151    66        2.287879
 591    258       2.290698
 542    221       2.452489
 595    292       2.037671
 347    165       2.103030 

Duane
Hook'D on Access
MS Access MVP

RE: Access 2010 - Problem sorting and averaging

(OP)
Hi,
That WORKED! Awesome.
Your first suggestion to double one or the other didn't work,
but double Both of them did.
It Also fixed the averaging problem too.

I'm not 100% sure WHY it works, but I'm happy that it does.
For now, I'm just going to go off and have a weekend.

Thank You very much to all who answered.

RE: Access 2010 - Problem sorting and averaging

To show appreciation for help received and let others know which post was helpful, please use Great post? Star it! link.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Access 2010 - Problem sorting and averaging

(OP)
Hi,
I had clicked the "Great Post? Star it!", but apparently I failed to notice the pop-up
saying "Click here to confirm". So I've done that now. Hope it's good.

Also, the query works fine if you convert to Single instead of double.
So the answer appears to be "convert to floating point".
It appears that if Access sees an integer numerator and integer denominator,
even though it converts the result to floating point, it still wants to sort
as if the result was an integer too.
I expect the same was true for the averaging problem as well, although I haven't
looked into that yet.
Verrrrrrryyy interesting.

Anyhow, thanks again everybody.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close