I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, "
_
& " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions .PshpID =
tblMonthly.Pshp ID " _
& "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it
returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage
where another set of eyes is needed. Thanks!)
Yisroel 8 1736
If PshpID is unique then you will get a record per record summation.
try
strSQL = "SELECT tblTransactions .TransDate, " _
& " Sum(tblTransact ions.DDAmount) AS SumDDAmount " _
& "FROM tblTransactions " _
& "INNER JOIN tblMonthly " _
& "ON tblTransactions .PshpID = tblMonthly.Pshp ID " _
& "WHERE tblTransactions .TransDate " _
& "BETWEEN #12/31/03# AND #05/31/04# " _
& "GROUP BY tblTransactions .TransDate"
This will give you
The total DDAmount per TransDate where records exist in tblTransactions
with corresponding records in tblMonthly
--
Terry Kreft
MVP Microsoft Access
"Yisroel Markov" <ey*******@inam e.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com... I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, " _ & " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _ & "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _ & "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage where another set of eyes is needed. Thanks!)
Yisroel
Your query lotals per each TransDate, which looks as individual
records. Remove TransDate from SELECT. Galina ey*******@iname .com (Yisroel Markov) wrote in message news:<2f******* *************** ****@posting.go ogle.com>... I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, " _ & " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _ & "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _ & "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage where another set of eyes is needed. Thanks!)
Yisroel
"Yisroel Markov" <ey*******@inam e.com> wrote in message
news:2f******** *************** ***@posting.goo gle.com... I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, " _ & " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _ & "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _ & "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage where another set of eyes is needed. Thanks!)
Without seeing your table design there's no way to tell what the problem is.
Try printing the sSQL variable to the immediate window and copy to a new
query to test the SQL. Otherwise post your table structure with some sample
data and the output you need.
Galina wrote: Your query lotals per each TransDate, which looks as individual records. Remove TransDate from SELECT.
Galina, I can't do that. I need to total on PshpID only, but first the
SQL has to select all transactions whose TransDate falls into this
range. The range is hard-coded right now, but eventually it will need to
use tblMonthly.BegD ate and an ending date from an open form. Can I do
that?
Spasibo!
Yisroel
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
To follow up and cut to the chase:
Thanks to all who responded. What I'm trying to do is this:
I have a tblMonthly, which has a list of clients. Each has a unique
PshpID and a beginning date (BegDate), which varies from client to
client. I also have a tblTransactions , which contains multiple
transactions for each client for many TransDates, each tagged with
their PshpID.
What I need to do is, for each client, select all transactions between
BegDate and a report date (fed from a user form and the same for all
clients), sum them up and write the sum to tblMonthly in the
corresponding client's record. This is proving unduly difficult (well,
I am a self-taught amateur at this). It seems impossible to select
such transactions (with variable beginning date) and sum them up in a
single SQL statement - there are problems using GROUP BY on TransDate
(which I don't want to do - I need to sum by PshpID - but SQL demands
it). I wrote a query to do that, first with hard-coded dates, and then
created another SQL-based recordset to sum up the amounts, thus
avoiding the problem with grouping on TransDate. That worked until I
replaced the ending date (5/31/04 in the example earlier in the
thread) with a reference to a textbox control on the user form - the
code saw it (I checked via a temporary string variable), but
OpenRecordset method responded with "Too few parameters. Expected 1."
So I created another table to hold the selected data, and will need to
sum it with another SQL-based recordset, and then write it to
tblMonthly... isn't there a more elegant way of doing this?
And is there a good book on Access VBA which goes a bit beyond the
basics, and has a good error reference? ey*******@iname .com (Yisroel Markov) wrote in message news:<2f******* *************** ****@posting.go ogle.com>... I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, " _ & " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _ & "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _ & "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage where another set of eyes is needed. Thanks!)
Yisroel
don't include 'transDate' in the SELECT string, ie.
strSQL = "SELECT tblTransactions .PshpID, " _
& " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _
& "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _
& "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _
& "GROUP BY tblTransactions .PshpID"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset) ey*******@iname .com (Yisroel Markov) wrote in message news:<2f******* *************** ****@posting.go ogle.com>... I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, " _ & " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _ & "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _ & "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage where another set of eyes is needed. Thanks!)
Yisroel
Yisroel
Pozhaluysta.
Of course you can do it! Make criteria in your query between dates,
however you want it, but uncheck (remove tick) in the Show: line and
select Where in the Total: line of query design. And your query will
select between dates and sum PshpID only. You don't need any code for
it.
Galina
Yisroel Markov <ey*******@inam e.com> wrote in message news:<40******* *************** @news.newsgroup s.ws>... Galina wrote:
Your query lotals per each TransDate, which looks as individual records. Remove TransDate from SELECT.
Galina, I can't do that. I need to total on PshpID only, but first the SQL has to select all transactions whose TransDate falls into this range. The range is hard-coded right now, but eventually it will need to use tblMonthly.BegD ate and an ending date from an open form. Can I do that?
Spasibo!
Yisroel *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it!
Hi
What do you need to write your sums into a table for? Anything, what
can be calculated, should not be kept in tables as a general
principle. You only make your life more difficult.
I don't think that you need any coding here. If we leave aside writing
sums into a table, which is not needed, what the query should provide
results for? A Form? a report?
If you have an error message "Too few parameters. Expected 1." it
means that you need to design a parameter query and code it's excution
correctly. If you still think you need it, search this group with key
words "parameter query". Sometime ago we discuss the topic quite a
lot.
I am tempted to suggest you to e-mail your database. It seems simpler
to make this query for you rather then to write long explanations.
Galina ey*******@iname .com (Yisroel Markov) wrote in message news:<2f******* *************** ****@posting.go ogle.com>... To follow up and cut to the chase:
Thanks to all who responded. What I'm trying to do is this:
I have a tblMonthly, which has a list of clients. Each has a unique PshpID and a beginning date (BegDate), which varies from client to client. I also have a tblTransactions , which contains multiple transactions for each client for many TransDates, each tagged with their PshpID.
What I need to do is, for each client, select all transactions between BegDate and a report date (fed from a user form and the same for all clients), sum them up and write the sum to tblMonthly in the corresponding client's record. This is proving unduly difficult (well, I am a self-taught amateur at this). It seems impossible to select such transactions (with variable beginning date) and sum them up in a single SQL statement - there are problems using GROUP BY on TransDate (which I don't want to do - I need to sum by PshpID - but SQL demands it). I wrote a query to do that, first with hard-coded dates, and then created another SQL-based recordset to sum up the amounts, thus avoiding the problem with grouping on TransDate. That worked until I replaced the ending date (5/31/04 in the example earlier in the thread) with a reference to a textbox control on the user form - the code saw it (I checked via a temporary string variable), but OpenRecordset method responded with "Too few parameters. Expected 1." So I created another table to hold the selected data, and will need to sum it with another SQL-based recordset, and then write it to tblMonthly... isn't there a more elegant way of doing this?
And is there a good book on Access VBA which goes a bit beyond the basics, and has a good error reference?
ey*******@iname .com (Yisroel Markov) wrote in message news:<2f******* *************** ****@posting.go ogle.com>... I have the following in my code:
strSQL = "SELECT tblTransactions .PshpID, tblTransactions .TransDate, " _ & " Sum(tblTransact ions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions .PshpID = tblMonthly.Pshp ID " _ & "WHERE tblTransactions .TransDate BETWEEN #12/31/03# AND #05/31/04# " _ & "GROUP BY tblTransactions .PshpID, tblTransactions .TransDate"
Set rsTrans = dbs.OpenRecords et(strSQL, dbOpenDynaset)
This compiles and runs, but instead of totaling DDAmount on PshpID, it returns individual records. tblMonthly contains only unique PshpID.
(Must be something really simple, but apparently I'm at the stage where another set of eyes is needed. Thanks!)
Yisroel This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Deano |
last post by:
OK, I'm working on a solution to the following problem but there are other
ways to skin a cat as they say...
Here's the table (simplified);
ID EmployeeName SalaryAcc
1 Brown 3,475
2 Smith 12,302
3 Smith 19,450
4 Jones 16,700
|
by: Tony Williams |
last post by:
I have a database that hold s figures for each quarter. I want to create a
query whereby the user can ask for the running total of the data upto the
quarter they enter into a prompt. The database stores the quarter name in
txtmonthlabel (a date field) and the quarters totals in txtdomic (a number
field) EG If the user enters March 2004 they get figures upto March 2004, if
they enter June 2004 they get total upto June 2004, in other words...
|
by: deko |
last post by:
I have a number of queries that pull totals from different tables. How do I
sum the different total values from each query to get a grand total? I
tried using a Union query like this:
SELECT cTotal As bct FROM qryCbank1
UNION SELECT cTotal As bct FROM qryCbank2
UNION SELECT cTotal As bct FROM qryCbank3
UNION SELECT cTotal As bct FROM qryCbank4
UNION SELECT cTotal As bct FROM qryCbank5
UNION SELECT cTotal As bct FROM qryCbank6;
|
by: New Guy |
last post by:
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:
There is a table of payments and a table of charges. Each client has
charges and payments during the month.
I'd like to get the totals of the payments and of the charges for each
client. When I run the following query, I get huge numbers that
appear as if the join is not working correctly.
|
by: BerkshireGuy |
last post by:
I have the following code:
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined,
intNumOfWasted, intNumOfApproved As String
Dim QDF As QueryDef
Dim PARAM As Parameter
| |
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with my
fields. When I try to run it I get #errors in my RunTot column. I'm
kinda new to this. Not sure if maybe I mistyped something wrong or is
there a better way to do this? I have pasted the code. Any help would
be greatly appreciated....
|
by: Micheal |
last post by:
Greetings Access Group,
Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.
I currently have a report that contains 2 totals and the percentage of the difference of the 2...
|
by: Ron |
last post by:
Hi All,
I've got a main form (frmMain) with 5 subforms on it. All subforms are
linked to different hidden controls on frmMain. The purpose of this form is
to record payments by 'source' (batches) so frmMain's source is a query on
BatchPaymentID. New 'source' for payment, new BatchPaymentID. 1st subform
(sfrmOne) is a client list of just the client's that would have that source
of payment. Next subform (sfrmTwo) is a list of charges...
|
by: plaguna |
last post by:
I’m trying to get some calculations in a Query. Four fields are from a table x, and base on those fields I need to get some combination of Totals. This is the scenario.
Total A : + from table x
Total B : + from table x
So far so good. I don’t have any problem to have totals until this point. My question is: how can I create these next calculations.
Total C : (Total A) / (Total B)
Total D : (Total B) * (Total C)
|
by: Bytesmiths |
last post by:
I'm using GROUP BY to come up with totals for some items, but would like to either have a grand total at the bottom, or a running total field.
I tried to use a variable as I found in several places on the web:
SELECT @total := total + value AS `Running Total`
but what I'm totalling is an aggregate, and it doesn't seem to work. I tried to explore some options dev.mysql.com, but again, my field seems to be too complex.
More specifically,...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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: 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: 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...
|
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: 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.
|
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...
| |