Make Table Query Mis Sorts Numbers (1 Viewer)

[tvman5683]

Hello,
I have a simple make table query that occasionally mis sorts on a number column.
SELECT Usage, Sell, [Service Unit Number], TechID, Current], INTO Rank
FROM tblCombined_Final
ORDER BY Usage DESC , Sell;

The data type is double and I have the sort as descending in the ORDER BY clause. But sometimes the largest number does not come up as the first row. It's very random. Here's an example of the results of multiple runs of the query. 878 should always be in the number one position
Thanks for any advice
Usage and PositionUsagePosition111111111111111111111111151451878187818781878187818781878187818781878187818781

 

[The_Doc_Man]

What are the odds that this is an output formatting issue rather than a sort issue? From what you've presented, it would be hard to make sense of that output.

 

[tvman5683]

That's what i don't understand. The format in the original table is number(double) and is the same in the created table. Yet it the created table still is mis sorted randomly. Sometimes on the very first query run, or other times I can make multiple runs without error.

 

[georgedwilkinson]

How do you know that the inserts are not happening in the right order, since the order data comes out of any given table will be random?

Yes, your "order by" clause does have the right thing, seemingly. That doesn't mean that Jet will store anything in a certain order though. By extension, anything that comes out of the table you are inserting data into MAY come out of the table in a random order (unless you supply an order by clause).

Agree with Doc: I can't tell what all the stuff at the bottom of your post means.

 

[MSAccessRookie]

Hello,
I have a simple make table query that occasionally mis sorts on a number column.
SELECT Usage, Sell, [Service Unit Number], TechID, Current], INTO Rank
FROM tblCombined_Final
ORDER BY Usage DESC , Sell;

The data type is double and I have the sort as descending in the ORDER BY clause. But sometimes the largest number does not come up as the first row. It's very random. Here's an example of the results of multiple runs of the query. 878 should always be in the number one position
Thanks for any advice
Usage and PositionUsagePosition111111111111111111111111151451878187818781878187818781878187818781878187818781

Your output is not formatted in a report style, so it is very difficult to guess what items are related to each other. Assuming there are two items per row (Usage and Position), the information could look like this

[COLOR=black][FONT=Arial]Usage               Position[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]1                      1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]15                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]45                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR][COLOR=black][FONT=Verdana][/FONT][/COLOR]

Or maybe like this

[COLOR=black][FONT=Arial][COLOR=black][FONT=Arial]Usage               Position[/FONT][/COLOR]
11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]11                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]15                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]45                     1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR]
[COLOR=black][FONT=Arial]878                   1[/FONT][/COLOR][COLOR=black][FONT=Verdana][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]

Or perhaps some other list I have not come up with.

Can you generate a report and post it here? removing the "Into Rank" from your original query should do just fine.

SELECT Usage, Sell, [Service Unit Number], TechID, Current
FROM tblCombined_Final
ORDER BY Usage DESC , Sell;

 

[georgedwilkinson]

It all comes back down to:
Data comes out of a table in a potentially random order.

The resolution is to always use an order by clause when you take data out of the table.

 

[tvman5683]

Thanks, the line at the bottom was a table that didn't copy in. I created a module to run my query in a loop and capture the Usage number and Position number after each run. Here's what I would see in the result table
878 1
878 1
157 1
22 1
The source table was the same and never changed but each time the make table query ran it put a different part at the top of the list. Then when I inserted my potion column with the auto number I got a different number one part when the part with the 878 should always come up first. I even tried eliminating all the columns except the three that I really need and it still failed randomly.

 

[tvman5683]

MS, your second example is exactly correct.

 

[georgedwilkinson]

The source table was the same and never changed but each time the make table query ran it put a different part at the top of the list.

No it didn't. See posts #4 and 6.

Then when I inserted my potion column with the auto number I got a different number one part when the part with the 878 should always come up first. I even tried eliminating all the columns except the three that I really need and it still failed randomly.

That is because data is not guaranteed to come out of a table in any particular order. See posts #4 and 6.

 

[tvman5683]

