Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

MS Access Append Query Problem

Locked

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

Topic

MS Access Append Query Problem

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.

MS Access Append Query Problem

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

MS Access Append Query Problem

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.

MS Access Append Query Problem

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

MS Access Append Query Problem

In reply to MS Access Append Query Problem

This question was closed by the author

[Author]

Replies