General discussion

  • Creator
    Topic
  • #2319055

    MS Access Append Query Problem

    Locked

    by mike thompson ·

    I am attempting to add records from one table (table A) to table B.

    Specifically, I’m trying to match the fields as follows:

    Table A Table B
    ——- ——-
    Amount to FY 2003 (IF another field for the
    same record on Table “Grant
    Year” equals 2003)

    Amount to FY 2002 (“” 2002), and so on.
    ————————————————-
    What I’ve tried so far …

    Under the append query column (in design view) I included the following criteria: [Table A]![Grant Year]=2003

    I repeated this for years 2002 – 2000.

    When I look at the datasheet view it shows a zillion records to be appended when table A only had 45 records to begin with.

    Any suggestions would be greatly appreciated. I’ve thought of modifying the field in Table B to directly correspond with table A’s fields, but I’m stubborn. I believe this should work, but how??

    Thanks in adance!

    Mike

All Comments

  • Author
    Replies
    • #3533094

      MS Access Append Query Problem

      by miss kitty ·

      In reply to MS Access Append Query Problem

      An Append query will keep adding records to a table if there is no specific key. So if you keep running the query without running a delete query on Table B, the table will keep growing. I set up work tables for the users to use and always have a delete query run before the append query runs.

      • #3533028

        MS Access Append Query Problem

        by mike thompson ·

        In reply to MS Access Append Query Problem

        Thanks Miss Kity, I’ll keep that in mind. However, I got the zillion records on the first run.

        Any ideas on how to conditionally append one field in table A to one of 3 possible fields in table B, based on the value of another field in table A?
        Thanks Again!

        Mike

    • #3384956

      MS Access Append Query Problem

      by rdwilson2 ·

      In reply to MS Access Append Query Problem

      Comment from Mike Thompson on 5/15/03:
      Thanks Miss Kity, I’ll keep that in mind. However, I got the zillion records on the first run.

      Regarding your follow-up question:
      “Any ideas on how to conditionally append one field in table A to one of 3 possible fields in table B, based on the value of another field in table A?”

      You need to have a conditional expression in the data-to-be-appended field for each of those theree columns. For instance “Iif(([TableA]![Grant_Year] = 2003), [TableA]![Grant_Amount], null)” could be put in the data-to-be-appended field for the Grant_Amount column of TableB.

      Regarding your initial question:

      If I understood correctly what you said you did, I would expect you might have 4 times as many records in your.

      Generally, the approach I take is to determine a unique characteristic from Table A (e.g. the Project Name or Grant Name) and make an initial pass that appends the non-variant portions fo the records. (The idea here is to create the records without any of the data specific to the FY Years and to also determine a unique key that can be used to match the records.) Then I create a set of UPDATE queries that update the appropriate fields in the various records. I also tend backup the tables being manipulated either by copying them to another database or, at the very least, creating a back-up of the entire database. Once I have tested the various queries, I then put them in a macro, restore the tables, and fire up the macro.

      If you’d like, I could make a quick pass at this if you would send me the two tables in question.

      • #3384414

        MS Access Append Query Problem

        by mike thompson ·

        In reply to MS Access Append Query Problem

        Thanks, that’s helpful.

        Since originally posting this question, I tried piggybacking a couple of queries together that feed a crosstab query that I can create a report from, with the information I need. Back then, I was definitely trying to accomplish too much in one pass.

        Thanks again!

        Mike

    • #3384413

      MS Access Append Query Problem

      by mike thompson ·

      In reply to MS Access Append Query Problem

      This question was closed by the author

Viewing 2 reply threads