Web Development

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.

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.

This question was closed by the author