Update Table Field Based on another Fields Data (1 Viewer)

Number11

Member
Local time
Today, 08:36
Joined
Jan 29, 2020
Messages
607
Hi i need to update a table field based on the results of another field within the same table

I have a field call Short_ID the data within this Field can be either

K
ET
CR
N
NN
S9
S6
NSS

Need populate into an empty field the results

so If K put into Field Called "Short_ID_Group" as P
and ET should be the same but CR would be "C"
 

plog

Banishment Pending
Local time
Today, 03:36
Joined
May 11, 2011
Messages
11,613
1. What is this database for? Without using database jargon tell us the real life things this database models.

2. How come you don't already have Short_ID_Group data in this table?

3. Is this a one time process or will this be an ongoing process? In the future will data be put into Short_ID and then you will need to add Short_ID_Group?

4. What about the other ID values? You gave us the grouping for 3 values, but your example has 5 more that you didn't account for.
 

Number11

Member
Local time
Today, 08:36
Joined
Jan 29, 2020
Messages
607
1. need for a stock take
2. new database tables importing from other database
3. no the data will never be entered into the new field "Short_ID_Group"
4. same was just for 3 to explain and but will have to do for all in the list

Thanks
 

plog

Banishment Pending
Local time
Today, 03:36
Joined
May 11, 2011
Messages
11,613
3. no the data will never be entered into the new field "Short_ID_Group"

My question is will this process that you are doing now--converting Short_ID into Short_ID_Group a one time thing (right now), or is will it be something you need to do regularly?

In a month or two will there be new data in your table that you will have to perform this process on again?
 

Number11

Member
Local time
Today, 08:36
Joined
Jan 29, 2020
Messages
607
My question is will this process that you are doing now--converting Short_ID into Short_ID_Group a one time thing (right now), or is will it be something you need to do regularly?

In a month or two will there be new data in your table that you will have to perform this process on again?
sorry misunderstood 1 time job
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:36
Joined
Jan 23, 2006
Messages
15,364
There must be some logic behind this
so If K put into Field Called "Short_ID_Group" as P
and ET should be the same but CR would be "C"
I think readers need to know a little more context to provide more focused responses.

It seems a small table with the corresponding value/new goup and a query would be sufficient -- but we need details.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:36
Joined
Feb 19, 2002
Messages
42,981
Your explanation doesn't give us a formula. Are you saying that you can somehow "calculate" the second value from the first? I don't see how. Therefore, what you need is a translation table. All values of a and the result you want as b. So, if the value doesn't change, then a and b have the same value.
 

plog

Banishment Pending
Local time
Today, 03:36
Joined
May 11, 2011
Messages
11,613
You should make a table for every value you need:

tblCodes
ShortID, GroupID
K, P
ET, P
CR, C
...

Then you bring tblCodes and your existing table into a query, link them via ShortID fields, bring down the GroupID field from the existing table, change the Query to an UPDATE query and then update the existing table's GroupID value to the GroupID value of tblCodes.
 

Users who are viewing this thread

Top Bottom