How do i append to an existing table without overwriting data

[Jaspal]

Hi,

I have a table that contains a notes field. What i would like to do is to append to this field in table with some more data (notes), however i dont want to over write the existing data. I would just like to add to it.

the current notes are added through the access front end, but in this instance i have a particular note that needs to be added to various enteries without overwriting existing data. I know this is to be done via an append query, any help would be great

Thanks

 

[zestygirl]

Without knowing whether the additional notes are in a table, or, is simply a short statement that you want to add to several exisiting entries, I can tell you that a query would be run in either case.

If you have a statement to add to several entries, write a query that filters those entries first (i.e. - [Notes] like "X") then, create a calculated field to combine into this field additional information, something like this:

Notes2=([Notes]&"X")....

Hope this helps.

 

[TheEvilBankers]

You can make a form that lets you pull up old records to update. And you just add what you want to the notes field. We use a number we assign to a customer that is unique to them and our department and we can use that to pull up call records to update.

K

 

[Jaspal]

The form idea is something that i have considered but there is alot of records that need their notes field updating, the additional note that i am adding is the same so i have say "this is the note" to add to the notes fields of certain records in the table without overwriting. I have created a qery that pulls back all the records that i want this to happen to. just need to add the note now !

 

[Mile-O]

Not an APPEND Query; an UPDATE query.

UPDATE MyTable SET MyField = MyField & "New Stuff" WHERE MyCriteria = True;

 

[Jaspal]

ive tried doing that with the UPDATE query but it is over righting the data that is already stored in the Notes field

 

[Mile-O]

Post your SQL

 

[Jaspal]

UPDATE Jedds_Data INNER JOIN tbl_Payment ON Jedds_Data.[Gabem reference Number] = tbl_Payment.GabRef SET Jedds_Data.NOTES = Jedds_Data.NOTES And "This is a test";

This query shows that two tables are used to extract the relevant crieria from the main table "Jedds_Data" this is the table that contains the notes field that i want to update but not overwrite

thanks

 

[Mile-O]

The syntax is ampersand (&); NOT And

 

[Jaspal]

sorry to be a pest, ive changed the syntax to a ampersand, but nothing is hapening its not updating at all. Is there another was for this to be done. Or am i still doing something wrong !

 

[neileg]

Is the join returning any data, 'cos the syntax is OK. Try running the query as a select query and see if it returns any records. If it doesn't, then there's nothing to update.

 

[Jaspal]

i started by running it as a select query and it returned the values that i wanted, exactly. then i ran it as an update query as advised above but is did not make the changes that i wanted it to.

 

[Pat Hartman]

It is REALLY hard to debug syntax when you don't post it. Obviously you don't understand the syntax Mile suggested so rather than talking about what you did, post the query.

You can make a form that lets you pull up old records to update

- This is a really poor idea. Forms are intended to work interactively a record at a time. If you have something that needs to be done to lots of records, an update query or DAO/ADO is the way to go. An update query should be your first choice since it is the most efficient.

 

[Jaspal]

SELECT Jedds_Data.NOTES
FROM Jedds_Data INNER JOIN tbl_Payment ON Jedds_Data.[Gabem reference Number] = tbl_Payment.GabRef
WHERE (((Jedds_Data.NOTES)=[NOTES] & "This is a test to update the Notes"));


As requested this is the syntax that i am using, really need help on this one so far the help been given has been greatfuly accepted but just doesn't work !

 

[Pat Hartman]

You have posted a select query. I thought you wanted to update your notes field.

This is an example of an update query that updates the Notes field in Jedds_Data. I included the join to tbl_Payment because I assumed that that was the table that contained the data you wanted to append to the existing note. I included a separator phrase. Change to whatever you want.

Update Jedds_Data INNER JOIN tbl_Payment ON Jedds_Data.[Gabem reference Number] = tbl_Payment.GabRef Set Jedds_Data.NOTES
= Jedds_Data.NOTES & " - new note - " & tbl_Payment.Notes;

 

[Jaspal]

Hi pat,

okay the code you gave worked but my problem is that is only updates the first record in the table, where as need it to update well over a a thousand records... im thinking along the lines of recordsets and using move next, but how is this applied to a query ?

 

[Pat Hartman]

If it updates only the first record, it is because only the first record has a match in the second table.

There is absolutely no need to write a code loop. The update query will work just fine as long as you can identify where the update is coming from. In your first example you joined two tables but you appended a literal string. Do you want to append a literal string or do you want to append data from a different table?

 

[Jaspal]

im joining two tables in order to retreive all the records that i want basically i have the payments table and in that is has all the employee referneces that need their notes field updating, however jedds_data has the field for the Notes that need to be updated. Hence i join the two tables to match the reference number and hence return all the noted fields that i require. In a slect query this works fine and i get all the recrds that i want (2000+). Then i converted the query in to an update query and applied the syntax that you advised but only the first field was updated and the rest of the records were untouched were as they should have been updated too. i know this from looking at the datasheet view after the query had run !

 

[Pat Hartman]

You are doing something wrong and I haven't a clue what it is. If you get the correct recordset when you join the two tables, the update query should update all the records in that set.

Rebuild the query.