Loading ...
Sorry, an error occurred while loading the content.

Re: [baseball-databank] Re: Help with update query in Access

Expand Messages
  • tmasc@yahoo.com
    ... 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
    Message 1 of 6 , Mar 7, 2003
    • 0 Attachment
      --- 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
      happening.

      Tom

      __________________________________________________
      Do you Yahoo!?
      Yahoo! Tax Center - forms, calculators, tips, more
      http://taxes.yahoo.com/
    • Seldon46@AOL.com
      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),
      Message 2 of 6 , Mar 7, 2003
      • 0 Attachment
        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.

        Good luck.
      • elbowruth
        ... I always do that, but it s a good reminder. ... The Errata table exists because I was able to write a query to isolate the offending records, and it is a
        Message 3 of 6 , Mar 8, 2003
        • 0 Attachment
          >--- In baseball-databank@yahoogroups.com, Seldon46@A...>wrote:

          >[...] make a copy of the master table.
          >
          I always do that, but it's a good reminder.

          >#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 the
          >Errata table, then you'll need to try #2 below.

          TangoTiger got me on the right track for an SQL solution, and after I
          got through about 40 cryptic Access syntax error messages (along with
          the even more helpful Help file comments), I got the master table
          corrected.

          Thanks for you help, though, especially with the IIF technique, which
          I probably will find useful.

          Cliff
        Your message has been successfully submitted and would be delivered to recipients shortly.