Updating the Master Table

[Victor]

How do I do this? I tried using an append query, but Access says I need at least one destination field. What I have is last weeks data with results exported to three tables: ALL_MSA, ALL_IMPC, and ALL_X. Now I 'd like to add these to their respective Master Tables. I would appreciate your help.

[hurrikane]

Vic,
I assume you mean by 'exported to 3 tables' you mean the export text files from the export programs.
If that is the case this is what you do.

go to File>>Get external data
change the file type to text and go to the HTR dir where the all_msa all_impc tables are
select the one you want to import
an import GUI come up. it should have the button "delimited files: checked
hit next
select the box that says "First row contains field names"
hit next
now you can select a new table or an existing table. click the down arrow on the existing tables and find the table you want the data to go in to..All_MSA or All_IMPC
this will now import.

Be sure you have your primary keys set so you don't import duplicates. If you get errors they will be put into a table showing where the errors occured.
most common errors

1. you didn't delete the old All_MSA file. the new files you export will be appended to the old All_msa table. This will cause duplicate records. It's not a problem as Access will only import one set of data as long as you have primary key set. A word of caution. If you exported one time with no results and then exported again with results. Both sets of data are in the same file. Problem is the data without result will be imported first and the data with results will be discarded. Best to be sure you delete the old All_MSA.txt file each time after you are done.
Same for all_IMPC

2. you forgot to click use first colums for data name. This causes the column names to be inserted as data and causes an error. this is not a problem as access will not allow you to import the column names as data.

Hope this helps

[MikeDee]

Victor

I'm reading your problem a little differently then HK. I am assuming that you have a master table of htr data that you have saved and now you have some additional days of htr data that you want to add to your master table.

If this is the situatiion you are correct in using a append query. You build the append query on the table with the data that you want to append to the master table. so put the all_msa table in the design field of your query.

Change the query type to append. Access will ask you which table you want to append the data to. Put in your master table anem with the msa data

Now put all of the fields in the all_msa table into the design grid of your query. You will see the corresponding field names from your master table appear in the append to line. Access is telling that it will append the data in the all msa table to the field with the same name in the mastr table.

another way to do this is just to put the * in the desing grid this tells access to append all fields to the corresponding field in your master table. With this method make sure that each table has the exact same field names or it will give you a error message.

run the query and the data will be appended to your master. The data also remains in the all msa table. the append is like doing a copy not a cut and paste.

now repeat for you other tables

[Victor]

Hurrikane,

What I'm trying to do is append (add) the past weeks races to my total race tables. In a separate database, I have (4) weeks of races in three tables: ALL_MSA, ALL_IMPC, and ALL_X. In my current week database, I have the past weeks races already imported into three tables with the same names as those in the separate database. How do I bring my current week races into the larger database and then have (5) weeks of races?

[Victor]

Thank you gentlemen.

[hurrikane]

wasn't quite sure how to read it...I don't run mine that way just to keep down table space but ...seems the Mike man has come to the rescue. Good luck!