update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table (Table name:Lotto) with 61 fields (Names: Pos1, Pos2, Pos3,
Pos4, Pos5, Jan1, Jan2, Jan3 ...... Feb25) All these fields are of BYTE data
type. I have ten records in this table. I have filled up numbers in Pos1 to
Pos5 for all 10 records. Data for Jan1..... Feb25 is ZERO.

I have another table (Table name:Draw) with 56 fields (Names: Jan1, Jan2,
Jan3 ...... Feb25). All these fields are of BYTE data type. I have 21 records
in this table. These 21 records are just the numbers that came in the draw.

Now I have shortlisted 10 combinations of 5 numbers which I have entered in
the Lotto table. I want to check how many numbers appreared in a particular
day.

Example:
My combinations are:
Pos1 Pos2 Pos3 Pos4 Pos5
1 5 12 15 21
2 4 12 16 18

I want to check, out of 1 5 12 15 21, how many numbers came on Jan1 (The
query should check this in Draw table in the field Jan1) and update the count
in Jan1 field of Lotto table next to that combination.

Maxi
 
I have a table (Table name:Lotto) with 61 fields (Names: Pos1, Pos2, Pos3,
Pos4, Pos5, Jan1, Jan2, Jan3 ...... Feb25) All these fields are of BYTE data
type. I have ten records in this table. I have filled up numbers in Pos1 to
Pos5 for all 10 records. Data for Jan1..... Feb25 is ZERO.
Click to expand...

This table IS INCORRECTLY DESIGNED.

Storing data (dates) in fieldnames is *NEVER* a good idea. A much
better design would be a table with seven fields: a unique primary key
(such as an Autonumber), a date field, and the five lotto numbers.
I have another table (Table name:Draw) with 56 fields (Names: Jan1, Jan2,
Jan3 ...... Feb25). All these fields are of BYTE data type. I have 21 records
in this table. These 21 records are just the numbers that came in the draw.

Now I have shortlisted 10 combinations of 5 numbers which I have entered in
the Lotto table. I want to check how many numbers appreared in a particular
day.

Example:
My combinations are:
Pos1 Pos2 Pos3 Pos4 Pos5
1 5 12 15 21
2 4 12 16 18

I want to check, out of 1 5 12 15 21, how many numbers came on Jan1 (The
query should check this in Draw table in the field Jan1) and update the count
in Jan1 field of Lotto table next to that combination.
Click to expand...

And this field SHOULD NOT EXIST, period. Just count the number of
occurances in a Query and display the Query.

John W. Vinson[MVP]
 
Is this the correct way?

[Table:Lotto]
[Fields:Autonumber, Date, D1, D2, D3 ...... D21]
[Example data:]
1 Jan1 9 10 13 14 15 18 24 33 39 41 44 48 56 61 63 69 73 77 78 85 99
2 Jan2 6 10 11 16 17 18 22 24 28 33 42 45 48 55 62 63 68 70 71 79 86

[Table:Check]
[Fields: Autonumber, P1, P2, P3, P4, P5, 1, 2, 3 .... 56]
[Example data:]
1 5 10 20 55 56 __ __ __ __
2 9 23 24 42 70 __ __ __ __
3 9 22 54 70 96 __ __ __ __
..
..
10 2 5 16 18 22 __ __ __ __

I want to count how many numbers out of 5 10 20 55 56 came in 1st Jan and
put that value in the first __, do this till Feb25 (56th field in the check
table) and then move on to the second combination 9 23 24 42 70 (record2 of
check table).

I am not sure how to do this.

Maxi
This table IS INCORRECTLY DESIGNED.

Storing data (dates) in fieldnames is *NEVER* a good idea. A much
better design would be a table with seven fields: a unique primary key
(such as an Autonumber), a date field, and the five lotto numbers.
Click to expand...
 
I have posted my question in my website as the alignment in this post goes
for a toss. The text editor wraps up the data in the next line by default and
because of which I am not able to copy my data in this post.

Please see the below link for my question.
http://www25.brinkster.com/shreejipc/Mihit/Book1.htm

Maxi

mac_see said:
Is this the correct way?

