Solved

Conditional Update - a challenging SQL Query

Posted on 2004-08-26
15
219 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

617 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