Solved

Conditional Update - a challenging SQL Query

Posted on 2004-08-26
15
217 Views
Last Modified: 2008-03-04
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
0
Comment
Question by:sciber_dude
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11906935
How does Eligibility relate to Status?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
ID: 11907098
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11907457
Is it a one to one relationship through Id field?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 44

Expert Comment

by:GRayL
ID: 11907646
That was my guess.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 11907726
So, next question... Why not everything in just one table?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11907744
I suggest we wait for the questioner to respond before making any assumptions.
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11908006
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
 
LVL 11

Author Comment

by:sciber_dude
ID: 11908021
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
 
LVL 11

Author Comment

by:sciber_dude
ID: 11908026
And I am sorry to have left this piece of information out..

Status.ID = Eligibility.ID

:) SD
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 11908667
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
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 125 total points
ID: 11908729
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
 
LVL 16

Assisted Solution

by:Nestorio
Nestorio earned 125 total points
ID: 11908756
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11908812
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
 
LVL 11

Author Comment

by:sciber_dude
ID: 12044760
Thanks experts!

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

:) SD
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12045528
Thanks. Glad I could help.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question