Oct 7 2009, 11:43 AM
Hello. Just a quick one I think.
I have googled for the answer and i cant find it anywhere.
How can I create a single update query that updates multiple tables, what is the format for it.
so far i have
UPDATE tablename1 SET tablename1.fieldname = new value
how would i put tablename2.fieldname into this?
THANK YOU for any help.
Oct 7 2009, 11:45 AM
The reason why you haven't found an answer is because I don't think you can update more than one table at a time. You might just have to settle with using two separate update queries to do what you need.
Just my 2 cents...
Oct 7 2009, 11:46 AM
"How can I create a single update query that updates multiple tables"
Not in Access I'm afraid.
Oct 7 2009, 04:58 PM
hummmm. Thank you for your help, incidently im from South Nottinghamshire, so not too far from Oadby.
On which case I think there must be another feature of access that I dont know about.....
table1 has a value field
table2 has a value field
I have 2 update queries. Each updates a different table.
The criteria on the updates is if t1.value + t2.value => 25 then update both tables by changing each value to 0.
I have a button in a form that would run one query after the other (with the t1 query first).
This works if t2.value => 25
However i have just discovered that by running one after the other after the first is run then t1.value = 0 so the update won't run on t2.value if its less that 25 (even though the combination of both may be more than 25) .
Any idea on how to solve this?
Again any help will be much appreciated
Oct 7 2009, 05:07 PM
I suppose I could, create another query to add the t1 id field to a new table and then have the WHERE criteria of the second update query = those id's only, then have another query to delete everything from this new table.
But this seems like a long way around....
1. What Entities do the 2 Tables represent?
2. How are these Entities related?
3. What are the "Values" in real life?
Oct 8 2009, 04:42 AM
o be more precise,
t1 is a product ordered table, so the value is the price*quantity
t2 is an accounts table where the value is the account initial balance (not all accounts start with 0)
Subsequently an account can have many products ordered
The first update query actually updates the paid field in product ordered table to paid (not to 0 as i said originally, sorry to make things more complicated).
The second update makes the initial balance 0.
The criteria in both my updates use is essentially when initial balance + the sum of (price*quantity) => 25 where t1.paid = no
writing this out has led me to the crux of this problem. That by saying WHERE paid = no that no-longer applies after the first query.
If I understand correctly, you are saying:
* T1 and T2 are related by a One-to-Many relationship with T2 as the "One" Table and T1 the "Many" Table via an AccountID?
* Basically, you want to "clean the slate" if
T2.InitialBalance + Sum( T1.Price * T1.Quantity when T1.Paid = False) >= 25 ?
(I have never seen this business rule???)
Osuspect that you need to establish a list of AccountIDs that need to be "cleaned" before you can actually start the cleaning process. This can be in the form of a temporary Table and then use this temp Table in the 2 Update Queries to identify the AccountIDs to be updated and therefore records belonged to these Accounts to be updated. Thus, the process should be something like:
1. A Make-Table Query to create the temp Table that holds the AccountIDs to be updated.
2. An Update Query to Update T1.
3. An Updae Query to Update T2.
4. Deletion of the temp Table.
Alternatively, you need to use code to create a CSV list of AccountIDs and dynamically construct the Update SQLStrings (in place of the 2 Update Queries) using the In operator with the CSV list and then execute the SQL Strings. In this case, it is logical to enclose the execution of the 2 SQL Strings in a JET Transaction since the updates should be "one fails then all fail".
Oct 8 2009, 11:42 AM
Thank you very much, I understand it must have been in the middle of the night for you.
eah a solution along those lines was all i could think of as well.
Yeah its a strange business rule, pay when it is over 25, but its a unique business (however i have noticed since working on databases how similar all businesses really are).
In retrospect instead of having an initial balance i should have created a product called initial balance and added it to each account.However its built like this now and to change it would be a huge change.
Incidentally what is a temporary table? just a standard table used for temporary purposes? This is what i have used in the past but was just wondering if Access has a purpose built temporary table function?
I think my knowledge might be getting to the point where i can give something back to the community
I suffer from insomnia lately...
emporary in usage sense only. Otherwise, it is a normal Access (JET actually) Table.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here