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!

 

[Michel Walsh]

Seems it is a problem of " at the wrong place, and wrong [ ]. Try:


UPDATE tmp_ft_component AS rc
SET rc.volume = Dmax("vol","GetTxnVolAmtTR",
" payee_ id= " & payee_id &
" And market= " & market &
" And period_id= " & period_id )
WHERE component_name='Total Revenue';


Part of the problem was the use of [tablename.fieldName] which should be
[tableName].[fieldName], instead. [tableName.fieldName] would be understood
as looking for a field name of... exactly that, in the table with the
default table active scope.

I assumed all the fields are numerical type (integer).



Hoping it may help,
Vanderghast, Access 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.

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!

 

[joeyrhyu]

Thank you for the help!

Out of curiosity, what would the following query look like if the
payee_id and market fields were text fields? I still haven't gotten
my head around the text/bracket combinations needed for text/varchar
fields.

Is this correct?

UPDATE tmp_ft_component AS rc
SET rc.volume = Dmax("vol","GetTxnVolAmtTR",
" payee_ id= ' " & payee_id & " ' "
" And market= ' " & market & " ' "
" And period_id= " & period_id )
WHERE component_name='Total Revenue';

Again, thank you for the help!

Seems it is a problem of " at the wrong place, and wrong [ ]. Try:

UPDATE tmp_ft_component AS rc
SET rc.volume = Dmax("vol","GetTxnVolAmtTR",
" payee_ id= " & payee_id &
" And market= " & market &
" And period_id= " & period_id )
WHERE component_name='Total Revenue';

Part of the problem was the use of [tablename.fieldName] which should be
[tableName].[fieldName], instead. [tableName.fieldName] would be understood
as looking for a field name of... exactly that, in the table with the
default table active scope.

I assumed all the fields are numerical type (integer).

Hoping it may help,
Vanderghast, Access 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.

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!

 

[John Spencer]

Yes, that is correct other than the extra spaces that I assume you added
for clarity.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you for the help!

Out of curiosity, what would the following query look like if the
payee_id and market fields were text fields? I still haven't gotten
my head around the text/bracket combinations needed for text/varchar
fields.

Is this correct?

UPDATE tmp_ft_component AS rc
SET rc.volume = Dmax("vol","GetTxnVolAmtTR",
" payee_ id= ' " & payee_id & " ' "
" And market= ' " & market & " ' "
" And period_id= " & period_id )
WHERE component_name='Total Revenue';

Again, thank you for the help!

Seems it is a problem of " at the wrong place, and wrong [ ]. Try:

UPDATE tmp_ft_component AS rc
SET rc.volume = Dmax("vol","GetTxnVolAmtTR",
" payee_ id= " & payee_id &
" And market= " & market &
" And period_id= " & period_id )
WHERE component_name='Total Revenue';

Part of the problem was the use of [tablename.fieldName] which should be
[tableName].[fieldName], instead. [tableName.fieldName] would be understood
as looking for a field name of... exactly that, in the table with the
default table active scope.

I assumed all the fields are numerical type (integer).

Hoping it may help,
Vanderghast, Access 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.
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!