|
| |
Update one field in a table based on another field in a different table
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
 |
Update one field in a table based on another field in a... - 1/3/2003 18:15:15
I am not sure if this is possible:
I have a web front end for an MS-Access database with a master client table, a client_status table, a (WIP)WorkInProgress table etc....
I want my users to be able to go into the WIP form, change a field, and when they click submit, I want a query to run that will update that table, then run another query based on the content in the field and update the STATUS field in the client_status table. the query needs to check for multiple values and update the status based on what' s in there.
Is there an IF,THEN,ELSE statement that I can put into the DRW Custom Query box that would accomplish this?
Thanks,
CW
|
|
|
|
rdouglass
Posts: 9187
From: Biddeford, ME USA
Status: offline
|
RE: Update one field in a table based on another field ... - 1/4/2003 15:18:02
If all the information to be posted comes from a form, you could just ' daisy-chain' the update scripts one after the other on the same page. If your second script requires info from another query (instead of just the form), you could run the first script on 1 page, get your results, then pass results to second page that has the second script.
Does that help??
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Update one field in a table based on another field ... - 1/5/2003 4:11:07
The question is can I do this using an if then else type of statement in the DRW?
I want to do something like:
If table1.field1=True then update table2.field with " TEXT" else leave it alone.
This command would need to check against mutliple possible values from different fields.
Example Scenario:
table1.field1=True or False
table1.field2=date
table1.field3=True or False
etc...
In my query statement, if field 1 is true, then that means we have done something and need to set the status. However, if field1 is True and now Field 2 is also true, then the status needs to change because we have now progressed to another step in our process.
Right now, I am having the users change the status manually, but I would like the status to be driven based on activities elsewhere in the database so they do not have to manually change it.
| |
|
|
|
rdouglass
Posts: 9187
From: Biddeford, ME USA
Status: offline
|
RE: Update one field in a table based on another field ... - 1/6/2003 11:04:05
AFAIK you' ll not be able to do this in a single query. You' ll probably have to do the initial update. Then run the query (to check the True / false field), THEN do the IF...THEN.
I would tend to build the SQL ' on-the-fly' based on your form fields (hidden and otherwise). And I would try to make use of the hidden fields to check status. Then you could pass one of various SQL statements to the DRW. I have had pretty good success using this method. Maybe something like:
<% IF Request.Form(" myHiddenStatusField" ) = TRUE THEN
mySQL = (first SQL statement - update table1)
ELSE
mySQL = (second SQL statement - update table 2)
END IF
fp_sQry = mySQL
.....
Of course the DRW needs to be on a diet. Am I understanding this problem correctly and does that help at all?
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
