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! 2 5631 jo********@gmail.com wrote:
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?
--
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.
On Sep 12, 2:34 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
joeyrhy...@gmail.com wrote:
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?
--
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mats |
last post by:
It's good practice to validate input, not only where it should be coming
from, but from anywhere it's possible to change or add input for a
"client".
If all user input is transfered using "post"...
|
by: Jegger |
last post by:
Hello!
We have following situation;
network with 100 users, aplication developed in Access, user DB
deployed on SQL Server.
Is it better to create query inside aplication (with code) and then...
|
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The...
|
by: marty3d |
last post by:
Hi!
I'm trying to create one query by combining several smaller UPDATE,
INSERT and DELETE queries. I know this is easily done in SQL Server
using GO, but I can't figure out how to do it in...
|
by: Madingo |
last post by:
I have been using Access 2003 for about a year and I am trying to find
out how to create a web test environment to try and transition some of
my Access applications on to the web. My stumbling...
|
by: DFS |
last post by:
Architecture: Access 2003 client, Oracle 9i repository, no Access security
in place, ODBC linked tables.
100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters
and ReadOnly....
|
by: Hank |
last post by:
We have just recently migrated the data from our Access 2000
backend to Postgres. All forms and reports seem to run correctly but,
in many cases, very slowly. We do not want to switch over until...
|
by: Ian Boyd |
last post by:
i've been thrown into a pit with DB2 and have to start writing things such
as tables, indexes, stored procedures, triggers, etc. The online reference
is only so helpful. The two pdf manuals are...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |