Import data after the fact

[skydiver]

Access 2003. I've created three additional Field Names to my main table after the fact. I currently have 1800 records with a numbered format for each field. We recently began recording data for the new fields. Up to this point that field was non-existent, therefore the recorded number should be a zero (up to this point). After adding the new fields to the table, the 1800 fields are left blank. How can I get zeros in there without typing them in manually? Is there a copy/paste trick? Can I create an outside excel table then import into those fields??? Please be as thorough as possible. I'm a novice. Help!

 

[boblarson]

Just use an UPDATE query.

UPDATE TableNameHere Set FieldNameHere = 0 WHERE FieldNameHere Is Null

Change TableNameHere and FieldNameHere to your actual table and field names.

 

[skydiver]

Thanks for you fast response Bob! Okay, I'm creating a new query from scratch. Can you walk me through on how to set up this query?

 

[boblarson]

Thanks for you fast response Bob! Okay, I'm creating a new query from scratch. Can you walk me through on how to set up this query?

just click NEW QUERY > DESIGN VIEW > and then just close the dialog for selecting the tables. Then click on the view > SQL View and paste what I posted and then just change the table and field names. You should start with one field and then after that works, change the field name again to the second field, run it again, then the third.

 

[skydiver]

It's not liking me Bob. In the SQL view it starts with: SELECT;
I've pasted the first line of your response replacing my TABLEname and FIELDname, but it keeps telling me:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the puncuation is incorrect."

 

[boblarson]

Did you overwrite the SELECT; part? If not, you should have.

 

[skydiver]

Here is my SQL statement:

UPDATE stats Set p-enf = 0 WHERE p-enf Is Null

It's telling me: "Syntax error in UPDATE statement"

 

[boblarson]

So that is a lesson on why it is bad to include special characters (-) or spaces in field or object names. You then need square brackets:

UPDATE stats Set [p-enf] = 0 WHERE [p-enf] Is Null

 

[skydiver]

Lesson learned. I've pasted exactly what you sent, but still the syntax error.

 

[boblarson]

So stats is the name of the table and p-enf is the field to be updated? Can you upload a screenshot of your update query in SQL View?

 

[skydiver]

Again Bob...just wanted to thank you for your patience and help. The statement worked after removing the asterisks from the brackets. Big relief! Thanks a mil Bob!!!