Solved

Conditional Update - a challenging SQL Query

Posted on 2004-08-26

Microsoft Access

3 solutions

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

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

Join The Community

15 Comments

LVL 41

Expert Comment

by:shanesuebsahakarn

ID: 119069352004-08-26

How does Eligibility relate to Status?

LVL 44

Accepted Solution

by:GRayL

GRayL earned 250 total points

ID: 119070982004-08-26

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))))

LVL 16

Expert Comment

by:Nestorio

ID: 119074572004-08-26

Is it a one to one relationship through Id field?

LVL 44

Expert Comment

by:GRayL

ID: 119076462004-08-26

That was my guess.

LVL 16

Expert Comment

by:Nestorio

ID: 119077262004-08-26

So, next question... Why not everything in just one table?

LVL 41

Expert Comment

by:shanesuebsahakarn

ID: 119077442004-08-26

I suggest we wait for the questioner to respond before making any assumptions.

LVL 11

Author Comment

by:sciber_dude

ID: 119080062004-08-26

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

LVL 11

Author Comment

by:sciber_dude

ID: 119080212004-08-26

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

LVL 11

Author Comment

by:sciber_dude

ID: 119080262004-08-26

And I am sorry to have left this piece of information out..

Status.ID = Eligibility.ID

:) SD

LVL 16

Expert Comment

by:Nestorio

ID: 119086672004-08-26

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

LVL 41

Assisted Solution

by:shanesuebsahakarn

shanesuebsahakarn earned 125 total points

ID: 119087292004-08-26

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)

LVL 16

Assisted Solution

by:Nestorio

Nestorio earned 125 total points

ID: 119087562004-08-26

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

LVL 16

Expert Comment

by:Nestorio

ID: 119088122004-08-26

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.

LVL 11

Author Comment

by:sciber_dude

ID: 120447602004-09-13

Thanks experts!

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

:) SD

LVL 44

Expert Comment

by:GRayL

ID: 120455282004-09-13

Thanks. Glad I could help.

Featured Post

Question has a verified solution.

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

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

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.

Delivering a Word Template for Use in Merging Data from Access

Article by: Helen

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…

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

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…