Sorting integers across columns

[puffthemagicdragon]

I have a table comprising integers in separate columns and need to create a query which will arrange them in ascending order (e.g. from 5 | 2 | 1 | 4 | 3 to 1 | 2 | 3 | 4 | 5). I thought this might be possible with a nested iif statement, but it's looking horribly convoluted. Can anyone suggest a neater, less error-prone way please?

 

[boblarson]

It would appear you have design problem (non-normalized database). What could be the purpose of

1. Having fields like this.

2. Putting the data into the fields like this.

This sounds more like "spreadsheet" thinking rather than relational database thinking. Why don't you just use Excel to deal with this? Access is not designed to work this way.

 

[puffthemagicdragon]

Hi Bob

The numbers are split across fields because the relative position of the integers is normally critical to the outcome. However, this time I'm looking specifically for groups which contain the same integers, though in various orders. The best way to summarise this seemed to be to get them all in the same order.

Also, though I could probably get around it in Excel for now, long-term we will hit the row limitation.

 

[Access Hero]

That really didn't answer Bob's question. Storing data in a relational database like you would store it in Excel causes exactly this sort of problem. Access is not Excel.

You could look into using a union query and sorting the results. You would have a separate "select" statement in the union query for each column you want to sort on.

 

[boblarson]

Your "explanation" still doesn't explain fully why you have separate fields for these numbers. Positioning of the numbers (finding the 2nd position, etc.) is still possible in a single field. So, again, I ask WHAT does this mean? What are you using it for? What is the data? Explain your business and what the purpose of this is, please. What you seem to think is necessary may not be necessary, or good, at all but we cannot give you good guidance unless we understand what it is you are actually doing and what you need to accomplish. This does not mean stating your question again, it means taking out your preconcieved notion of how this has to be solved.

We see so many times people who post a question on how to do something but in reality they really need something entirely different. But they don't know that they need something entirely different when they posted the original question. I have serious questions as to the current process and what is required overall as we do not just give answers to the minor problems (doing band-aid fixes), we like to be able to give a very good, and best practices solution if possible.

 

[puffthemagicdragon]

I'm sure you are correct, the architecture could probably be a lot better.

The application's main purpose is to support our charity numbers game in which players pick a series of five numbers from 1 to 59. Some have more than one set of numbers, (hence a separate table). These numbers are then compared with a further set of six, selected weekly. Wins are achieved by matching the first three, first four, or all five numbers against any of the six (held in another table).

The practical problem which has emerged as the game has grown is that some folk have chosen the same numbers, albeit not necessarily in the same order. Though the probability of matching any five from six remains the same throughout, if the winning number sets are the same the jackpot prize payout (which is a fixed amount) is potentially huge.