D
deb
I found this article from Douglas Steele...
Query that will update or insert as required.
For the sake of discussion, I'll assume that there are two tables
(ExistingData, and NewData), and that the two tables are identical in
structure.
1. Create a new query
2. Add tables NewData and ExistingData to the query.
3. If a relationship line isn't drawn between the two tables, do so now,
relating them by their primary key.
(Note that if it's a compound primary key, you need to join on each of the
fields in the index.)
4. Double-click on the relationship line joining the two tables, and choose
the option "Include ALL
records from 'NewData' and only those records from 'ExistingData' where the
joined fields are equal.",
then click OK. (If you're dealing with a compound primary key, you need to
do this for each line
joining the tables.)
5. Drag all of the fields from table ExistingData into the query grid.
6. From the Query menu, select Update Query to change the Select query to an
Update query.
7. Now, for every field in the query, go to the Update To cell and type
[NewData].[name of the field].
8. Save the query with an appropriate name.
When I create this. I get the error "Too many fields defined."
Unfortunately I have 160 fields. They are imported via excel.
Is there anything I can do to get around this?
Thanks
Query that will update or insert as required.
For the sake of discussion, I'll assume that there are two tables
(ExistingData, and NewData), and that the two tables are identical in
structure.
1. Create a new query
2. Add tables NewData and ExistingData to the query.
3. If a relationship line isn't drawn between the two tables, do so now,
relating them by their primary key.
(Note that if it's a compound primary key, you need to join on each of the
fields in the index.)
4. Double-click on the relationship line joining the two tables, and choose
the option "Include ALL
records from 'NewData' and only those records from 'ExistingData' where the
joined fields are equal.",
then click OK. (If you're dealing with a compound primary key, you need to
do this for each line
joining the tables.)
5. Drag all of the fields from table ExistingData into the query grid.
6. From the Query menu, select Update Query to change the Select query to an
Update query.
7. Now, for every field in the query, go to the Update To cell and type
[NewData].[name of the field].
8. Save the query with an appropriate name.
When I create this. I get the error "Too many fields defined."
Unfortunately I have 160 fields. They are imported via excel.
Is there anything I can do to get around this?
Thanks