Keeping Data Field Formats While Importing

[hinklewj]

Within Access I import a table once a month to replace an existing table with updated data. Both tables have the same data fields. My current
method is to import the table over the existing one, and then spend 20
minutes re-formatting all of the data fields.

Is there a way to update this data without having to reformat the data fields every time? I tried table linking, but Access won't allow me to update data field formats in a linked table.

Thanks!!

 

[dk]

What is the field you have to reformat? Can you post some sample data? What is the format that Access produces and what is the format that you want?

 

[hinklewj]

There are several fields I have to reformat. Such as defining the primary keys, stating that duplicates are OK, field lengths, etc.

It would be ideal if I can somehow keep the existing table with all its formats while bringing in the updated data, which is coming from an existing query.

Hey is there a way to bring in an additional field to my table, coming from a query? This would solve my problem all together.

Thanks,
Bill

 

[dk]

Hi,

Why don't you use an append query to update the data in your table? That won't (and can't) change your field definitions. Open the source data query in design view and click Query, Append Query. You'll be asked to choose which table you wish to append the data to. Choose your table and save the query.

Let me know if that helps or if I'm off track.

 

[hinklewj]

Instead of adding records, I need the replace certain data in the records. The Acct # is the primary key, but their score changes monthly. In addition, we are adding new accounts monthly...would I use an update query? I'm new to using append and update functions. Thanks!

Acct # Score
1 10.00
2 98.59
3 54.56
4 64.53
5 99.54

to the following

# Score
1 12.00
2 54.55
3 54.55
4 36.44
5 34.44
6 54.01
7 64.55
8 61.21