Update Query

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

Guest

I have posted my question on 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.

http://www25.brinkster.com/shr­eejipc/Mihit/BoardAccessQuesti­on.htm
or
http://www.geocities.com/mac_s­ee/BoardAccessQuestion.htm

ByteMyzer from google groups had helped me in a similar kind of a situation
(link given below). I don't know vba coding and I tried a lot to accomplish
this task but unsuccessful. Can anybody help me on this?

http://groups-beta.google.com/group...a10c6927721/534a4ff6334b1cee#534a4ff6334b1cee

Maxi
 
Many people here are very reluctant to go to strange websites and
download unknown pages - especially since you give no hint what your
question might be. You're much more likely to get an answer if you
explain carefully in your newsgroup message.
 
I want to check the first combination of the Check table (7, 22, 23, 33, 35,
41, 48, 55, 78, 87) as to how many numbers in this combination came on 1st
Jan (record 1 of Lotto table). The Answer is 6. Then put "1" in the first
record of Check table under the field "Six". Check the same combinatino in
2nd Jan (Record 2 of Lotto table) The answer is 2 so it won't go anywhere and
move to record three of Lotto table. Do the same till EOF of record table. If
another 6 numbers match, change the number "1" to "2" in first record of
Check table. Once we reach in EOF. Pick the next combination of the Check
table and do the same task. Do this till all the five combinations are done.
(Note: These 10 records in Lotto table and 5 records in Check table are just
examples. I have lot of data in both the tables).

Please see the below link to view the table structure and table data. If the
page does not open, copy-paste the url and paste it in a new broswer window.

http://www25.brinkster.com/shr­eejipc/Mihit/BoardAccessQuesti­on.htm
or
http://www.geocities.com/mac_s­ee/BoardAccessQuestion.htm

ByteMyzer from google groups had helped me in a similar kind of a situation.
I don't know vba coding and I tried a lot to accomplish this task but
unsuccessful. Can anybody help me on this?

To view ByteMyzer's help on a similar situation, please go to the below link

http://groups-beta.google.com/group...a10c6927721/534a4ff6334b1cee#534a4ff6334b1cee

Any help will be appreciated

Maxi
 
Neither of the links you posted actually work even when pasted into a
new browser window.

Your table structure is making the task vastly more difficult. You
should normalise your data into three or more tables:

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 - or is it 11 - records per combination)
C_No (foreign key)
PickedNumber
(primary key includes both these fields)

Don't worry if this means having a few million records.

Once you've simplified your structure, ask in the Queries newsgroup
(microsoft.public.access.queries) and they'll show you how to generate
the output you want.
 
If you watch the website address url, the newsgroups automatically puts
hypens "-" symbols in between.

You will have to remove those in order to view the table data and structure.

In the first url,
replace shr­-eejipc with shr­eejipc AND BoardAccessQuesti­-on with
BoardAccessQuesti­on

In the second url,
replace mac_s-ee with mac_see

Maxi
 
I did that before I wrote my last message. You need to normalise your
data along the lines I suggested.
 
I did ask but no reply as yet. Can you look into it please?
Click to expand...

This is what you posted in .queries:
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] has suggested me to normalise my data
into three or more tables and ask for an answer in the
(microsoft.public.access.queri­es) 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)
Click to expand...

To populate the new tables you need to create and execute a series of
Append queries.
First, make certain that you have an up-to-date backup of your database.

1) tblDraws
Create an append query whose source is your present Lotto table,
appending to tblDraws, with the D_No and date fields from Lotto going to
the corresponding fields in tblDraws.

2) tblNumbersDrawn
Create an append query whose source is Lotto, appending to
tblNumbersDrawn, with the D_No field from Lotto going to D_No in
tblNumbersDrawn and P1 going to DrawnNumber.

After executing this query, modify it to replace P1 with P2. Execute the
modified query and repeat for P3..P21.

3) tblCombinations
The idea is the same as (2), except that the data comes from your Check
table and you use successive versions of the query to append C_No to
C_No F1 to F10 to PickedNumber.
 
Back
Top