Copy Table data to another Table

  • Thread starter Thread starter Richardb
  • Start date Start date
R

Richardb

I have a database that several people have to use a form to collect
information into a table. This will be done on a weekly basis. Their
database will be email back to us and than we want to gather all data into
one table in a single database which will be used for reports. Question is
what is the best way to copy the tables into the master database? Would like
to be able to eliminate duplicates since the next time they email the
database it will have some of the same info. Any help would be greatly
appreciated.

Richard
 
Richard,
Easy way would be to run append queries from each emailed database to update
the info into the table in the master database.
To prevent duplicates you can set up an index on the field or fields which
must not be duplicated.
You do this on the table in the master database.
When you run the append queries, access just fails to add any duplicate
info.
To set up a unique index
--table in design view
--choose the index button on the toolbar
--on the dialog that opens
--in the column for index name type NoDupes
--in the column for field name select one of the field names (that mustn't
be duplicated) from the drop down list
--at lower panel of dialog set the value for Unique to Yes

to add other fields to this index,
--on the next row leave the column for index name blank
--in the column for field name select the next field that needs to be part
of the unique index
--this field is now added to the unique index

to add yet another field to the unique index
--on the next row leave the column for index name blank
--in the column for field name select the next field that needs to be part
of the unique index
--this field is now added to the unique index

to add yet another field to the unique index repeat the above

When you run the append queries, access will give you a warning message
about not being to append all the records -
the number of records it can't add in the message will be the ones that are
the duplicates

Jeanette Cunningham
 
Thank you

Jeanette Cunningham said:
Richard,
Easy way would be to run append queries from each emailed database to update
the info into the table in the master database.
To prevent duplicates you can set up an index on the field or fields which
must not be duplicated.
You do this on the table in the master database.
When you run the append queries, access just fails to add any duplicate
info.
To set up a unique index
--table in design view
--choose the index button on the toolbar
--on the dialog that opens
--in the column for index name type NoDupes
--in the column for field name select one of the field names (that mustn't
be duplicated) from the drop down list
--at lower panel of dialog set the value for Unique to Yes

to add other fields to this index,
--on the next row leave the column for index name blank
--in the column for field name select the next field that needs to be part
of the unique index
--this field is now added to the unique index

to add yet another field to the unique index
--on the next row leave the column for index name blank
--in the column for field name select the next field that needs to be part
of the unique index
--this field is now added to the unique index

to add yet another field to the unique index repeat the above

When you run the append queries, access will give you a warning message
about not being to append all the records -
the number of records it can't add in the message will be the ones that are
the duplicates

Jeanette Cunningham
Click to expand...
 
Back
Top