Solved

Conditional Update - a challenging SQL Query

Posted on 2004-08-26
15
212 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

758 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

22 Experts available now in Live!

Get 1:1 Help Now