MS ACCESS Jet SQL: Update statements that utilize saved queries/dmax

[joeyrhyulz]

Hi,

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!

 

[Bob Barrows [MVP]]

Hi,

I'm trying to make a very simple update statement (in Oracle) in jet
sql that seems much more difficult than it should be.


Questions:
1) What am I missing on the field joins on the Dmax function?

An Access group would be more relevant for this question.

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.

Yes, but it has to be done correctly - again - see an Access group. You
have to get this working in Access before even trying to get it to work
from ASP .... oh, wait a minute ... the domain functions (dmax, dmin,
etc.) are not usable from ASP.

Does this question have anything to do with ASP?

 

[joeyrhyu]

An Access group would be more relevant for this question.


Yes, but it has to be done correctly - again - see an Access group. You
have to get this working in Access before even trying to get it to work
from ASP .... oh, wait a minute ... the domain functions (dmax, dmin,
etc.) are not usable from ASP.

Does this question have anything to do with ASP?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Oops, I'm sorry, I posted this in the wrong forum.