Populating new field in existing database

jtp607

Registered User.
Local time
Today, 12:25
Joined
Oct 28, 2013
Messages
13
Hi, I am trying to add a new email field to my existing database of 900 employees. I have imported an excel worksheet of email addresses into a new table that has lastname, firstname, employee#, email. The primary key on the new table is email. The primary key on the employee table is employee#, and there is a new field titled email. The email field in both tables are text fields. In running an append query, it either adds 900 to 27000 additional fields of email only or it says cannot complete because of key code violations. On the join properties I have clicked on #3 all records from source to only equal fields of destination. Thanks for your help.
 
Sounds like you are trying to add email addresses that already exist in your destination or you have duplicate email addresses in your source.

What field are you joining these two tables on?
 
I have tried joining on the email field, and it doesn't work. In looking at the employee table the email field is empty. When an append query has run before, I went from 900 records to 1800. It just added 900 new records and did not append to the existing. Thanks for your time.
 
It just added 900 new records and did not append to the existing

I fear you are using terms incorrectly. When you append data you are adding new records to a table. If you want to change the data in existing records that is called 'updating'. Which is it you are trying to do. Can you post your database or an example?
 
I was given the email addresses after the fact of setting up the database, so therefore it is a new field with no existing data in an existing database. As I understand it, then I must then append the data, because updating requires the change of existing data, which in this case, there is none. I apologize that I am new here, and do not know how to post the database.
 
Your still losing me with your use of terms. Let's start from the beginning:

A record is a row in a table.
A field is essentially a column in a table.
When you append data you add a new record to a table.
When you update data you change the value in a field of a record.
If a record exists and one of its fields is blank, changing that value from blank to a value is called updating.

With those terms in mind, what exactly are you trying to do? Append or update?
 
Okay, then by those terms I am updating, because I am taking a blank field and adding data. I just tried an update query, and am obviously doing it wrong. Please help, thanks.
 
The common field between the table with the emails and the table without the emails is the employee number so that is the field you have to join on.

Add both tables to the QBE.
Draw the join line to connect the employee number fields.
Select email from the table you want to update (the one where the email is blank)
In the UpdateTo cell type the tablename.column name that is the source of the email.

As always, before you run any action query, make sure you have a backup of your database and/or tables should anything go wrong so you can recover.
 
Thanks Pat, just tried that. It failed due to key violations. In the update query, I am pointing from the email source table to the employee destination table, using the employee# pk. Joined properties #3 all records from source to equal fields in destination. It isn't working. I am uploading a screen shot, hope it helps. Thanks, again.
 

Attachments

  • ScreenShot for Help.JPG
    ScreenShot for Help.JPG
    87 KB · Views: 65
You need to choose option 1 for the join. You can only update if a match is found in BOTH tables.
 
Hi Pat,

Tried just what you said, used the option #1 in the join fields. Here is the screen shot. Thanks.
 

Attachments

  • Screen Shot #2.JPG
    Screen Shot #2.JPG
    94.2 KB · Views: 69
Pat, got it worked out. Thank you to you and Plog for all of your assistance.
 
Do you have a unique index on email? If so, that would explain the duplicates error. Do a "find duplicates" query on the source data to confirm that you have duplicates. If the duplicates query shows a bunch of records with "blank" emails, the email is actually a ZLS (zero length string) and you should convert it to null in the update by using the Trim() function.

Trim(yourtable.email)
 

Users who are viewing this thread

Back
Top Bottom