By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Looking for a query

P: n/a
I have a table (Table name : Lotto) with 23 fields (D_No, DrawDate, P1,
P2,.....P21) and it has draw results from 1st Sep 2004 till date. I
have another table (Table name : Check) with 15 fields (F1,F2,....F10,
R6, R7, R8, R9, R10). I have few lacs combinations of 10 numbers in the
Check table in the first 10 fields F1-F10).

I want to check how many numbers from the first combination (record1 of
Check table, fields F1-F10) matched in jan1 (record1 of Lotto table).
If six numbers matches, update R6 field of record 1 of Check table to
"1", if 10 numbers match, update R10 to "1". Don't update anything if 5
or less numbers match. Then check the same first combination of Check
table in Jan2 (record 2 of Lotto table) and increment R6-R10 fields by
one. Continue this till date and then move on to the next combination
(record2 of Check table).

John Nurick [Microsoft Access MVP] from Microsoft newsgroups has
suggested me to normalise my data into three or more tables and ask for
an answer in the microsoft.public.access.queries) newsgroup. But I am
really confused on how to do it as I don't know anything about
normalisation. I am not able to understand the structure that he wants
me to have. Can any one explain to me what changes do I have to make to
my current database, how to use relationships and what queries should I
use to get the desired result?

Following is his suggestion.

tblDraws (one record per draw)

D_No (primary key)
DrawDate

tblNumbersDrawn (21 records per draw)

D_No (foreign key)
DrawnNumber
(primary key includes both these fields)

tblCombinations (10 records per combination)

C_No (foreign key)
PickedNumber
(primary key includes both these fields)

Any help will be appreciated.

Maxi

Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Maxi wrote:
I have a table (Table name : Lotto) with 23 fields (D_No, DrawDate, P1,
P2,.....P21) and it has draw results from 1st Sep 2004 till date. I
have another table (Table name : Check) with 15 fields (F1,F2,....F10,
R6, R7, R8, R9, R10). I have few lacs combinations of 10 numbers in the
Check table in the first 10 fields F1-F10).

I want to check how many numbers from the first combination (record1 of
Check table, fields F1-F10) matched in jan1 (record1 of Lotto table).
If six numbers matches, update R6 field of record 1 of Check table to
"1", if 10 numbers match, update R10 to "1". Don't update anything if 5
or less numbers match. Then check the same first combination of Check
table in Jan2 (record 2 of Lotto table) and increment R6-R10 fields by
one. Continue this till date and then move on to the next combination
(record2 of Check table).

John Nurick [Microsoft Access MVP] from Microsoft newsgroups has
suggested me to normalise my data into three or more tables and ask for
an answer in the microsoft.public.access.queries) newsgroup. But I am
really confused on how to do it as I don't know anything about
normalisation. I am not able to understand the structure that he wants
me to have. Can any one explain to me what changes do I have to make to
my current database, how to use relationships and what queries should I
use to get the desired result?

Following is his suggestion.

tblDraws (one record per draw)

D_No (primary key)
DrawDate

tblNumbersDrawn (21 records per draw)

D_No (foreign key)
DrawnNumber
(primary key includes both these fields)

tblCombinations (10 records per combination)

C_No (foreign key)
PickedNumber
(primary key includes both these fields)

Any help will be appreciated.

Maxi


I updated the code in your first request in another post of yours. It
should work with your current structure.

However...John's idea is good. If this is simply a new database, you
may want to scrap your current setup and start anew.

Create 3 tables. Just as he stated. Now create a form for table Draws,
Drawn, and Combinations. Make Drawn and Combinations subforms in the
main form Draws. Drawn and Combinations could be Datasheets or
Continuous forms. Link C_NO in Combinations to D_No in D_No in the
mainform and D_NO in Drawn to D_No in the main form. Set some code to
not exceed 10 records in Combinations and no more than 21 in Drawn.
Voila, you have a list that you can use.

Getting the counts is another thing. With some queries you should be
able to line them up.

Nov 13 '05 #2

P: n/a
Why do I have to make forms? I will start making a new database however
I want to check if this is the correct setup. Please reply.

First Table : tblDraws (one record per draw)
Fields:D_No (primary key) and DrawDate
Sample data:
D_No DrawDate
1 9/1/2004
2 9/2/2004

Second Table : tblNumbersDrawn (21 records per draw - I DID NOT
understand this)
Fields: D_No (foreign key), DrawnNumber (primary key includes both
these fields - I DID NOT understand this)
Sample data:
D_No DrawnNumber
1 5
1 10
1 15
1 16
1 18
1 24
1 36
1 37
1 38
1 44
1 46
1 50
1 52
1 55
1 56
1 58
1 62
1 63
1 69
1 72
1 76

I DID NOT understand how to setup the third table tblCombinations

Can you help me understand what is normalisation and how to normalise
my database to get the desired results and Yes I also want the queries
to get the resired result

Maxi

Nov 13 '05 #3

P: n/a
Maxi wrote:
Why do I have to make forms?
You certainly don't need to make forms. The reason people use forms is
that they are structured and can have rules and validation. For data
entry, it makes things easier. But if you want to enter the data in a
table, you can certainly bypass the structure and validation rules you
create.

I will start making a new database however I want to check if this is the correct setup. Please reply.

First Table :tblDraws (one record per draw)
Fields:D_No (primary key) and DrawDate
Sample data:
D_No DrawDate
1 9/1/2004
2 9/2/2004
Very good.

Second Table : tblNumbersDrawn (21 records per draw - I DID NOT
understand this)
Fields: D_No (foreign key), DrawnNumber (primary key includes both
these fields - I DID NOT understand this)
Sample data:
D_No DrawnNumber
1 5
1 10
1 15
1 16
1 18
1 24
1 36
1 37
1 38
1 44
1 46
1 50
1 52
1 55
1 56
1 58
1 62
1 63
1 69
1 72
1 76
Excellent

I DID NOT understand how to setup the third table tblCombinations
Make of copy of tblNumbersDrawn and call it tblCombinations (click on
tblNumbersDrawn, right click, and press Copy, and enter the name
tblCombinations, structure only) and open it up. If you want, you can
change the field name DrawnNumber to CheckNumber or whatever.

Side note: some programmers like to put "tbl" in front of table names
and "qry" in front of query names. Frankly, if I view it as extra
typing. Oftentimes I create Alpha grouping of my tables. Ex:
Lotto
LottoDrawn
LottoNumbers
When you open the database window, all three of your tables are grouped
together without 3 chars in front of it. But that's my preference, I
see no benefit of adding more characters to a table name.
Can you help me understand what is normalisation
http://en.wikipedia.org/wiki/Database_normalization

and how to normalise my database to get the desired results and Yes I also want the queries
to get the resired result


Now you can create a query. Query/new and add tblNumbersDrawn. Drag
the field D_No to the query. Drag NumbersDrawn to the query column
twice. Now, click View/Totals from the menu. D_No and the first
NumbersDrawn should be GroupBy. The second NumbersDrawn would be Count.

Save this query. I'll pretend it is named Quer1.

Now create another query. Add the tables tblDraws, tblCombinations, and
the query Query1.

Create your relationships between the tables Drag a relationship line
from tblDraws to tblCombinations (drag the field D_No in tblDraws to
C_No in tblCombinations). If D_No is 1, all records with 1 in C_No are
selected.

Now drag from tblCombinations C_No to D_No in Query1. Drag another
relationship line between CheckNumber in tblCombinations and D_No in
Query1.

Now drag the fields down to finish the query. In the field row, drag
D_No and DrawDate from tblDraws. Drag CheckNumber from tblCombinations.
Drag the count value field from Query1.

Now run the query. You should get all of the numbers from
tblCominations that were drawn in tblNumbersDrawn.

That will get you the basic results.

You now need to create another query that will sum the totals from this
query and also determine if there were repeats of a number.

Once that is done you have the fun part of formatting the data for a
report. You might want to check out CrossTabs/Pivot queries in on-line
help if you want to format the data to 1 row per day.

Put it this way, your request is complicated.
Nov 13 '05 #4

P: n/a
This will check only one number as to how many times it was repeated
and on what date. I want to check a combination of 10 numbers. I have
uploaded my 164kb .mdb file on my website (link given below). Can you
have a look at it and let me know your suggestions?

www25.brinkster.com/shreejipc/maxi.mdb

If you open the file, I want to search all 10 numbers 7 22 23 33 45 41
48 55 78 87 (record1 of check table) in all 21 numbers given in 1st Jan
(record1 of Lotto table). Update the field "Six" to 1 in Check table if
6 numbers match, update field "Seven" to 1 if 7 numbers match and so
on.

Then make a same check in 2nd Jan and so on and go on incrementing the
values in field Six Seven Eight Nine and Ten with 1 as and when they
match.

Then check the second record of check tacble (That is next ten numbers)
and so on till the last combination of 10 numbers (last record of check
table)

I hope now I am clear on what exactly I want to do.

If you want to change the database structure, please change it and send
it to me @ ma********@hotmail.com

Maxi

Nov 13 '05 #5

P: n/a
my email address is mac<underscore>mahesh<at>hotmail<dot>com
If you are not able to download my file, just remove the hyphen (-)
mark from the url.

Maxi

Nov 13 '05 #6

P: n/a
If you copy and paste the website address to download my database file,
you will notice that it will not work because by default it puts an
hyphen (-) before "maxi" in the url. Just remove that hyphen mark and
it will work

Nov 13 '05 #7

P: n/a
Maxi wrote:
This will check only one number as to how many times it was repeated
and on what date. I want to check a combination of 10 numbers. I have
uploaded my 164kb .mdb file on my website (link given below). Can you
have a look at it and let me know your suggestions?

www25.brinkster.com/shreejipc/maxi.mdb

If you open the file, I want to search all 10 numbers 7 22 23 33 45 41
48 55 78 87 (record1 of check table) in all 21 numbers given in 1st Jan
(record1 of Lotto table). Update the field "Six" to 1 in Check table if
6 numbers match, update field "Seven" to 1 if 7 numbers match and so
on.

Then make a same check in 2nd Jan and so on and go on incrementing the
values in field Six Seven Eight Nine and Ten with 1 as and when they
match.

Then check the second record of check tacble (That is next ten numbers)
and so on till the last combination of 10 numbers (last record of check
table)

I hope now I am clear on what exactly I want to do.

If you want to change the database structure, please change it and send
it to me @ ma********@hotmail.com

Maxi


Hi Maxi, unfortunately I don't have your version of Access on my
computer. You could stick the following code into a code module and run
it...if you do you can change the "C:\" to point to an empty folder. If
basically will send your tables to a text file format, and it will list
all of your queries to a text file. I don't think you really have any
code/forms/reports of any interest to your problem. Then zip the files
up and I'll see if I can make heads/tails of this situation.

Sub TableFile()
Dim tdf As TableDef
Dim s As String
Dim i As Integer
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , tdf.Name, "C:\" &
tdf.Name & ".txt", True
End If
Next
MsgBox "done"
End Sub
Public Sub QueryFile()
Dim qdf As QueryDef
Dim s As Variant
Open "C:\Query.txt" For Output As #1
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
Print #1, qdf.Name
s = qdf.SQL
Print #1, s
Print #1,
Print #1,
End If
Next qdf
Close #1
MsgBox "Done"
End Sub
Nov 13 '05 #8

P: n/a
Copying my tables in text file format here in the post. I don't have
any Query/code/forms/reports in my database. I don't want any forms or
reports. The only thing I want is Query or VBA code that would
accomplish my task.

Also uploading the zip file on my website which can be downloaded from:
http://www25.brinkster.com/shreejipc/MaxiLotto.zip

Lotto table:
"D_No","Date","P1","P2","P3","P4","P5","P6","P7"," P8","P9","P10","P11","P12","P13","P14","P15","P16" ,"P17","P18","P19","P20","P21"
1,1/1/2005
0:00:00,4,5,7,10,16,18,22,25,26,29,36,38,41,48,55, 49,52,57,59,64,78
2,1/2/2005
0:00:00,2,3,4,8,16,17,18,20,28,29,32,34,37,38,42,4 8,52,55,59,72,90
3,1/3/2005
0:00:00,1,2,9,18,22,35,42,44,47,49,50,51,54,56,60, 61,69,72,73,86,92
4,1/4/2005
0:00:00,4,8,21,22,23,24,43,44,49,50,61,41,44,49,54 ,60,61,63,65,67,77
5,1/5/2005
0:00:00,3,4,9,12,14,18,25,26,28,30,36,37,42,44,45, 48,58,73,75,76,80
6,1/6/2005
0:00:00,5,9,10,15,17,21,27,34,36,37,39,47,55,57,58 ,63,64,65,72,75,78
7,1/7/2005
0:00:00,5,7,12,14,15,18,20,23,27,28,30,35,44,45,46 ,57,62,64,65,69,72
8,1/8/2005
0:00:00,8,9,13,14,18,19,23,27,29,34,36,37,46,48,50 ,54,55,56,58,65,70
9,1/9/2005
0:00:00,3,5,6,16,22,23,24,27,28,29,37,41,43,44,46, 47,49,50,68,88,89
10,1/10/2005
0:00:00,1,7,8,9,11,14,20,27,29,33,43,53,55,58,59,6 2,67,69,71,77,92

