CODEWITHSUNDEEP
ms-access
user411103
user411103

Reputation:

Move data from field to field in MS Access

In MS Access I have a table like this:

TABLE USER
Id (int)
Status (text)
OldStatus (text)

I would like to create a MS Access Query which, when executed, moves the content of Status field to the top of OldStatus, and adds the current year. For example:

Before execution:

ID    STATUS       OLDSTATUS
1     Very good    [2010] Excelent
                   [2009] Very bad

2     Excelent     [2010] Bad
                   [2009] Good

After execution:

ID    STATUS       OLDSTATUS
1                  [2011] Very good
                   [2010] Excelent
                   [2009] Very bad

2                  [2011] Excelent
                   [2010] Bad
                   [2009] Good

How can I do that? THANK YOU!!

Upvotes: 0

Views: 1207

Answers (1)

JeffO
JeffO

Reputation: 8053

Didn't know the name of your table, so you'll have to substitute. The goal here is to maintain the old status while concatenating the current status. I used a space (" ") to put between, but you can use any character you want.

Update Status_Table
Set OLDSTATUS = OLDSTATUS & " " & STATUS

For what it is worth, there is a better way. Create a StatusHistory table:

StatusHistoryID
, StatusID
, Status
, Status_Date

You would have more flexibility here. Reports could be set for a date range or a particular year. You're table would be very hard to run a report on the status for 2010.

Upvotes: 1

Related Questions