MS ACCESS Jet SQL: Update statements that utilize saved queries/dmax
am 12.09.2007 20:43:29 von joeyrhyulzHi,
I'm trying to make a very simple update statement (in Oracle) in jet
sql that seems much more difficult than it should be.
The root of my problem is that I'm trying to update a field on a table
using dmax, which references another query to update the table.
Although I have all of the correct keys from the physical table joined
to the query in the dmax function, the code/ms access seems to ignore
the joins. As a result, all payees are having their "vol" field set
instead of a select subset that is returned by the saved query.
Saved Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id
FROM ft_payees AS p, ft_txn_summary AS t
WHERE p.payee_id=t.payee_id And p.market=t.market
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc
SET rc.volume = Dmax("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_ id=
" & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And
"GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
WHERE rc.component_name='Total Revenue';
I've tried fixing the joins to:
DMax("vol", "GetTxnVolAmtTR", "GetTxnVolAmtTR.payee_ id= " &
[rc.payee_id] & " And GetTxnVolAmtTR.market= " & [rc.market] & " And
GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
but that just sets the values to null.
----------------------
Another possible way of going about this problem is to utilize the
saved query like a table and do something like the following:
UPDATE tmp_ft_component AS rc inner join on GetTxnVolAmtTR as tr
SET rc.volume = tr
WHERE rc.component_name='Total Revenue'
AND tr.payee_id = rc.payee_id
AND rc.market = tr.market
AND tr.period_id = rc.period_id;
I've tried running it and it gives me the "not an updateable
statement." something I'm quite familiar with after wrestling with
Jet SQL for some time.
Questions:
1) What am I missing on the field joins on the Dmax function?
2) Is it possible to join to a saved query like a table for update
statements? I know they work if you just do a simple select.
Any help would be much appreciated.
Thank you!