Check table:
"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,,,,,
2,8,21,22,23,44,49,61,68,88,99,,,,,
3,9,21,49,46,49,54,61,62,72,77,,,,,
4,1,27,28,36,39,42,45,80,86,93,,,,,
5,1,22,27,33,43,53,67,77,92,95,,,,,
Again, I want to search all 10 numbers 7 22 23 33 45 41 48 55 78 87
(record1 of check table - Field D1 to D10) in all 21 numbers given in
1st Jan (record1 of Lotto table). Update the field "Six" to 1 in Check
table if any 6 numbers (out of the above 10 numbers) match, update
field "Seven" to 1 if 7 numbers match and so on.

Then make a same check in 2nd Jan and so on and go on incrementing the
values in field Six Seven Eight Nine and Ten with 1 as and when they
match.

Then check the second record of check tacble (That is next ten numbers)
and so on till the last combination of 10 numbers (last record of check
table)

After running the query/code, the check table should look like this:

"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,1,0,0,0,0
2,8,21,22,23,44,49,61,68,88,99,1,1,0,0,0
3,9,21,49,46,49,54,61,62,72,77,2,0,0,0,0
4,1,27,28,36,39,42,45,80,86,93,0,0,0,0,0
5,1,22,27,33,43,53,67,77,92,95,0,0,1,0,0

Maxi

Nov 13 '05 #9

P: n/a
Copying my tables in text file format here in the post. I don't have
any Query/code/forms/reports in my database. I don't want any forms or
reports. The only thing I want is Query or VBA code that would
accomplish my task.

Also uploading the zip file on my website which can be downloaded from:
http://www25.brinkster.com/shreejipc/MaxiLotto.zip

Lotto table:
"D_No","Date","P1","P2","P3","P4","P5","P6","P7"," P8","P9","P10","P11","P12","P13","P14","P15","P16" ,"P17","P18","P19","P20","P21"
1,1/1/2005
0:00:00,4,5,7,10,16,18,22,25,26,29,36,38,41,48,55, 49,52,57,59,64,78
2,1/2/2005
0:00:00,2,3,4,8,16,17,18,20,28,29,32,34,37,38,42,4 8,52,55,59,72,90
3,1/3/2005
0:00:00,1,2,9,18,22,35,42,44,47,49,50,51,54,56,60, 61,69,72,73,86,92
4,1/4/2005
0:00:00,4,8,21,22,23,24,43,44,49,50,61,41,44,49,54 ,60,61,63,65,67,77
5,1/5/2005
0:00:00,3,4,9,12,14,18,25,26,28,30,36,37,42,44,45, 48,58,73,75,76,80
6,1/6/2005
0:00:00,5,9,10,15,17,21,27,34,36,37,39,47,55,57,58 ,63,64,65,72,75,78
7,1/7/2005
0:00:00,5,7,12,14,15,18,20,23,27,28,30,35,44,45,46 ,57,62,64,65,69,72
8,1/8/2005
0:00:00,8,9,13,14,18,19,23,27,29,34,36,37,46,48,50 ,54,55,56,58,65,70
9,1/9/2005
0:00:00,3,5,6,16,22,23,24,27,28,29,37,41,43,44,46, 47,49,50,68,88,89
10,1/10/2005
0:00:00,1,7,8,9,11,14,20,27,29,33,43,53,55,58,59,6 2,67,69,71,77,92

Check table:
"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,,,,,
2,8,21,22,23,44,49,61,68,88,99,,,,,
3,9,21,49,46,49,54,61,62,72,77,,,,,
4,1,27,28,36,39,42,45,80,86,93,,,,,
5,1,22,27,33,43,53,67,77,92,95,,,,,
Again, I want to search all 10 numbers 7 22 23 33 45 41 48 55 78 87
(record1 of check table - Field D1 to D10) in all 21 numbers given in
1st Jan (record1 of Lotto table). Update the field "Six" to 1 in Check
table if any 6 numbers (out of the above 10 numbers) match, update
field "Seven" to 1 if 7 numbers match and so on.

Then make a same check in 2nd Jan and so on and go on incrementing the
values in field Six Seven Eight Nine and Ten with 1 as and when they
match.

Then check the second record of check tacble (That is next ten numbers)
and so on till the last combination of 10 numbers (last record of check
table)

After running the query/code, the check table should look like this:

"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,1,0,0,0,0
2,8,21,22,23,44,49,61,68,88,99,1,1,0,0,0
3,9,21,49,46,49,54,61,62,72,77,2,0,0,0,0
4,1,27,28,36,39,42,45,80,86,93,0,0,0,0,0
5,1,22,27,33,43,53,67,77,92,95,0,0,1,0,0

Maxi

Nov 13 '05 #10

P: n/a
Hi! Thanx for looking into my problem

Copying my tables in text file format here in the post. I don't have
any Query/code/forms/reports in my database. I don't want any forms or
reports. The only thing I want is Query or VBA code that would
accomplish my task.

Also uploading the zip file on my website which can be downloaded from:
http://www25.brinkster.com/shreejipc/MaxiLotto.zip

Lotto table:
"D_No","Date","P1","P2","P3","P4","P5","P6","P7"," P8","P9","P10","P11","P12","P13","P14","P15","P16" ,"P17","P18","P19","P20","P21"
1,1/1/2005
0:00:00,4,5,7,10,16,18,22,25,26,29,36,38,41,48,55, 49,52,57,59,64,78
2,1/2/2005
0:00:00,2,3,4,8,16,17,18,20,28,29,32,34,37,38,42,4 8,52,55,59,72,90
3,1/3/2005
0:00:00,1,2,9,18,22,35,42,44,47,49,50,51,54,56,60, 61,69,72,73,86,92
4,1/4/2005
0:00:00,4,8,21,22,23,24,43,44,49,50,61,41,44,49,54 ,60,61,63,65,67,77
5,1/5/2005
0:00:00,3,4,9,12,14,18,25,26,28,30,36,37,42,44,45, 48,58,73,75,76,80
6,1/6/2005
0:00:00,5,9,10,15,17,21,27,34,36,37,39,47,55,57,58 ,63,64,65,72,75,78
7,1/7/2005
0:00:00,5,7,12,14,15,18,20,23,27,28,30,35,44,45,46 ,57,62,64,65,69,72
8,1/8/2005
0:00:00,8,9,13,14,18,19,23,27,29,34,36,37,46,48,50 ,54,55,56,58,65,70
9,1/9/2005
0:00:00,3,5,6,16,22,23,24,27,28,29,37,41,43,44,46, 47,49,50,68,88,89
10,1/10/2005
0:00:00,1,7,8,9,11,14,20,27,29,33,43,53,55,58,59,6 2,67,69,71,77,92

Check table:
"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,,,,,
2,8,21,22,23,44,49,61,68,88,99,,,,,
3,9,21,49,46,49,54,61,62,72,77,,,,,
4,1,27,28,36,39,42,45,80,86,93,,,,,
5,1,22,27,33,43,53,67,77,92,95,,,,,
Again, I want to search all 10 numbers 7 22 23 33 45 41 48 55 78 87
(record1 of check table - Field D1 to D10) in all 21 numbers given in
1st Jan (record1 of Lotto table). Update the field "Six" to 1 in Check
table if any 6 numbers (out of the above 10 numbers) match, update
field "Seven" to 1 if 7 numbers match and so on.

Then make a same check in 2nd Jan and so on and go on incrementing the
values in field Six Seven Eight Nine and Ten with 1 as and when they
match.

Then check the second record of check tacble (That is next ten numbers)
and so on till the last combination of 10 numbers (last record of check
table)

After running the query/code, the check table should look like this:

