General discussion

Locked

MS Access Append Query Problem

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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Access Append Query Problem

by Miss Kitty In reply to MS Access Append Query Pr ...

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.

Collapse -

MS Access Append Query Problem

by Mike Thompson In reply to MS Access Append Query Pr ...

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

Collapse -

MS Access Append Query Problem

by RDWilson2 In reply to MS Access Append Query Pr ...

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.

Collapse -

MS Access Append Query Problem

by Mike Thompson In reply to MS Access Append Query Pr ...

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

Collapse -

MS Access Append Query Problem

by Mike Thompson In reply to MS Access Append Query Pr ...

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums