I have a vba function im my database to create stored queries in
access. It all works fine except when I start getting into more complex
queries that use aggragate functions.
For example, if I type into a query:
Select tbl1.fieldA, tbl1.fieldB, tbl2.fieldC, sum(tbl2.fieldd ) as
sumOfD, iif(sumOfD=0,0, tbl1.fieldB/sumOfD) as ratio FROM tbl1 LEFT JOIN
tbl2 on tbl1.fieldc=tbl 2.fieldc WHERE tbl2.fieldE>100 GROUP BY
sum(tbl2.fieldd );
it runs fine, but if I pass the string to this fuction to create the
procedure, it says it needs:
"iif(sumOfD=0,0 ,tbl1.fieldB/sumOfD)" in the GROUP BY clause. If I try
adding it, it says that it should not be part of the GROUP BY clause.
So.... my question is, is there any way to get by this?
I simply use
CurrentProject. Connection.Exec ute SQL
to run the SQL, and my SQL string looks like:
CREATE PROC [QueryName] as SelectQuerySQL;
thanks for any help
Matt Bob 1 1808
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The query is incorrect in the GROUP BY clause. Let's try some ANSI 92
syntax instead of JET's syntax (Using an aggregate alias in an
expression):
SELECT T1.fieldA,
T1.fieldB,
T2.fieldC,
IIf(SUM(T2.fiel dD)=0,0,T1.fiel dB/SUM(T2.fieldD)) as ratio
FROM tbl1 As T1
LEFT JOIN
tbl2 As T2
ON T1.fieldc=T2.fi eldc
WHERE T2.fieldE>100
GROUP BY T1.fieldA, T1.fieldB, T2.fieldC
If this fails 'cuz it wants T1.FieldB in the GROUP BY clause, in the
Query's Design column grid, try setting the ratio column's "Total" row
to "Expression ."
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQ/zhO4echKqOuFEgE QJcQACg1KZ+tVjD 4HsjRHXgEfb+FFl QOCEAn0g7
w9K8+atUA3y6ik+ umG81upgU
=5QpP
-----END PGP SIGNATURE-----
Matt Bob wrote: I have a vba function im my database to create stored queries in access. It all works fine except when I start getting into more complex queries that use aggragate functions.
For example, if I type into a query:
Select tbl1.fieldA, tbl1.fieldB, tbl2.fieldC, sum(tbl2.fieldd ) as sumOfD, iif(sumOfD=0,0, tbl1.fieldB/sumOfD) as ratio FROM tbl1 LEFT JOIN tbl2 on tbl1.fieldc=tbl 2.fieldc WHERE tbl2.fieldE>100 GROUP BY sum(tbl2.fieldd );
it runs fine, but if I pass the string to this fuction to create the procedure, it says it needs: "iif(sumOfD=0,0 ,tbl1.fieldB/sumOfD)" in the GROUP BY clause. If I try adding it, it says that it should not be part of the GROUP BY clause.
So.... my question is, is there any way to get by this?
I simply use CurrentProject. Connection.Exec ute SQL
to run the SQL, and my SQL string looks like: CREATE PROC [QueryName] as SelectQuerySQL; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: GC |
last post by:
HI,
I'am using Crystal Report with visual Studio .NEt 2003
When i make a report using a store procedure, I can not see all the fields
of the store proc and i'm suppose to see those fields. I'm suppose to see a
"+" sign at the left of the store procedure name in the Field Explorer section
When we click on the sign "+" we see all the field of the store proc.
We can from there select and drag those fileds on the report.
|
by: (Pete Cresswell) |
last post by:
I've got a .BAT file that I use for executing various MS Access apps that I
wrote way back in the days of 2.0.
It's evolved over time, but it still contains a number of possible paths to
MSACCESS.EXE, tries them all, takes the first one it finds, and dies if none is
found.
Seems to me like at some time or another I was able to clone this .BAT file and
implement it with no path. Something about MSACCESS being a registered
application.
|
by: Bruce |
last post by:
Is there a way to create the "old style" Excel pivot table form in
MSAccess 2002/2003?
I've seen several postings "dancing around this issue", but no
definite answer.
Is there a way to clone the Access 2000 Pivot Table Wizard over to
2003 to provide this functionality?
Rgds,
Bruce
|
by: CLarkou |
last post by:
I am trying to create an installation CD for my MSAccess program in
Office 2003. I used the "Access 2003 Developer Extensions". My MDE
files are installed ok, the problem is with OCX files. There is no
option for selecting them in the package program. In previous Office
version packages, OCX files where installed with setup program.
Is there any way to include them in the package program or I should
register them manually ?
|
by: Chris |
last post by:
I recieve unrecognized database format when trying to open a MSAccess
2003 database with MSAccess 2000. I only need it to open as
read-only.
Why won't it open my database?
| |
by: reidarT |
last post by:
I am making an installation program for an Access application. The
application is run on both Office 2000, 2002, 2003 and soon 2007 versions of
Office,
When I install the application I need a shortcut from the users desktop and
I need
C:\programfiles\office\office9\msaccess path\application.mdb /wrkgrp
path\lockfile.mdw
C:\programfiles\office\office10\msaccess path\application.mdb /wrkgrp
path\lockfile.mdw...
|
by: stuart.medlin |
last post by:
I have an Access 2003 application that I recently converted from
Access 97. I have a routine in which I use to export the data that
the users have keyed into a text file. This text file resides on a
network drive where the back end database resides so that multiple
users can write to the same file. I have set up the path to the back
end (under the Link Manager) using the UNC (univ. naming convention).
When this export routine runs, I...
|
by: adjo |
last post by:
I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc....
|
by: jdaelhousen |
last post by:
I have a bit of a problem I'm hoping someone can shed some light on...
I have a VB.Net console application written in VS 2003 that produces a .exe file that now sits on a Windows 2000 server directory. This exe does the following 3 things:
1.) Using the VB Interaction SaveSetting() method, it programmatically updates the system registry under the HKEY_CURRENT_USER\SOFTWARE key and saves a directory location used by a PDF driver (so that...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |