Shift Data

[conleyle]

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

 

[ranman256]

Which is it, an update query or an Append query?
It cant be both like you stated.

 

[conleyle]

Update. I thought I corrected it.

 

[Micron]

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>