473,597 Members | 2,234 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.pa yee_id And p.market=t.mark et
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):

UPDATE tmp_ft_componen t AS rc
SET rc.volume = Dmax("vol","Get TxnVolAmtTR","G etTxnVolAmtTR.p ayee_ id=
" & [rc.payee_id] And "GetTxnVolAmtTR .market= " & [rc.market] And
"GetTxnVolAmtTR .period_id= " & [rc.period_id] & "")
WHERE rc.component_na me='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_componen t AS rc inner join on GetTxnVolAmtTR as tr
SET rc.volume = tr
WHERE rc.component_na me='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!

Sep 12 '07 #1
2 5647
jo********@gmai l.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.
Sep 12 '07 #2
On Sep 12, 2:34 pm, "Bob Barrows [MVP]" <reb01...@NOyah oo.SPAMcom>
wrote:
joeyrhy...@gmai l.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.

Sep 12 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4972
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" you can be pretty tough on querystrings, if you use them at all. But user input could have a name like Mc'Donald, and we would not like quotes (wether single or double) in input to a database or an asp script. Though I beleive more dangerous in...
6
3427
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 pass it to SQL Server for execution or is it better to have all these queries saved like stored procedures and then called from aplication?
49
14308
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 application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data (SQL Server 2000), 40 users. I'm wondering what are the disadvantages of using Access as front-end? Other that it's not...
4
4807
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 Access. Is it even possible, or are there at least any workarounds? My goal is for my client to just have to run a query (or a macro or something) to update his products table with a previously imported
21
2139
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 block is that I do not know how to create the web server environment. If anyone knows of a good starting point on how to learn this it would be most appreciated because I am unsure of what questions to even ask. Regards, Bill Mahoney The Alcott...
11
3553
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. Each group sees a different set of menu options when they open the client and login to Oracle. For the sake of speed I use pass-through queries here and there for updates and deletes. I update their SQL property in code and execute them.
10
2356
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 we can speed things up. We would like to start implementing Stored Procedures so we can do Server-Side processing. Can anyone recommend a book that would help us learn how to use sprocs or pass-through queries? I apologize if my terminology...
138
59256
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 only so helpful. Googling is only so helpful. So let's start with some simple SQL constructs, that i know so very well in SQL Server, that seem to be like pulling teeth in DB2. 1. Selecting a value
11
4480
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 AutoNumber* fields in them. Correct me if I'm wrong, but I'm assuming this means that I cannot now alter these existing Access tables and change their primary key to an "AutoNumber" type. If I'm right about this, I need some suggestions as to the...
0
8274
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8381
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6695
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5434
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3886
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3930
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2404
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1494
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.