update query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mac_see - 26 Feb 2005 03:43 GMT

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

Reply to this Message

John Vinson - 26 Feb 2005 04:25 GMT

>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.

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
[quoted text clipped - 13 lines]
>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.

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

John W. Vinson[MVP]

Reply to this Message

mac_see - 26 Feb 2005 18:19 GMT

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.

> And this field SHOULD NOT EXIST, period. Just count the number of
> occurances in a Query and display the Query.
>
> John W. Vinson[MVP]

Reply to this Message

mac_see - 26 Feb 2005 23:09 GMT

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

> Is this the correct way?
>
[quoted text clipped - 33 lines]
> >
> > John W. Vinson[MVP]

Reply to this Message

Duane Hookom - 27 Feb 2005 04:21 GMT

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.

Signature

Duane Hookom
MS Access MVP

>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
[quoted text clipped - 45 lines]
>> >
>> > John W. Vinson[MVP]

Reply to this Message

John Vinson - 27 Feb 2005 00:42 GMT

>Is this the correct way?

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]

Reply to this Message