Make Table Sorting Order

[mattm31]

I'm doing a make table query, where i enter about 30 contract numbers as the criteria, but i need the contract numbers to stay in the same order i enter them when they move into the table. I have nothing sorted in the query but
it just seems to sort on the first column whatever i do.

Any suggestions >?

 

[fredericofonseca]

You don't normally have to have the data on your tables ordered, but if you wish to have that on a select you use a "order by" as part of the "select".

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mattm31]

I have no sorting order on the query or the table but it is sorting which i do not want. I enter the criteria as follows,
'where contract_no = "BL25544664" or "FY234245" or "BB236234"'

And thats the order i want them to stay in.

 

[fredericofonseca]

If the table where you are selecting from has a primary key then that key order will be used. Note sure if the same won't happen without PK but with a index that matches the fields being selected.

Apart from the above, if you NEED the records on a particular order on the table, and that order is not the natural order of those values then your only option is to issue an individual select for each value on your desired order.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BNPMike]

The word relational comes from the word relation. A relation is unordered.That was one of the key principles of the relational model. The fact that Access sometimes orders tables in something you should ignore. It's not necessary for SQL and it's unpredictable.

Always deal with your data via queries. As it stands no query can deliver the order you want so you'll have to add another - order - field. Then
1 => BL25544664
2 => FY234245
3 => BB236234

Now you can order on the order field and Access can never disturb that.

 

[PHV]

WHERE contract_no In ('BL25544664','FY234245','BB236234')
ORDER BY IIf(contract_no='BL25544664', 1,
IIf(contract_no='FY234245', 2,
IIf(contract_no='BB236234', 3,9)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

PHV And you're going to hard code that every time?

mattm31 SQL does not offer ordering derived from OR clauses. You'd be better creating a new temporary table and joining from it. Most likely the results will come out in the order of the temporary table, but as I said, that is not the 'correct' way to approach this problem.

 

[LOKIDOG]

You've got to be kidding. Relation has nothing to do with ordered - it means data relating to other data based on features of the data - it's completely independent of this issue. Access re-ordering data is extremely frustrating and is in fact a flaw.

When entering data the user should be able to see the data that was just entered. With Access reordering data without regard to anything logical, or without control of this action is simply very bad programing.

I've had this problem over and over, and the solution to put in date and time info, or numbering the data, is simply a Fix, and will not work in many instances easily (When trannfering data or appending data to another table for instance). Why should someone have to put in more fields that are not needed for anything else but keeping the data in the order it was entered. Silly!

 

[BNPMike]

Sorry LOKIDOG. Like many people you assume relational must mean something to do with relationships. Not so. It's best to learn things and not jump to conclusions in life.

Look up the word relation and you'll find it is a special kind of table.

The reason it is unordered underlies the whole relational model. Buy a book. You'll find it useful.

 

[r937]

And you're going to hard code that every time?

no, that's what a front-end script is for


where contract_no
in ('#param1#'
,'#param2#'
,'#param3#')
order by
iif(contract_no='#param1#',0,1)
, iif(contract_no='#param2#',0,1)
, iif(contract_no='#param3#',0,1)

see? it is possible -- and practical, too

but a separate column for sequence is nevertheless a good idea



r937.com | rudy.ca

 

[MajP]

This seems like everyone is making it awfully hard for a very simple issue. What is wrong with using an Autonumber in you table? If you are really concerned about the order in which it goes in then you will always have a field to sort on. Still not sure why you would care about the order in a table, as long as there is a field to sort on. Personally, I almost always have an autonumber field as a primary key. If your autonumber is not you primary key then there are still easy solutions for appending and transferring data.