Append Query to add column?

[vjorden]

Hi Everyone!



I am a self taught Access user, I know a little VBA but not much.

Here is my issue: I currently have a database populated with thousands of records and now all of a sudden the folks I work for want to add in another column of data. The primary key main table are SSN. I have an excel spreadsheet with this new column of data and the related SSN.

I do not want to have to data entry each of these values, so how can I get the data for this new column into the associated rows?

My first thought was an append query...but I just can't seem to get it to work. Any ideas on how best to get this done?

Thanks!

VJ

[boblarson]

Append queries do not add columns to tables. It is very, very rare to have to add a column to a table in a properly normalized database. So, that might be an indication that yours is not correctly designed in the first place. But, be that as it may, you would need to use an ALTER TABLE SQL statement.

So, anyway, you should just open the table in design view and add the extra column, import the data to a temporary table and then use an UPDATE query to update the existing data.

[vjorden]

Thanks Bob! I understand it is rare, they were tracking the info in a separate department and have now decided they want me to start tracking the info. If only they could have seen the light of centralizing information to begin with!

I actually tried an update query as well with little success. I imported the info from the excel spreadsheet into a table, added the new column to the main table and set up the update query but am having no luck...the main table is not showing the new info even after I run the update query.

Any tips or things I should make sure I am doing to get it run correctly?

Thanks for your help...I appreciate it!

VJ

[boblarson]

So, first off you would

1. Add the new field into the main table (setting the correct datatype as well).
2. Then you would import the spreadsheet and tell it to use a NEW TABLE.
3. Then you would use an Update Query to update the existing records in the field in the main table by using an Update query like this (changing the names to your actual names)

UPDATE MainTableNameHere 
INNER JOIN ImportedTableNameHere 
ON MainTableNameHere.IDFieldNameHere=ImportedTableNameHere.IDFieldNameHere 
SET MainTableNameHere.FieldToUpdateNameHere= [ImportedTableNameHere].[FieldWithNewDataHere]
WHERE (((ImportedTableNameHere.IDFieldNameHere)=[MainTableNameHere].[IDFieldNameHere]));