SORTING...Does access sort to the tenth decimal place?

[yleeSF]

Hello!

Query: Only Store # & % Plan Hours fields.

Sorted % Plan Hours in Descending Order, but it did not sort the records to tenth decimal place correctly.

For example, Access returned the records in descending order as such.

Store# %Plan Hours
2 118.53 %
3 118.62 %

118.62 % is higher than 118.53 %. Why is it not sorting correctly? Is this a bug in Access? Can this be fixed? Please Advise.

Thanks!!!
=)

 

[Golom]

It should sort correctly if you are sorting by ONLY %Plan Hours. Is it possible that you have
[blue][tt]
ORDER BY Store#, [%Plan Hours]
[/tt][/blue]?

 

[yleeSF]

Thank You for your Quick Response.

I am positive that I have not sorted by store. How come my query doesn't sort to the nearest decimal place?

I had also show my query to a DBA and he is also stumped.

PLEASE ADVISE & THANK YOU!

 

[scriverb]

Please copy and paste your SQL code from your query here so that we may see exactly what the problem is.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[yleeSF]

Hello Bob,

Here's my SQL CODE...

SELECT dbo_store.region, dbo_store.district, dbo_store.store, dbo_store.store_name AS [Store Name], [Sales Plan % & Sits Plan %].SumOfdsittingsty, [Sales Plan % & Sits Plan %].[% Sales Plan], [Sales Plan % & Sits Plan %].[% Sit Pl], [% Plan Hours].[% Plan Hours], [Sales Plan % & Sits Plan %].SumOfdsitpl, [Sales Plan % & Sits Plan %].SumOfdsalesty, [Sales Plan % & Sits Plan %].SumOfdsalespl, [% Plan Hours].SumOftotal_actual_hours, [% Plan Hours].SumOftotal_labor_plan, [sumOftotal_actual_hours]-[SumOftotal_labor_plan] AS [Labor Variance], [start?] AS Start, [end?] AS [End]
FROM [Sales Plan % & Sits Plan %] RIGHT JOIN ([% Plan Hours] LEFT JOIN dbo_store ON [% Plan Hours].store_num = dbo_store.store) ON [Sales Plan % & Sits Plan %].store_num = [% Plan Hours].store_num
WHERE ((([% Plan Hours].[% Plan Hours])>1.05))
ORDER BY [% Plan Hours].[% Plan Hours] DESC;

The result is....

Store # % Plan Hours
217 107.85%
193 107.97%

107.97% should be before 107.85%..... If you can advise, I would most appreicate it.

Thanks!!!

 

[scriverb]

I don't see anything wrong with the code. Let's try taking the DESC sort designation off the field and see what happens. Right now your are sorting DESC and it looks ASC so let's just take off the DESC and take a look at the resulting data.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Golom]

I agree with scriverb ... code looks fine.

You could change to
[blue][tt]
ORDER BY 8 DESC
[/tt][/blue]
but that should not make a difference (grasping at straws here.)

The other anomoly is that the table name and field name are identical for that field. Try giving the table an alias in the FROM clause to see if that makes a difference (more grasping.)

 

[scriverb]

Hi Golom. This is weird one, yes.

HERE GOES WITH ANOTHER "Grasping at the proverbial straw...".

yleeSF: Is there any chance that your are looking at a report that uses your query and you aren't seeing the correct sort in the report? If so, check your Sorting and Grouping property and see if you are resorting ascending within the report.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[TonyJollans]

Hi yleeSF,

From the look of your Query, [% Plan Hours] is a numeric field (you check it is > 1.05), and your posted output shows it as a percentage. It seems likely that the Query is input into something else which produces your Output, so ..

I agree with Bob. If it's not a further sort of your output, can you tell us what does happen after the Query to produce the output.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[JonFer]

I did the following:

- put a sort on a field in a select query
- run the query
- highlight the sort column in datasheet view
- change the order (A->Z button)
- save the query then close it

