Access 2010 - Problem sorting and averaging 1

[jvsg23]

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.

 

[dhookom]

Try convert the numerator or denominator to a double:

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

 

[jvsg23]

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

 

[dhookom]

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

 

[LimitedTech]

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.

 

[SkipVought]

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

 

[SkipVought]

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

 

[jvsg23]

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

 

[dhookom]

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

 

[jvsg23]

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.

 

[dhookom]

Try:

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.
[pre]
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[/pre]


Duane
Hook'D on Access
MS Access MVP

 

[jvsg23]

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.

 

[Andrzejek]

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.

 

[jvsg23]

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.