Can you verify sorting order? (1 Viewer)

[tvman5683]

In my table I have one column that lists part usage (number) in descending order. I have a second column with an auto number for ranking. So my highest used part ranks as number one.
Is there any way to verify the sort order on my usage column is descending as my rank number increases? The table is used as a reference for other functions. I'm just trying to verify the table was created correctly. With 2000 technicians and 600,00 parts in it it's immpossible to verify manually.

Thanks
JB

 

[Pat Hartman]

There is no way to control the physical sequence of a table, nor is there any way to control the order in which rows are returned for viewing EXCEPT by using a query with an ORDER BY clause. By definition, tables are unordered sets. Therefore, if the order of a recordset is important to you, you MUST use a query with an order by clause rather than the table itself.

Jet fools people into thinking that table rows are maintained in a particular order because when a database is compacted, all tables are resequenced into key order and will maintain that order until updated. Once rows are updated, added, or deleted, the order is no longer maintained.

 

[tvman5683]

Thanks for the reply
I understand I can't change the table. I want to query it for let's say the top 50 parts for each technician. But I need to know each parts exact rank that was assigned by the autonumber field when I built the table.
In the past I ran into mis sorts where the highest used part did not have the number 1 rank assigned to it. I guess I'm just trying to verify my Make Table query correctly created the table. Here's a sample of the table. But with over 600,000 records I can't be sure each Techs Top parts were sorted correctly.

JB

Tech Top 200 AssortmentsService Unit NumberTech IDCurrent Full Part NumberPart DescriptionPDC Part Sales QuantitySellPositionVerify0007323000010926110285753ACOUPLER1268$15.001000073230000109261103392519FUSE-THRML628$13.002000073230000109261103363394PUMP615$48.003000073230000109261103360629GEARCASE486$163.004000073230000109261103949238SWITCH455$31.00500007323000010926110285805VALVE-INLT427$24.0060000732300001092611080040DOG-AGIT-4PK376$8.00700007323000010926110285785CLUTCH363$38.00800007323000010926110341241BELT-DRUM313$18.009000073230000109261108536974SUPPORT- RED298$12.00100

 

[tvman5683]

Sorry the table did not copy correctly.

 

[tvman5683]

Here is a sample table with three techs Top parts listed

 

[dkinley]

I am sorry for jumping in Pat, just tossing my .02 in that I almost responded earlier.

On an assembly line for manufactured parts - each one can't be gaurenteed 100% defect free. If they could, the company would lose money because nothing would leave the factory because they would all be tested. All they do is sample some inputs and sample some of the outputs and come up with a statistic that has a confidence value attached to it.

Because your records are so many and you state that you cannot manually guarentee - then your best bet is to sample and claim some sort of confidence.

The great thing is that the confidence interval can be increased on an increased sample rate or a more complex rating of the sample. This is the concept of SixSigma, to reduce defects to the sixth variance.

This link: http://www.itl.nist.gov/div898/handbook/pmc/section2/pmc22.htm

discusses sampling to give you an idea and perhaps a path to get started on. If you are great at statistics then this should provide you a good start, if not - ask around the office, someone had to have to sit through a class at one time or another and can possibly lend you a hand.

I used the manufacturing line as an analogy, but there are formulas for data as well - most are the same depending on how you sample.

I know it is not much help and probably opens a whole new can of worms for you - but sampling is the only way to go in large data sets to look for a type 1 and type 2 errors in the algorithm you have constructed.

-dK

 

[tvman5683]

Thanks for the reply
I just find it hard to understand if I sort records by desc or asc order and attach a position number to that result, it could be wrong. Is there a margin of error in Access?
I found a site that was talking about a Recursive quick sort query.
I think it's something that might help me loop thru my records for each tech and check that no usage value is greater than the one with the preceding rank value.
I'll let you know if I can adapt it to my database.

JB

 

[The_Doc_Man]

You are also asking another question that requires a warning.

You seem to be saying, "Can I trust my AUTONUMBER field to always number in a specific order?" The answer is, "Not really." When you use AUTONUMBER, what you are doing is telling Access that you need to have a field that is unique so you can use it for a prime key as the one side of a one-many relationship. The FIRST priority for Access is to assure uniqueness. Specific ordering of the fields is a secondary concern.

Access will normally assign AUTONUMBER numbers monotonically. Gaps might occur if you create a record in a way that forces the AUTONUMBER to be allocated, but then you back out and cancel the update for that record. If the number was allocated and THEN you backed out, that number is a gone pecan. So you cannot trust AUTONUMBER fields to have the property that mathematicians call "montonicity."

If you want a specific order of values based on some property of a given table, write a query that does the sort based on said property. Sorting on an AUTONUMBER field is not guaranteed to produce the desired result every time.

 

[dkinley]

Set your rank order field of your query before the part number field and sort ascending on both. As Pat posted, this has to be done in a query and both sorts have to engaged. The priority of the sort is the order of the fields in the query (left-to-right from colum to column).

Run the query and sample a few. If you wanted an exact measurement, then you can apply sampling theory as I posted. If not, you can just randomly check a few and think to self 'oh that is working' or 'no, that is not working'.

I have no knowledge of confidence measurement in Access's sorting algorithm, but using the method described in the first paragraph, I've never had a problem. Be warned though, don't take my word on it, I knew how my data was and knew the expectation result - yours is probably set up different than mine and might produce different results.

-dK

 

[Pat Hartman]

Now you've got me confused. Are the autonumbers assigned as each row is added or are they assigned after the fact based on some perception of how the data is physically stored. Please outline your exact procedure for creating the autonumber (which you are calling rank) and the usage number.

 

[tvman5683]

Pat, I think I found a fix. As my Tech Part use table is created for each tech, the auto number field is in the create table statement. So then when my usge query goes out to cature the top used parts for that tech (DESC) the auto number will rank the parts.
I found a query that will analyze the previous part use to the next.
I'm subtracting row 1 use from row 2, row 2 from row 3 etc. I now have a field that displays the difference. any negative numbers in this field would indidcate a bad sort occured when the data was appended in the main table.
This is what I was after. So as each tech is processed thru the code, the additional query will help verify the sort order on the usage is DESC.


Thanks
for your help

 

[datAdrenaline]

In short, you are mis-using the Autonumber datatype ...

Also ... if you want to show the records in a particular sort order via the datasheet view of the table, you can set the OrderBy property of the table definition in Design View... but that setting is easily altered by sorting your table while in datasheet view then clicking the SAVE button, or responding to Access when it askes if you want to change the design of the table upon closing of the data sheet.

As a side note, the only sorting issues I know of are on "Decimal" datatyped fields ... but I beleive that has been corrected with the implementation of A2007/ACE

 

[The_Doc_Man]

To "really" rank-order correctly, what you want to know is the

1 + the number of elements ahead of this one in whatever property is the sort order.

This is equivalent to 1 + DCount( "[fieldx]", "tablex", "[fieldx] > """ & [fieldx] & """ )

Note: Depending on exactly WHAT you are using for fieldx, that might be "<" rather than ">", and the syntax might need adjusting if fieldx is numeric rather than text. Look up the examples for DCount and also search for DCount in this forum to see many examples of same. THIS is how you rank-order, and it correctly handles cases where you have ties, unless you have aberrant treatment for rank-ordering of ties.