A Totals Query That Doesn't Total

[Yisroel Markov]

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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

[Terry Kreft]

If PshpID is unique then you will get a record per record summation.

try

strSQL = "SELECT tblTransactions.TransDate, " _
& " Sum(tblTransactions.DDAmount) AS SumDDAmount " _
& "FROM tblTransactions " _
& "INNER JOIN tblMonthly " _
& "ON tblTransactions.PshpID = tblMonthly.PshpID " _
& "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*******@iname.com> wrote in message
news:2f**************************@posting.google.c om...

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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

[Galina]

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.google. com>...

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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

[John Winterbottom]

"Yisroel Markov" <ey*******@iname.com> wrote in message
news:2f**************************@posting.google.c om...

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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.

[Yisroel Markov]

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.BegDate 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!

[Yisroel Markov]

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.google. com>...

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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

[Roger]

don't include 'transDate' in the SELECT string, ie.
strSQL = "SELECT tblTransactions.PshpID, " _
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID = tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04# " _
& "GROUP BY tblTransactions.PshpID"

Set rsTrans = dbs.OpenRecordset(strSQL, dbOpenDynaset)

ey*******@iname.com (Yisroel Markov) wrote in message news:<2f**************************@posting.google. com>...

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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

[Galina]

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*******@iname.com> wrote in message news:<40**********************@news.newsgroups.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.BegDate 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!

[Galina]

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.google. 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.google. com>...

I have the following in my code:

strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, "
_
& " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions
" _
& "INNER JOIN tblMonthly ON tblTransactions.PshpID =
tblMonthly.PshpID " _
& "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04#
" _
& "GROUP BY tblTransactions.PshpID, tblTransactions.TransDate"

Set rsTrans = dbs.OpenRecordset(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