Now when I open the query it uses the saved datasheet sort instead of the sort in the SQL statement. I couldn't see where this sort was saved - there is a row in the MSysQueries table with an Attribute value of 11 indicating the sort but I didn't see it change between creating the query and saving the new sort in datasheet view.

Try copying your SQL into a new query window and running it so it only uses that SQL and not any additional saved sorts in the query object.

 

[JonFer]

Follow-up: queries have an OrderBy property like tables that override the SQL sort order. You can see it in the query design view.

 

[scriverb]

JonFer, I think you have it here. Probably during the design of the query the data was resorted through the datasheet view to ASCENDING and saved. Now that sort of data is overriding the SQL ordering.

Waiting for yleeSF's reply.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[yleeSF]

Hello Everyone,

Thank you All for your continued advice. Here's my response....

TonyJollans:
Q: What happens after the Query to produce output?
A: The calculation is numeric. Used the Format Property to Format the result as a %.

Just to clarify not all of the output of the query is in Ascending Order. Again, it does sort the data in descending order but not to the 10th decimal place. Here's a larger subset of the results:

Store # % Plan Hours
25 119.29%
32 118.02%
3 118.61%
100 118.19%

Jonfer:

Q Part I: Executed this, but the result in Ascending order seems to have the same problem as the result in Descending order. (change the order A to Z button). The results do not sort correctly to the nearest 10th. Here’s a subset of the results:

Store # % Plan Hours
162 105.58%
42 105.03%
4 105.00%
237 106.1%
35 106.82%

Q Part II: Not familiar with SQL. I’m not sure how to see the MsysQueries Table with an Attribute 11……

Q Part III: Should I execute this, given that Part I’s result is not sorted to the 10th decimal place?

THANK YOU ONCE AGAIN FOR ALL YOU HELP!!! =)

 

[scriverb]

I have created a table with an autonumber and a number field(double-precision). I have entered values from 1.0615 thru 1.0931. Total of 21 values, 12 of which are 1.07XX. The following SQL is used and all values are sorted appropriately descending including the 10ths digit. The column Percent is formated using Percent.

SELECT tblRates.Percent
FROM tblRates
ORDER BY tblRates.Percent DESC;

Percent
109.31%
109.12%
108.99%
108.78%
108.23%
108.12%
107.89%
107.81%
107.77%
107.65%
107.54%
107.34%
107.31%
107.23%
107.20%
107.19%
107.18%
107.11%
106.34%
106.23%
106.15%

As you can see these records are all sorted properly in descending order. The values are stored in a double-precision numeric field. Values are entered as 1.07XX.

At this point we have to rely upon you to look over your tables, fields, queries, formating and tell us if there is something that is different.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Tranman]

While we're grasping at straws...

Is it possible that somewhere (on a form or report or whatever, this field is being referred to accidentally as %[% Plan Hours], or maybe it's called just Plan Hours on a form, but somewhere it is referred to as %Plan Hours? If it were, you'd be talking about the integer portion of the number instead of the whole thing(according to the old rules of typecasting), which *could* make it sort the integer part correctly(which appears to be happening), but the decimal part would be random. If I remember my QBasic correctly .

Graspingly,
Tranman

 

[JonFer]

Simplify the situation by createing a new query with just the [% Plan Hours] table, add the one field you want, and then add this field as well:

CDbl([% Plan Hours].[% Plan Hours])

Finally, add this Order By clause:

"Order by CDbl([% Plan Hours].[% Plan Hours])"

See what happens.

 

[yleeSF]

Thanks JonFer!

The CDBL function worked. My results are now sorting correctly to the 10th decimal place.

Thanks Again!!! =)

 

[JonFer]

The [% Plan Hours] field must have been text so I assume you used the Format() function when originally building that field. In the future, it is better to modify the display format (field property) instead of using the Format() function. Format() returns a text string while the format property leaves the field type and just changes the display.