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