Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Solved
Conditional Update - a challenging SQL Query
Posted on 2004-08-26
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
-----------------------
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
[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
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
IIF(Eligibility.CompAccess
IIF(Eligibility.CompAccess
Set Status.NewGroups =
IIF(Status.Groups = 1 OR Status.Groups = 2 OR Status.Groups = 99,Status.Groups,
IIF(Eligibility.CompAccess
IIF(Eligibility.CompAccess
IIF(Eligibility.CompAccess
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
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
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
Btw, congratulations on becoming a Guru. I got 11,770 more to go in the ASP TA ;-)
:) SD
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
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
You can simplify it somewhat:
UPDATE Status As S INNER JOIN Eligibility As E ON S.ID=E.ID SET S.NewGroups=Switch(S.Group
UPDATE Status As S INNER JOIN Eligibility As E ON S.ID=E.ID SET S.NewGroups=Switch(S.Group
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
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
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.
Thanks experts!
Sorry I wasnt around to close this question. had to go to SF to attend a conference.
:) SD
Sorry I wasnt around to close this question. had to go to SF to attend a conference.
:) SD
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses
Course of the Month5 days, 2 hours left to enroll
670 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.