[Table:Lotto]
[Fields:Autonumber, Date, D1, D2, D3 ...... D21]
[Example data:]
1 Jan1 9 10 13 14 15 18 24 33 39 41 44 48 56 61 63 69 73 77 78 85 99
2 Jan2 6 10 11 16 17 18 22 24 28 33 42 45 48 55 62 63 68 70 71 79 86

[Table:Check]
[Fields: Autonumber, P1, P2, P3, P4, P5, 1, 2, 3 .... 56]
[Example data:]
1 5 10 20 55 56 __ __ __ __
2 9 23 24 42 70 __ __ __ __
3 9 22 54 70 96 __ __ __ __
.
.
10 2 5 16 18 22 __ __ __ __

I want to count how many numbers out of 5 10 20 55 56 came in 1st Jan and
put that value in the first __, do this till Feb25 (56th field in the check
table) and then move on to the second combination 9 23 24 42 70 (record2 of
check table).

I am not sure how to do this.

Maxi
This table IS INCORRECTLY DESIGNED.

Storing data (dates) in fieldnames is *NEVER* a good idea. A much
better design would be a table with seven fields: a unique primary key
(such as an Autonumber), a date field, and the five lotto numbers.
Click to expand...
And this field SHOULD NOT EXIST, period. Just count the number of
occurances in a Query and display the Query.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Is this the correct way?
Click to expand...

No. It emphatically is NOT.

Any table with fields with 1, 2, 3 as the end of their fieldname is
ipso facto in violation of First Normal Form. You're storing a one to
many relationship IN EACH RECORD. Your Lotto table should have *three
fields* - LottoDate, Seq, and Draw. Seq would have values 1 to 21 (or
could be left out altogether); if there are always 21 numbers drawn
you'ld add 21 records with Draw in this table.

If you're hoping to use this database to predict future lottery draws,
you're on the wrong track. In a fair lottery the probability of
drawing a given number is COMPLETELY UNAFFECTED by any previous draws.
If you are flipping a fair coin and flip ten heads in a row - an event
which will happen once in 1024 runs of ten, on average - the
probability of getting heads on the eleventh flip is still 1/2. The
same principle applies. You're wasting your time and effort!

John W. Vinson[MVP]
 
Apparently you found it necessary to create a new thread on this question in
another news group. I did search that other NG for your name since I thought
it might be a duplicate thread but your name didn't come up since you
changed NGs. Not a good idea if you like getting assistance.

My reply in that new thread was about the same advice as you have been given
by John Vinson in this thread.

--
Duane Hookom
MS Access MVP


mac_see said:
I have posted my question in my website as the alignment in this post goes
for a toss. The text editor wraps up the data in the next line by default
and
because of which I am not able to copy my data in this post.

Please see the below link for my question.
http://www25.brinkster.com/shreejipc/Mihit/Book1.htm

Maxi

mac_see said:
Is this the correct way?

[Table:Lotto]
[Fields:Autonumber, Date, D1, D2, D3 ...... D21]
[Example data:]
1 Jan1 9 10 13 14 15 18 24 33 39 41 44 48 56 61 63 69 73 77 78 85 99
2 Jan2 6 10 11 16 17 18 22 24 28 33 42 45 48 55 62 63 68 70 71 79 86

[Table:Check]
[Fields: Autonumber, P1, P2, P3, P4, P5, 1, 2, 3 .... 56]
[Example data:]
1 5 10 20 55 56 __ __ __ __
2 9 23 24 42 70 __ __ __ __
3 9 22 54 70 96 __ __ __ __
.
.
10 2 5 16 18 22 __ __ __ __

I want to count how many numbers out of 5 10 20 55 56 came in 1st Jan and
put that value in the first __, do this till Feb25 (56th field in the
check
table) and then move on to the second combination 9 23 24 42 70 (record2
of
check table).

I am not sure how to do this.

Maxi
This table IS INCORRECTLY DESIGNED.

Storing data (dates) in fieldnames is *NEVER* a good idea. A much
better design would be a table with seven fields: a unique primary key
(such as an Autonumber), a date field, and the five lotto numbers.
Click to expand...
And this field SHOULD NOT EXIST, period. Just count the number of
occurances in a Query and display the Query.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top