Clash Royale CLAN TAG#URR8PPP

Update column based on another column in table - MS Access

I have a table, titled Game, structured like this:

[Game_Name] [Publisher]


The data in the table would look like this:

[Game_Name] [Publisher]
1
3
1
2
2
1
3


And Game_Name will be blank. With this, I want to update the value of Game_Name based on the value of the Publisher column.

How this should work:

If the Publisher = 1 -> for each of the instances (rows) where the Publisher column is 1, the Game_Name will be GM-1,...GM-n. So, in this example, there are 3 instances where Publisher is 1. So the values would be GM-1, GM-2, GM-3.

If the publisher = 2, similar to the above, expect the format is 1-1,...n-n. So, since we have 2 instances where Publisher is 2, the values for Game_Name would be GM-1-1, GM-2-2.

Lastly, if Publisher = 3, the values for Game_Name would be 1-1-1,...n-n-n. There are three instances here so the values for Game_Name would be GM-1-1-1, GM-2-2-2, GM-3-3-3.

This is my current approach

UPDATE Game
SET Game_Name =
SWITCH (Publisher = 1, "GM-"&[1+1],
Publisher = 2, "GM-"&[1+1]&"-"&[1+1],
Publisher = 3, "GM-"&[1+1]&"-"&[1+1]&"-"&[1+1])


I am having trouble keeping track of the incrementing value and when to reset. Also, whether this should be a Macro or not.

Any help is appreciated.

1 Answer
1

Consider:

SELECT ID, Publisher, "GM-" & Choose([Publisher],[GrpSeq],[GrpSeq] & "-" & [GrpSeq],[GrpSeq] & "-" & [GrpSeq] & "-" & [GrpSeq]) AS Game_Name
FROM (SELECT ID, Publisher, DCount("*","Game","Publisher = " & [Publisher] & " AND ID <" & [ID])+1 AS GrpSeq FROM Game) AS Q1;


Be aware this will likely perform slowly with very large dataset. In which case a VBA approach would probably be preferable. If there are significantly more than 3 Publisher values, VBA approach may be only option.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.