473,372 Members | 1,039 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Select Case in Update Query

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

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "UPDATE PAR SET PAR.ProjMgr = '" & NewResource & "' 
  2. WHERE PAR.EngMgr='" & FLD & "' AND PAR.ProjMgr='" & OldResource & "';"
  3. CurrentDb.Execute "UPDATE PAR SET PAR.ProdProcRep = '" & NewResource & "' 
  4. WHERE PAR.ProdProcMgr='" & FLD & "' AND PAR.ProdProcRep='" & OldResource & "';"
  5. CurrentDb.Execute "UPDATE PAR SET PAR.MfgRep = '" & NewResource & "' 
  6. WHERE PAR.MfgMgr='" & FLD & "' AND PAR.MfgRep='" & OldResource & "';"
  7. CurrentDb.Execute "UPDATE PAR SET PAR.QARep = '" & NewResource & "' 
  8. WHERE PAR.QAMgr='" & FLD & "' AND PAR.QARep='" & OldResource & "';"
  9. CurrentDb.Execute "UPDATE PAR SET PAR.MatRep = '" & NewResource & "' 
  10. 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,
Nov 17 '08 #1
4 7749
374 Expert 256MB
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.
Nov 17 '08 #2
32,556 Expert Mod 16PB
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).
Nov 17 '08 #3
In response to PianoMan and NeoPa


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.


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,

Nov 18 '08 #4
32,556 Expert Mod 16PB
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.
Nov 18 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

by: avinash | last post by:
hi myself avi i am developing one appliacaion in which i am using vb 6 as front end, adodb as database library and sql sever 7 as backend. i want to update one table for which i required data from...
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
by: 001 | last post by:
Hello, The select statement needs only 1 second to complete the query. But the update statement spends 30 minutes. Why? SELECT STATEMENT: declare @IDate smalldatetime select @IDate=col001...
by: parwal.sandeep | last post by:
Hello grp! i'm using INNODB tables which are using frequently . if i fire a SELECT query which fetch major part of table it usually take 10-20 seconds to complete. in mean time if any UPDATE...
by: eholz1 | last post by:
Hello PHP programmers. I had a brilliant idea on one of my pages that selects some data from my mysql database. I first set the page up to display some info and an image, just one item, with a...
by: RAG2007 | last post by:
I'm using the QueryDef and Execute method to update a record in my MySQL backend. Problem: When the Passthrough update query is defined using QueryDef, it becomes a select query, and I cannot use...
by: George | last post by:
I have just loaded Access 2007 and am having trouble creating a new database where I update my table from a query. I have done this numerous times in years past but am totally frustrated tryng to...
by: Chris Cowles | last post by:
I use an application that uses Oracle 8.1.7. All functions of the application are completed with calls to stored procedures. A data entry error occurred that caused thousands of records to be...
by: DuncanIdaho | last post by:
Hi Apologies if this is similar to a (very) recent post. I was wondering if it is possible to execute an update query that contains a select statement on an MS access 2000 database. I have...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.