Append matching records

[nicomak]

Hello All,

I'm trying to append records from table 1 to table 2. However, only matching
fields/columns from table 1 will be appended to table 2. For example, table 2
has 30 fields but table 1 only has 12 fields that match the 30 in table 1 and
should be appended to table 2. When I run my append query I get an 'Enter
Parameter Value' pop up box for the unmatched 18 fields, which I have to okay
to get the query to run. How do I get rid of this pop up box?

I tried search the discussion groups and was unable to find any other posts
that were similar to my problem. Unless I was using the wrong search words.
I'd appreciate any help.

Thanks!

nicomak

 

[Evi]

Base a query on Table 1 by clicking on the table and going to Insert, Query,
Design View, only add the fields to the query grid which you need to append.

Turn the query into an Append query by using the black down arrow next to
the Query Type button.
If some of Table2's fields don't have the same name as those in Tbl1 then
click in the grey arrow under that field, in the Append To row, and choose
the correct field. (the arrow will appear only when you click on the row)
Evi

 

[nicomak via AccessMonster.com]

Hi Evi,

Thanks for the reply.

I forgot to mention that the matching fields that need to be appended are not
always the same. Sometimes it could be 12 fields, other times 15 or other
times 23. I don't want to have to rewrite the query every time. I just want
the append query to determine which fields match and insert those records
into table.

Thanks,

nicomak

Base a query on Table 1 by clicking on the table and going to Insert, Query,
Design View, only add the fields to the query grid which you need to append.

Turn the query into an Append query by using the black down arrow next to
the Query Type button.
If some of Table2's fields don't have the same name as those in Tbl1 then
click in the grey arrow under that field, in the Append To row, and choose
the correct field. (the arrow will appear only when you click on the row)
Evi

Hello All,

[quoted text clipped - 12 lines]

nicomak

 

[John Spencer]

The only way I can think of to do something like this is to use VBA to build
the query string.

I assume that you mean fields "match" if the field name in the source table
matches the field name in the destination table AND the field types are the
same. Is that correct?

What do you want to do about duplicate records? For instance, if you should
happen to attempt to import the same source twice, what do you want to
happen?



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Evi,

Thanks for the reply.

I forgot to mention that the matching fields that need to be appended are
not
always the same. Sometimes it could be 12 fields, other times 15 or other
times 23. I don't want to have to rewrite the query every time. I just
want
the append query to determine which fields match and insert those records
into table.

Thanks,

nicomak

Base a query on Table 1 by clicking on the table and going to Insert,
Query,
Design View, only add the fields to the query grid which you need to
append.

Turn the query into an Append query by using the black down arrow next to
the Query Type button.
If some of Table2's fields don't have the same name as those in Tbl1 then
click in the grey arrow under that field, in the Append To row, and choose
the correct field. (the arrow will appear only when you click on the row)
Evi

Hello All,

[quoted text clipped - 12 lines]

nicomak

 

[nicomak via AccessMonster.com]

Hi John,

Yes, that's what I mean by "match". I'm not really concerned with duplicate
records. Basically I have many tables each with different field names, that I
want to filter out only the matching field names and insert (or convert)
these records into another table.

Can you elaborate on the VBA? I've never used VBA before.

Much appreciated.

nicomak

The only way I can think of to do something like this is to use VBA to build
the query string.

I assume that you mean fields "match" if the field name in the source table
matches the field name in the destination table AND the field types are the
same. Is that correct?

What do you want to do about duplicate records? For instance, if you should
happen to attempt to import the same source twice, what do you want to
happen?

Hi Evi,

[quoted text clipped - 28 lines]