Looking for a query

[mac_see]

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 field
(F1,F2,....F10, R6, R7, R8, R9, R10). I have few lacs combinations o
10 numbers in the Check table in the first 10 fields F1-F10).

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

John Nurick [Microsoft Access MVP] from Microsoft newsgroups ha
suggested me to normalise my data into three or more tables and as
for an answer in the microsoft.public.access.querie­s) newsgroup. Bu
I am really confused on how to do it as I don't know anything abou
normalisation. I am not able to understand the structure that h
wants me to have. Can any one explain to me what changes do I have t
make to my current database, how to use relationships and what querie
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.

Max

 

[mac_see]

I want to check a combination of 10 numbers and NOT just one number. 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

Note: If you copy and paste the website address onto a new browser window 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

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 @ (e-mail address removed) ( mac<underscore>mahesh<at>hotma­il<dot>com )


Maxi

 

[mac_see]

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

Note (If you are not able to download the file, just copy paste th
entire website address in a new browser window and remove the hyphe
mark (-) before maxi.

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

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

Then check the second record of check tacble (That is next te
numbers) and so on till the last combination of 10 numbers (las
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 an
send it to me @ (e-mail address removed)
(mac<underscore>mahesh<at>hotmail<dot>com

Max