Shift Data

conleyle

New Member
Joined
May 6, 2015
Messages
29
I am not sure if this is even possible, but here goes.

I have a table that I will be making updates in with an Append query. There is a comment field, and there will likely be a new comment every time the field is updated (which could be multiple times). I would like to keep the previous comments in there somehow. I'm thinking of having multiple comment field (comments 1, comments 2...) and whenever I run the make table having it update the information with the current info, but shifting what is currently in [comments 2] into [comments 3], and [comments 1] into [comments 2], and the new comment going into [comments 1]. Is this possible? I'm trying to think through in my head how to make this work and I am getting my own syntax error in my mind :)
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,956
Which is it, an update query or an Append query?
It cant be both like you stated.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
You need tblComments with a field that uses the original table ID field to link the comment with those records. If you do not have that in the existing table, you will have to add it and run an Update on the comments table (ensuring you do this correctly) before you delete the comments field in the original table. You didn't provide any table or field names being used, so I made up my own for you to relate to. In the example, record 1 in first table has two comments in the second table. If needed, a date field goes in tblCmnts, not the other, because it is related to the comment. If you want to show who entered the comment, the proper technique is to log the ID from tblUser (not their name) to maintain normalization.

tblOriginal
ID
DATA1
DATA2
1
adsfasdf
sdfvbxvx
2
afghtrh
fgssgfd

<tbody>
</tbody>

tblCmnts
ID
OrigID
Comment
1
1
sdfgsdf
2
1
dhmkgkgjk

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top