Solved

Conditional Update - a challenging SQL Query

Posted on 2004-08-26
15
214 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now