Sorting Query

[Guest]

Is it possible to sort data within a record by numerical order.

Below is an example of some of the records and I want the numbers to be in
ascending order within each row?

Can anyone help?

Thanks

Field2 Field3 Field4 Field5 Field6
15 12 50 33 44 would show 12 15 33 44 50
12 19 33 34 8 would show 8 12 19 33 34
6 26 14 9 2 would show 2 6 9 14 26
19 45 43 8 16
31 42 37 36 15

 

[John Vinson]

Is it possible to sort data within a record by numerical order.

Below is an example of some of the records and I want the numbers to be in
ascending order within each row?

Can anyone help?

Thanks

Field2 Field3 Field4 Field5 Field6
15 12 50 33 44 would show 12 15 33 44 50
12 19 33 34 8 would show 8 12 19 33 34
6 26 14 9 2 would show 2 6 9 14 26
19 45 43 8 16
31 42 37 36 15

Not at all easily, and the need to do so very strongly suggests that
your table structure is incorrectly normalized.

What's the actual table? What type of data is being represented? Might
it not be better to have *two* tables in a one-to-many relationship
rather than storing what appears to be instances of "the same kind" of
data across fields in a record?

John W. Vinson[MVP]

 

[Guest]

The data is imported from another source, and shows lottery numbers in the
order they were drawn. I want to work out some statistics for a college
course and Ideally require them to be a numerical order. If it cant be
achieved in Access I shall just have to sort in excel first then import it
across.

Many thanks

 

[John Vinson]

The data is imported from another source, and shows lottery numbers in the
order they were drawn. I want to work out some statistics for a college
course and Ideally require them to be a numerical order. If it cant be
achieved in Access I shall just have to sort in excel first then import it
across.

You can use a "Normalizing Union" query to extract the five fields
across, into five records vertically. Let's say your table is named
Lotteries, has a primary key Field1, and the additional five fields.

Create a new query based on the table. You'll need to go to the SQL
window, you can't do it in the grid; edit it to

SELECT Field1, Field2 AS Drawn
UNION ALL
SELECT Field1, Field3 AS Drawn
UNION ALL
SELECT Field1, Field4 AS Drawn
UNION ALL
SELECT Field1, Field5 AS Drawn
UNION ALL
SELECT Field1, Field6 AS Drawn
ORDER BY Field1, Field2;

OTOH if you're just doing statistics on the drawn number - why is the
sort order relevant? The mean and SD will be the same, whatever the
order!


John W. Vinson[MVP]