Select Case in Update Query

[hapnendad]

In the question statement below Field names are in [] and variables are in (). All fields referenced are in what I have named the ‘PAR’ Table.

Using MS Access 2003, I am working on a project management database and have reached the point where I need to develop a way for managers to re-assign responsibility for all active projects to another of his/her resources.
The Field names for the different managers associated with each project are:
[EngMgr] [ProdProcMgr] [MfgMgr] [QAMgr] [MatMgr]

When a new project is entered each manager assigns a resource to a related Resource field as follows:
[EngMgr] assigns a person to Resource field [ProjMgr]
[ProdProcMgr] assigns a person to Resource field [ProdProcRep]
[MfgMgr] assigns a person to Resource field [MfgRep]
[QAMgr] assigns a person to Resource field [QARep]
and [MatMgr] assigns a person to Resource field to [MatRep].

There are multiple manager names possible for each Manager Field and multiple resource names possible for each Resource Field

I have a routine that replaces the resource for a single project which works fine but now I need to replace the assigned resource in every project where the vacating resource appears.

The problem I have is this: An assigned resource leaves the company and all active projects assigned to this resource must be reassigned.

I am capturing the username of the database user (CurrentUser) who is attempting to re-assign a resource for all projects and comparing it against a table containing all manager names so I can restrict this function to allow managers to re-assign only his/her resources. Once I verify the user is a manager, I prompt for the name of the resource to be replaced (OldResource) and the resource to replace with (NewResource).

Once I have (OldResource) and (NewResource) captured I need to run an update query or some other database wizardry on the PAR Table that will do the following:

Update [ProjMgr] to (NewResource) Where [EngMgr] = (CurrentUser)
Or Update [ProdProcRep] to (NewResource) Where [ProdProcMgr] = (CurrentUser)
Or Update [MfgRep] to (NewResource) Where [MfgMgr] = (CurrentUser)
Or Update [QARep] to (NewResource) Where [QAMgr] = (CurrentUser)
Or Update [MatRep] to (NewResource) Where [MatMgr] = (CurrentUser)

I can do this with 5 sequential update queries as shown below four that would do nothing and one that would (when criteria are met), just looking to expand my apparent lack of knowledge about queries.

I was hoping to learn more about using select case statements inside an SQL statement. So I could have one query to execute no matter who is using the database and so I don’t have to loop through a recordset. Also, I am just looking to expand my apparent lack of knowledge about queries.

These five statements do what I need but, would like to know if it can be done in one. Note: Line continuation characters are missing and FLD = CurrentUser

CurrentDb.Execute "UPDATE PAR SET PAR.ProjMgr = '" & NewResource & "' 
WHERE PAR.EngMgr='" & FLD & "' AND PAR.ProjMgr='" & OldResource & "';"
CurrentDb.Execute "UPDATE PAR SET PAR.ProdProcRep = '" & NewResource & "' 
WHERE PAR.ProdProcMgr='" & FLD & "' AND PAR.ProdProcRep='" & OldResource & "';"
CurrentDb.Execute "UPDATE PAR SET PAR.MfgRep = '" & NewResource & "' 
WHERE PAR.MfgMgr='" & FLD & "' AND PAR.MfgRep='" & OldResource & "';"
CurrentDb.Execute "UPDATE PAR SET PAR.QARep = '" & NewResource & "' 
WHERE PAR.QAMgr='" & FLD & "' AND PAR.QARep='" & OldResource & "';"
CurrentDb.Execute "UPDATE PAR SET PAR.MatRep = '" & NewResource & "' 
WHERE PAR.MatMgr='" & FLD & "' AND PAR.MatRep='" & OldResource & "';"
 

Can anyone help or is this unanswerable without further info or just impossible?

Thanks in advance for any guidance/help,
Jeff

[PianoMan64]

Hey Jeff,

What I would recommend for you, would be to put all those update queries into a large Select Case Statement and depending on which user is moving what, you would know what Update query to execute.

That would simplify things a great deal and run a whole lot faster.

Are you using MS Access as a backend, or are there some other backend database like MS SQL, or Oracle?

The other question that I had is, are you taking advantage of the Workgroup option within MS Access or are you using MS Access 2007 ACCDB files?

Let me know,

Joe P.

[NeoPa]

Sometimes it's necessary to design a query to do it all in one go. At others, it is better and simpler to allow the code (very flexible) to create a query (some SQL) on the fly which, because it has the selections already available to it, is targetted directly at the specific solution required.

If in your case, it is necessary to use a more complicated update query, then using IIf() may work for you. Care must be taken though, as it won't update if Access (Jet) decides the query is not updatable (Reasons for a Query to be Non-Updatable).

[hapnendad]

In response to PianoMan and NeoPa

Joe,

I am mainly working on functionality at this point. I have created all the static and data tables in a stand alone database for development with plans of using Access as the back-end database once I have added all the bells and whistles and get them all working properly.

I can see how a select case will handle the queries, something on the order of:

Select Case FLD
Case EngMgr1, EngMgr2, EngMgr3
Update query here for ProjMgr
Case ProdProcMgr1, ProdProcMgr2, ProdProcMgr3
Update query here for ProdProcRep
Case ETC...
End Select Which will work fine and I only run one query.


I am not using the workgroup option. What I do is to capture the windows log-in username of the computer using the database and lock out functionality if the user is not found in one of two tables using a Dlookup command.

The reason I am doing things this way is because I learn on the fly at home and do not have access to the company network from home. And, they pay me to do mechanical engineering work.


Neopa,

I read the info about non-updatable queries but don’t think that applies here as I want only to update a data table.

But, your reply got me to thinking that I could add a couple of fields to my ManagerResource table that would contain the field name associated with a particular manager and the field name that will hold his/her resource assignment. I could then query the Manager Resource table based on the current user and pull out the two field names associated with that manager and reference the query fields in the Update query reducing it to one query. This will be a little more work than a big select case statement that would still have 5 query statements so I will probably try both just to learn.

By the way, I found this site: Using CASE Expressions with information on the Case statement but did not know If Access SQL will allow it’s use. Will have to explore

I have a couple of ways to precede, Thanks for your responses.

Please consider this question answered.

Thanks again,

Jeff

[NeoPa]

Thanks for the full response Jeff.

CASE statements (as found in MS SQL Server's T-SQL) are not available in Access's Jet SQL I'm fairly sure.

Anyway, welcome to Bytes! and come back for more if / when you feel the need.