Sorting lotto numbers

[clickaccess]

Hi,

I want to sort the past winning number for a local lotto.

Sample incoming:
F1 F2 F3 F4 F5
31 15 1 2 20
35 39 8 26 19
7 28 27 36 12
25 28 29 9 4
16 4 2 19 35
32 23 39 19 31
26 31 33 12 23
30 9 26 22 21
35 4 13 1 32


Sorted sample:
F1 F2 F3 F4 F5
1 2 15 20 31
1 4 13 32 35
2 4 16 19 35
4 9 25 28 29
7 12 27 28 36
8 19 26 35 39
9 21 22 26 30
12 23 26 31 33
19 23 31 32 33


I have tried the filer/sort function, but that fails in the second column.

Thanks you
Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[fneily]

Why wouldn't you type in the winning lotto number IN ORDER??? Maybe someone will give you a little program to sort the numbers for a SINGLE record which you can then feed all the other winning numbers to get them in numeric order. But that's a waste of time.
Also, why don't you use Excel? Format single digits with a leading 0, concatenate the fields, then sort. But that goes back to my first question.

 

[fneily]

By the way, you can use Microsoft Excel’s Sort feature, but the tedium might kill you before you reach the last of those rows. Here is what you have to do. Highlight the numbers in the first row and choose Sort from the Data menu. Excel asks whether you want to expand the selection. You don’t want to do that, as it would sort the entire block of data based on the selected row. Instead, select Continue with the current selection and click on the Sort button. Now, in the Sort dialog, you must click on the Options button, select Sort left to right, click on OK, and click OK. Repeat for each row.
So you can copy and paste your Access info into Excel and do the above, or I did find a nice bubble sort written in C++ you could try to incorporate.
Maybe someone has a VBA coded sort.

 

[gmmastros]

Never underestimate the power of a database! If you're willing to be creative, you can get it to do a lot for you. Like in this case. I created a new Access Database. In the database, I created a table named Lotto. This table has 6 columns, LottoId (AutoNumber), F1 -> F5 as number (long integer).

I then inserted the sample data, and I was successful in getting the desired output, all from within the database.

[!]Note:[/!] This method only works if you have a primary key defined.

Here's how I did it.

First, I created a query and saved it. I called the query, GetNumbers

Select LottoId, F1 As NumberDrawn
     From Lotto

     Union All 

     Select LottoId, F2
     From Lotto

     Union All 

     Select LottoId, F3
     From Lotto

     Union All 

     Select LottoId, F4
     From Lotto

     Union All 

     Select LottoId, F5
     From Lotto
ORDER BY LottoId, NumberDrawn;

If you [tt][blue]Select * from GetNumbers[/blue][/tt] you'll notice that the data in the columns is put in to 1 column (important for the self join in the next query).

I then created this query (which uses the previous one).

SELECT A.LottoId, 
       A.NumberDrawn As F1, 
       B.NumberDrawn As F2, 
       C.NumberDrawn As F3, 
       D.NumberDrawn As F4, 
       E.NumberDrawn As F5
FROM   (((GetNumbers As A
       Inner Join GetNumbers As B 
           On  A.LottoId = B.LottoId
           And A.NumberDrawn < B.NumberDrawn)
       Inner Join GetNumbers As C 
           On  C.LottoId = B.LottoId
           And B.NumberDrawn < C.NumberDrawn)
       Inner Join GetNumbers As D 
           On  D.LottoId = C.LottoId 
           And C.NumberDrawn < D.NumberDrawn)
       Inner Join GetNumbers As E 
           On  E.LottoId = D.LottoId 
           And D.NumberDrawn < E.NumberDrawn
Order By A.NumberDrawn, 
         B.NumberDrawn, 
         C.NumberDrawn, 
         D.NumberDrawn, 
         E.NumberDrawn

It joins to itself multiple times so that it can order the data the way you want it.

Of course, if your table names and/or field names are different, you'll have to modify this code.

Hope it helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

Another way (with an UDF).
In a standard code module create the following function:

'A generic function to get the Xth element of an arbirtrary numbers of same type values:
Public Function myXthMin(x As Integer, ParamArray Args())
If x <= 0 Or x > UBound(Args) + 1 Then Exit Function
Dim i As Long, j As Long, tmp
For i = 0 To UBound(Args) - 1
  For j = i + 1 To UBound(Args)
    If Args(i) > Args(j) Then
      tmp = Args(j): Args(j) = Args(i): Args(i) = tmp
    End If
  Next
Next
myXthMin = Args(x - 1)
End Function

Now your query:
SELECT myXthMin(1, [F1], [F2], [F3], [F4], [F5]) AS F1
, myXthMin(2, [F1], [F2], [F3], [F4], [F5]) AS F2
, myXthMin(3, [F1], [F2], [F3], [F4], [F5]) AS F3
, myXthMin(4, [F1], [F2], [F3], [F4], [F5]) AS F4
, myXthMin(5, [F1], [F2], [F3], [F4], [F5]) AS F5
FROM yourTable

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

 

[gmmastros]

PHV,

Obviously, you are more skilled at writing Access queries than I am.

Since I already created a database to test this on, I thought I would play around with your solution so that I could learn from it. I changed the table name to Lotto (because that's the table I had in my database). When I add your function to a code module, and then ran the query, I got an error.

Circular reference caused by alias 'F1' in query definition's SELECT list.

To correct this error, I changed As F1 To As N1. (I also changed the other column aliases.) After doing this, the query ran as expected. Here's what it eventually looked like.

[tt][blue]SELECT myXthMin(1, [F1], [F2], [F3], [F4], [F5]) AS N1
, myXthMin(2, [F1], [F2], [F3], [F4], [F5]) AS N2
, myXthMin(3, [F1], [F2], [F3], [F4], [F5]) AS N3
, myXthMin(4, [F1], [F2], [F3], [F4], [F5]) AS N4
, myXthMin(5, [F1], [F2], [F3], [F4], [F5]) AS N5
FROM Lotto [/blue][/tt]

I quess I don't understand WHY that would cause a circular reference. Your thoughts?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

My bad: I created alias with SAME name as field ...

 

[gmmastros]

PHV,

My intention was not to point out a mistake.

This query runs fine...

[tt][blue]SELECT Lotto.F1 as F1,
Lotto.F2 as F2,
Lotto.F3 As F3,
Lotto.F4 As F4,
Lotto.F5 As F5
FROM Lotto;[/blue][/tt]

In this case, the alias is the same as the field name, and there is no problem. Why would using a field in a UDF cause a circular reference when aliasing it the same as a field name? I don't understand why it would be different. Is this just a quirk with Access?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

Perhaps this ?
SELECT myXthMin(1, Lotto.F1, Lotto.F2, Lotto.F3, Lotto.F4, Lotto.F5) AS F1
...

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

 

[fneily]

Both PHV and GMMASTROS are excellent examples of some advanced course work. But, I ask again, why wouldn't you just type in a winning number in order? When I ask the Maryland State Lottery Commission for the winning numbers for the past 10 years, they don't send them as they were picked, but rather in ascending order.

 

[clickaccess]

Fneily,

I need it to create a sorted list (CA lottery does not provide a sorted list on their website) and carry a printed version so that I do not play the numbers that have already been picked. Why spend a doller on a number that is already a winner. I under stant that this is random, but still you do what you can do to save that dollar.

And everybody else thaks a lot.

Click

Email Service Free from Banner ads.

http://www.SacMail.com