Append query issues

0nyx175

Registered User.
Local time
Yesterday, 16:44
Joined
Aug 7, 2012
Messages
26
Hi guys,



I’m having trouble with append queries. I have the following tables:



Customer Number
First name
Surname
DOB
Post-code

A company will occasionally send us large updates to post-codes which will show records and new data. It’s not as simple as replacing the entire file, they will only send us a few amendments and not the entire database I need to append this data into the existing table so for example I have


Customer Number: 123
First name: Bob
Surname: Smith
DOB: 21-01-2001
Post-code: MK408EE

The company would send me through


Customer Number: 123
First name: Bob
Surname: Smith
DOB: 21-01-2001
Post-code: MK402LL

I am having no luck appending this as my query seems to simply over-write all of the data in the post-code table leaving blanks– how can I get the database to look at the customer number and appropriate, change the post-code - can anyone suggest proper structure?


Thanks in advance

 
Its not an Append Query you need it is an UPDATE query.
 
Thanks for your response Trevor G - i'm in the same position stuck with update queries - can you suggest a structure
 
Microsoft Help will give you a clear picture, but lets try this.

Create a query using the 2 tables, then join the tables together using the ID field (Click drag across), Select to use the Update Query then, double click the Post Code Field that you want to update, and then It should show you the Field Name and the Table name, next you go into the Update To row and type in [TableNameThatHasUpdatedData]![Post Code]

Then if you change the view of the query it will show you empty boxes but gives you the correct amount of records it will update, then go back into design view and click the Run button you will be prompted to state how many records it will update then click yes and it should work.

Sample SQL VIEW code of such an Update.

UPDATE Reservations INNER JOIN tblTemp ON Reservations.ID = tblTemp.ID SET Reservations.[Post Code] = [tbltemp]![post code];

Please MAKE SURE YOU PRACTISE ON A COPY OF THE DATABASE.
 

Users who are viewing this thread

Back
Top Bottom