Impotring new field from Excel to Access table

[vangogh228]

I have been given a new set of fields to add to a database table. The new information to be added is all in Excel. I know I can 'append' new records to a table... but how do you add a new field and get the information into the appropriate already-existing records?

The database has client demographic information, with a key field of SSN. Now they want to track spouse information. The Excel spreadsheet has the client's SSN and the client's spouse info. I need to get this new information into my current records.

Thanks for any help!!

Tom

 

[bradcccs]

If this is a once off (or not very often) occurence, couldn't you edit the table properties, and add the necessary fields?

Then you could perform and update query to update the records (update the new fields) based on the linking "SSN" number and data from the excel spreadsheet.

Brad.

 

[vangogh228]

Brad:

UPDATE QUERY! Thanks! I have never had the necessity to use one, had never really studied what they did and had never conceptualized why I would ever use one. After doing a little reading, this is obviously exactly what I need to do.

Thanks for the kick in the pants.

Tom

 

[vangogh228]

Ok.... I need another kick in the pants.

My main table is called Personal Info. The key field is SSN. I have just added a field to the Personal Info table called Spouse. I have been given a table of information called Spouse Names that has the primary person's SSN related to the key field in the Personal Info table, and the spouse's name.

I need to get the spouse names from the new table and put them into the new field of the primary table. I started out writing an update query, but realized I have no idea how to finish this.

ANY HELP IS GREATLY APPRECIATED.

Tom

 

[bradcccs]

Here comes the boot

Copy this to the SQL view of your update query:


UPDATE [Personal Info] INNER JOIN [Spouse Names] ON [Personal Info].ssn = [Spouse Names].ssn SET [Personal Info].Spouse = [spouse names].spousename;


I have used your table and field names where available. The only one you will have to verify is the field name that is storing the spouse name in the table "Spouse Names". I have represented this as "spousename" in the Update Query above.

Thus you will only have to change:

UPDATE [Personal Info] INNER JOIN [Spouse Names] ON [Personal Info].ssn = [Spouse Names].ssn SET [Personal Info].Spouse = [spouse names].spousename;

After copying the infor, and correcting the "spousename" field name, have a look at the query in design view, and I think you will get the idea of how to construct them. (Fairly easy)

Make sure that before activating any update query that you have a backup of the primary table (or all tables for that matter) as unexpected results can cause headaches.

Let me know how you get on.

Brad.

 

[vangogh228]

Brad:

Thanks for the help. Before coming back on, I figured out a procedure that works:

Add the new field to main table, as you suggested.

Simply copy/paste the appropriate range from the Excel spreadsheet onto the Tables tab of the database, creating a new table (be careful to name your Excel sheet tab first to avoid conflicts).

Edit the names of the new table's fields (and properties) as appropriate. Make the properties as consistent as possible with the target table's fields.

Write a query in design view that pulls ALL the fields from the MAIN table, and the field from the 'imported' table that you wish to draw from.

In the datasheet view of the query, simply copy/paste the ENTIRE COLUMN of the 'imported' data to the target field of the main table by clicking in the column headers appropriately.

No need to save the query.

Q.E.D.

I actually had eight fields to this to in the main table and was able to complete the entire process in about 90 minutes from start to finish.