Re: [baseball-databank] Re: Help with update query in Access
- --- elbowruth <baseball318@...> wrote:
This doesn't necessarily apply to what you are doing,
but as a caution to others attempting this: be careful
of joins. The join is not necessarily a 1-1 join. In
my example, if the fields being joined are not the
keys (or all of the keys), you might end up with a
1-many, and you may accidently update records you
didn't want. Most likely, you won't mind (and would
prefer) the 1-many. But, just realize what's
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
- Cliff - here are two ideas for your Access challenge. Neither explicitly uses SQL. Before you try either one of these (or any other approach, for that matter), make a copy of the master table.
#1 - Ignore the Errata Table. If the "Update" query can identify and select the records that need to be "updated" and what the correct score should be, then it can also "update them". In the "Update to" field on the query design screen just type in formulas (use the wizard) that identify the case and the correct scores values by using "IIF" statements.
If the "correct" score for the 400 games is only in the Errata table, then you'll need to try #2 below.
#2 - First, name the field for the score in the Errata table as "CORRECTSCORE". Let's say the name of the field in the master file is "SCORE". Create a "update" query that defines the link (presumably the "GameID" field) between the master table and the Errata table. Now, in the "Update to" field on the query design grid, type in the formula "IIF(CORRECTSCORE IS NULL, SCORE=SCORE, SCORE=CORRECTSCORE"). Now, "run" the query. It will rebuild the master table and replace SCORE values with a CORRECTSCORE value when there is a matching GameID record in the Errata file.
>--- In email@example.com, Seldon46@A...>wrote:I always do that, but it's a good reminder.
>[...] make a copy of the master table.
>#1 - Ignore the Errata Table.[...]The Errata table exists because I was able to write a query to isolate
the offending records, and it is a lot easier to make the corrections
by hand on a line-by-line basis then trying to find the appropriates
ones among a half million lines. This wasn't a case where I could
apply a formula to make the updates.
>If the "correct" score for the 400 games is only in theTangoTiger got me on the right track for an SQL solution, and after I
>Errata table, then you'll need to try #2 below.
got through about 40 cryptic Access syntax error messages (along with
the even more helpful Help file comments), I got the master table
Thanks for you help, though, especially with the IIF technique, which
I probably will find useful.