Conditional Update - a challenging SQL Query

Need help with a query to update a field in a table.

-----------------------
TableName: Status
-----------------------
ID            - Text
GROUPS   - Number
 (possible values are: 1, 2, 3, 99)
NewGroups - Number                                   << This field needs to be updated. (see below)
-----------------------
TableName: Eligibility
-----------------------
ID                 - Text
CompAccess  - Number
 (possible values are: 1, 2, 3, 99)

~~~~~~~~~~~
Rules of Update:-
~~~~~~~~~~~
1. Given an Status.ID
2. If (Status.Groups = 1 OR Status.Groups = 2 OR Status.Groups = 99) -> Update Status.NewGroups = Status.Groups
3. If (Status.Groups = 3) then ...
         If (Eligibility.CompAccess = 1) -> Update Status.NewGroups = 4
         If (Eligibility.CompAccess = 2 OR Eligibility.CompAccess = 3) -> Update Status.NewGroups = 5
         If (Eligibility.CompAccess = 99) -> Update Status.NewGroups = 9

I would have done this using an elaborate ASP script. But i know it can be done using Access SQL statement.

Helpppppppppppp pleaseeeeeeeeeeee!!!

:) SD
LVL 11
sciber_dudeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
GRayLConnect With a Mentor Commented:
UPDATE Status INNER JOIN Elibiblity on Status.ID = Eligibility.ID
Set Status.NewGroups =
IIF(Status.Groups = 1 OR Status.Groups = 2 OR Status.Groups = 99,Status.Groups,
IIF(Eligibility.CompAccess = 1, 4,
IIF(Eligibility.CompAccess = 2 OR Eligibility.CompAccess = 3, 5,
IIF(Eligibility.CompAccess = 99,9,0))))
0
 
shanesuebsahakarnCommented:
How does Eligibility relate to Status?
0
 
NestorioCommented:
Is it a one to one relationship through Id field?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Take the quiz
 
GRayLCommented:
That was my guess.
0
 
NestorioCommented:
So, next question... Why not everything in just one table?
0
 
shanesuebsahakarnCommented:
I suggest we wait for the questioner to respond before making any assumptions.
0
 
sciber_dudeAuthor Commented:
well. yes it is one to one relationship.

Well, I cant have it one table because ...
There are are 1500 variables for each record and we need to split them into tables based on semantic themes.
And its an ACCESS database - It wont let me have more than 255 variables per table.

:) SD
0
 
sciber_dudeAuthor Commented:
wow GRayL - That is some SQL statement. Let me test it out and i will get back to you.

Btw, congratulations on becoming a Guru. I got 11,770 more to go in the ASP TA ;-)

:) SD
0
 
sciber_dudeAuthor Commented:
And I am sorry to have left this piece of information out..

Status.ID = Eligibility.ID

:) SD
0
 
NestorioCommented:
Update Status S, Eligibility E
Set S.NewGroups = Switch(S.Groups = 1 or S.Groups = 2 or S.Groups = 99, S.Groups, S.Groups = 3 and E.CompAccess = 1, 4, S.Groups = 3 and (E.CompAccess = 1 or E.CompAccess = 3), 5, S.Groups = 3 and E.CompAccess = 99, 9)
Where S.Id = E.Id
0
 
shanesuebsahakarnConnect With a Mentor Commented:
You can simplify it somewhat:

UPDATE Status As S INNER JOIN Eligibility As E ON S.ID=E.ID SET S.NewGroups=Switch(S.Groups<>3,S.Groups,E.CompAccess=1,4,E.CompAccess=99,5,E.CompAccess<4,5)
0
 
NestorioConnect With a Mentor Commented:
Correction:

Update Status S, Eligibility E
Set S.NewGroups = Switch(S.Groups = 1 or S.Groups = 2 or S.Groups = 99, S.Groups, S.Groups = 3 and E.CompAccess = 1, 4, S.Groups = 3 and (E.CompAccess = 2 or E.CompAccess = 3), 5, S.Groups = 3 and E.CompAccess = 99, 9)
Where S.Id = E.Id

0
 
NestorioCommented:
I didn't use a simplified expression like S.Groups <> 3 because I don't know if it could appear values for S.Groups other than 1, 2, 3, 99.
0
 
sciber_dudeAuthor Commented:
Thanks experts!

Sorry I wasnt around to close this question. had to go to SF to attend a conference.

:) SD
0
 
GRayLCommented:
Thanks. Glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.