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.

 

[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 !

 

[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 ?

 

[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 !