"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,1,0,0,0,0
2,8,21,22,23,44,49,61,68,88,99,1,1,0,0,0
3,9,21,49,46,49,54,61,62,72,77,2,0,0,0,0
4,1,27,28,36,39,42,45,80,86,93,0,0,0,0,0
5,1,22,27,33,43,53,67,77,92,95,0,0,1,0,0

Maxi

Nov 13 '05 #11

P: n/a
Copying my tables in text file format here in the post. I don't have
any Query/code/forms/reports in my database. I don't want any forms or
reports. The only thing I want is Query or VBA code that would
accomplish my task.

Also uploading the zip file on my website which can be downloaded from:
http://www25.brinkster.com/shreejipc/MaxiLotto.zip

Lotto table:
"D_No","Date","P1","P2","P3","P4","P5","P6","P7"," P8","P9","P10","P11","P12","P13","P14","P15","P16" ,"P17","P18","P19","P20","P21"
1,1/1/2005
0:00:00,4,5,7,10,16,18,22,25,26,29,36,38,41,48,55, 49,52,57,59,64,78
2,1/2/2005
0:00:00,2,3,4,8,16,17,18,20,28,29,32,34,37,38,42,4 8,52,55,59,72,90
3,1/3/2005
0:00:00,1,2,9,18,22,35,42,44,47,49,50,51,54,56,60, 61,69,72,73,86,92
4,1/4/2005
0:00:00,4,8,21,22,23,24,43,44,49,50,61,41,44,49,54 ,60,61,63,65,67,77
5,1/5/2005
0:00:00,3,4,9,12,14,18,25,26,28,30,36,37,42,44,45, 48,58,73,75,76,80
6,1/6/2005
0:00:00,5,9,10,15,17,21,27,34,36,37,39,47,55,57,58 ,63,64,65,72,75,78
7,1/7/2005
0:00:00,5,7,12,14,15,18,20,23,27,28,30,35,44,45,46 ,57,62,64,65,69,72
8,1/8/2005
0:00:00,8,9,13,14,18,19,23,27,29,34,36,37,46,48,50 ,54,55,56,58,65,70
9,1/9/2005
0:00:00,3,5,6,16,22,23,24,27,28,29,37,41,43,44,46, 47,49,50,68,88,89
10,1/10/2005
0:00:00,1,7,8,9,11,14,20,27,29,33,43,53,55,58,59,6 2,67,69,71,77,92

Check table:
"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,,,,,
2,8,21,22,23,44,49,61,68,88,99,,,,,
3,9,21,49,46,49,54,61,62,72,77,,,,,
4,1,27,28,36,39,42,45,80,86,93,,,,,
5,1,22,27,33,43,53,67,77,92,95,,,,,
Again, I want to search all 10 numbers 7 22 23 33 45 41 48 55 78 87
(record1 of check table - Field D1 to D10) in all 21 numbers given in
1st Jan (record1 of Lotto table). Update the field "Six" to 1 in Check
table if any 6 numbers (out of the above 10 numbers) match, update
field "Seven" to 1 if 7 numbers match and so on.

Then make a same check in 2nd Jan and so on and go on incrementing the
values in field Six Seven Eight Nine and Ten with 1 as and when they
match.

Then check the second record of check tacble (That is next ten numbers)
and so on till the last combination of 10 numbers (last record of check
table)

After running the query/code, the check table should look like this:

"C_No","D1","D2","D3","D4","D5","D6","D7","D8","D9 ","D11","Six","Seven","Eight","Nine","Ten"
1,7,22,23,33,35,41,48,55,78,87,1,0,0,0,0
2,8,21,22,23,44,49,61,68,88,99,1,1,0,0,0
3,9,21,49,46,49,54,61,62,72,77,2,0,0,0,0
4,1,27,28,36,39,42,45,80,86,93,0,0,0,0,0
5,1,22,27,33,43,53,67,77,92,95,0,0,1,0,0

Maxi

Nov 13 '05 #12

P: n/a
There was a server error for a very long time and I tried to post my
reply several times that it the reason, the same post was sent multiple
times. Just look at the post below your reply.

Maxi

Nov 13 '05 #13

P: n/a
If you are not able to download the text files that I sent, then copy
and paste the same url in a new browser window and remove the hyphen
mark (-) between shr and eejipc. Is shouldn't be shr-eejipc but
shreejipc.

Maxi

Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.