Then I confused? what's the point of the order by clause?
Shouldn't my new table be created with the highest Usage part always first?

 

[gemma-the-husky]

but there is more going on, isnt there - your query is not just a read from this table, but something rather more complex, as there are other fields involved
- what does your output query REALLY look like

eg, you mention its a maketable query

now, inserting into a new table may well not respect the order you started with - access probably uses some tree balancing algorithm to insert columns - remember everything is a set of homogeneous data, as far as access is concerned, so the order doesnt matter

[similarly, if you insert a csv file into a table, you would expect that the displayed rows are in the same order as the csv - but they often arent, - and it doesnt matter!!, thats the thing]

when you retrieve the data, use a sorted query, and you shouldnt have any probelms.

 

[georgedwilkinson]

Then I confused? what's the point of the order by clause?
Shouldn't my new table be created with the highest Usage part always first?

Yes, your table has the rows inserted in the order you supplied in the order by clause. However, there is absolutely no guarantee that rows will be physically located in any particular order, nor is there any guarantee that a query will pull them out in any particular order.

They are ordered by what is convenient to the SQL engine's insert and select routines, which includes which block of the table is currently in memory, where are the available free spaces in those blocks to write the data of this particular row, etc.

Here is the bottom line: there is absolutely no guarantee that data will be taken out of any table in the order the data was put into that table.

Posters, please read tvman's entire post before responding. He is inserting data into a table and expects the data to come out of that table in the order he put it in:

SELECT Usage, Sell, [Service Unit Number], TechID, Current], INTO Rank

he expects to get the data out of table Rank in the order he got it out of table tblCombined_Final.

 

[tvman5683]

Here's a sheet with two results from the same query on the same table

 

[tvman5683]

Thanks Again GW for replying. I understand what your saying, but I use other tables and pull data out with sort criteria and it always come out with the highest values listed first. Those tables are being created directly in coded modules. I just don't understand why the query doesn't do the same.

 

[boblarson]

Here's a sheet with two results from the same query on the same table

TVMAN:

The data in tables in Access are not stored in any particular order (regardless of how you put it in). When you open a table you are, in fact, actually opening basically a query of the data. When wanting data in a particular order you should use a query to bring back data and use it with an order by clause. The order by property in a table is not reliable enough to give you what you want. So, you should use queries to get what you want.

 

[boblarson]

Thanks Again GW for replying. I understand what your saying, but I use other tables and pull data out with sort criteria and it always come out with the highest values listed first. Those tables are being created directly in coded modules. I just don't understand why the query doesn't do the same.

I think you have just been "lucky" that it has done this. It is something that confuses new users (and even somewhat experienced ones) quite frequently because if you get something frequent enough it is easy to assume that it is supposed to do that. In this case it isn't. It CAN happen that way but it is not guaranteed to do so.

You need a query with an ORDER BY [FIELD] DESC to get the highest values first.

 

[georgedwilkinson]

but I use other tables and pull data out with sort criteria and it always come out with the highest values listed first.

That is totally coincidental.

 

[tvman5683]

I created a Test table1 with Parts Numbers A thru K. I assinged a use value of 10 to 0 one for each part number. I then created a make table2 query with the same criteria of DESC on part usage. I inserted my position column. I ran this sequence thru the looping module.
First run 1600 no errors
Second run 3700 no errors. This is telling me there's a problem with my Usage numbers in the real tables. They are imported from a text file but set as number(double). I'll have to try to reformat them somehow.
Thanks to all for the great feedback

 

[georgedwilkinson]

That is totally coincidental. It is all about how Jet finds a place to store a record, not about what order the data is inserted in.

 

[Pat Hartman]

tvman, you are simply not listening to the answer. Tables are UNORDERED sets!!! and that is that. Just because it looks like Jet "always" returns records in pk order doesn't make it so. Two things lead people to that assumption.

1. When a database is compacted, Access reorders all tables into primary key sequence. This has the effect of a clustered index (which Jet does not specifically support).
2. They are using such a small set of data that it hasn't exceeded 2k which means that it is retrieved all at once and in whatever order it was stored in or resequenced to if the db has been compacted.