Append additional information to a field

[Lorlai]

I have a summary field that contains data. Sometimes I have additional information that needs to be attached with the current data. For instance:



Old Data: Product A is very nice. It has a red exterior and a blue interior. People of all ages should enjoy this product. It is estimated that consumers will pay $500-$600 for this product.

New Data: 01/01: Children 0-12 years of age find this product to be especially amusing. New marketing should be pushed towards this age group.

I want this to all be combined, like the following:

Product A is very nice. It has a red exterior and a blue interior. People of all ages should enjoy this product. It is estimated that consumers will pay $500-$600 for this product.

01/01: Children 0-12 years of age find this product to be especially amusing. New marketing should be pushed towards this age group.

Is this possible to do in an append query? to keep the old data and just append this current data along with it?

Thank you!

[pbaldy]

You would want an update query, not an append query. An append query adds a new record, an update query modifies an existing record. It can do what you're asking.

[Lorlai]

Thank you for getting back to me PBaldy, I appreciate it.

I am not certain I understand how to use an update statement in a way that will keep the old data and only add new data. It was my understanding that when you use an update statement, the previous data is removed and the new data is placed. If my new data contained none of the old data, how could I go about adding this new data while still keeping the old?


Thank you!

[pbaldy]

Any number of ways, but since you asked about queries, an update query would look like:

UPDATE TableName
SET FieldName = FieldName & " More info here"

The key in any method is to concatenate the existing value with the new.

[Lorlai]

Thank you! I can't believe that never occurred to me; that's basic programming...

One more question, if you don't mind: is it possible to compare the data to see if any new data has been added? Like if you had abcdefghij in the original data, and your new data contained the exact same thing, but there are other records that need updating? I don't want to have abcdefghjijabcdefghij.

[pbaldy]

Perhaps, though it might help to know your specifics. Generally I'd expect to be updating a specific record, so in a query I'd be adding a criteria:

UPDATE TableName
SET FieldName = FieldName & " More info here"
WHERE KeyFieldName = 123

[Lorlai]

I have a large report that is given to me in an excel file. I have imported this file into access and created the appropriate field names. This report is sent out every week, and there are small changes that are made throughout the report. I have a macro that looks over the report and makes updates throughout. This works for all the fields except for the description fields, which need to keep there old data and attach the new data, which may not match the old data. This will only happen for a few products out of the thousand that are in the report, so I have no way to set the specific criteria.

Any expert thoughts on this?