DSum & Dates

[David8]

Hi from Barcelona,
I've a query with this field:

TOTAL: DSuma("one*myfield";"table";"[date] between #02/01/2009#and
#02/28/2009#")

The problem is it not makes a running sum, since the first day the field
shows the total of the month in the field.
Where is the problem?
Thanks,
David8

 

[John W. Vinson]

Hi from Barcelona,
I've a query with this field:

TOTAL: DSuma("one*myfield";"table";"[date] between #02/01/2009#and
#02/28/2009#")

The problem is it not makes a running sum, since the first day the field
shows the total of the month in the field.
Where is the problem?
Thanks,
David8

There is no problem; Access is giving you exactly what you're asking for (even
if that's not what you want!). Your DSum is explicitly summing the value of
the field (is it *REALLY* a field named one*myfield? if so that's a Bad Idea)
from the table "table" for all dates between February 1 and February 28. There
is nothing in this expression that would tell the program to give you a
running sum.

If that is not what you want to sum, please indicate the context in which
you're doing this (what's the query containing this?) and what range of dates
you want summed.

 

[David8]

Hi John,
The field name is CASH, i've change it for this example, the table name is
SALES.
I've a table whith a date field, a field who have the name of the month and
a money field who is named CASH.
I want a running sum, and i write this:

RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id])

and the same query who contains this runningfield has a filter for the month
February, but the result of the runningsum is for all the year, and i want
the running sum begins the first day of the month.
Thanks again,
David8

Hi from Barcelona,
I've a query with this field:

TOTAL: DSuma("one*myfield";"table";"[date] between #02/01/2009#and
#02/28/2009#")

The problem is it not makes a running sum, since the first day the field
shows the total of the month in the field.
Where is the problem?
Thanks,
David8

There is no problem; Access is giving you exactly what you're asking for (even
if that's not what you want!). Your DSum is explicitly summing the value of
the field (is it *REALLY* a field named one*myfield? if so that's a Bad Idea)
from the table "table" for all dates between February 1 and February 28. There
is nothing in this expression that would tell the program to give you a
running sum.

If that is not what you want to sum, please indicate the context in which
you're doing this (what's the query containing this?) and what range of dates
you want summed.

 

[John W. Vinson]

Hi John,
The field name is CASH, i've change it for this example, the table name is
SALES.
I've a table whith a date field, a field who have the name of the month and
a money field who is named CASH.
I want a running sum, and i write this:

RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id])

and the same query who contains this runningfield has a filter for the month
February, but the result of the runningsum is for all the year, and i want
the running sum begins the first day of the month.

This will sum all the records from SALES where the ID is less than the current
record's ID - whether the record was entered yesterday or three years ago.

Do you have a sales date? Do you in fact have the *NAME* of the month -
"Enero", "Febrero" - in the table, or a date/time field indicating the sale
date? If you have the month name, how do you distinguish January sales in 2009
from those in 2007 or 2008 or 2010?

You will need *some* additional criteria in the third argument of Dsuma to
limit the records to those prior to this record in this month. Unless you
specify it in the call to DSuma, it will look at all records in the table, and
will have no relationship to the records in the query from which you are
calling it.

 

[David8]

Yes, i've a month field and a year field, but for the moment i've only
records for 1 year, so i write this and i've the same results.
RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id] Y [mes]="FEB")
Is correct this?

Thanks,
David8

Hi John,
The field name is CASH, i've change it for this example, the table name is
SALES.
I've a table whith a date field, a field who have the name of the month and
a money field who is named CASH.
I want a running sum, and i write this:

RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id])

and the same query who contains this runningfield has a filter for the month
February, but the result of the runningsum is for all the year, and i want
the running sum begins the first day of the month.

This will sum all the records from SALES where the ID is less than the current
record's ID - whether the record was entered yesterday or three years ago.

Do you have a sales date? Do you in fact have the *NAME* of the month -
"Enero", "Febrero" - in the table, or a date/time field indicating the sale
date? If you have the month name, how do you distinguish January sales in 2009
from those in 2007 or 2008 or 2010?

You will need *some* additional criteria in the third argument of Dsuma to
limit the records to those prior to this record in this month. Unless you
specify it in the call to DSuma, it will look at all records in the table, and
will have no relationship to the records in the query from which you are
calling it.

 

[John W. Vinson]

Yes, i've a month field and a year field, but for the moment i've only
records for 1 year, so i write this and i've the same results.
RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id] Y [mes]="FEB")
Is correct this?

You can tell if it works - I can't!

It does makes some assumptions and has some normalization problems. If you get
data for another year, it will mix FEB 2009 data with FEB 2010 data; it does
assume that ID is strictly increasing; it requires a separate query every
month... Is there some reason for using separate year and month fields rather
than a date field (which WILL sort chronologically)?

Could you please post the COMPLETE SQL of the query and give some indication
of the table fieldnames? I'm not fluent in Spanish but I can read it if that's
an issue.

 

[David8]

Table name: SALES
Fields:
mes (Text)
cash (Currency)

The table has this records:
mes cash
ene 10
ene 5
feb 12
feb 6
mar 50
mar 60

Query: SELECT DSum("cash","SALES","Id <=" & [Id]) AS VENTAS, SALES.id,
SALES.mes, SALES.cash
FROM SALES
WHERE (((SALES.mes)="FEB"))
ORDER BY SALES.id;

The result must do (i want this)
VENTAS mes cash
12 feb 12
18 feb 6

And now, the results are:
VENTAS mes cash
27 feb 12
33 feb 6

Thanks,
David8

Yes, i've a month field and a year field, but for the moment i've only
records for 1 year, so i write this and i've the same results.
RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id] Y [mes]="FEB")
Is correct this?

You can tell if it works - I can't!

It does makes some assumptions and has some normalization problems. If you get
data for another year, it will mix FEB 2009 data with FEB 2010 data; it does
assume that ID is strictly increasing; it requires a separate query every
month... Is there some reason for using separate year and month fields rather
than a date field (which WILL sort chronologically)?

Could you please post the COMPLETE SQL of the query and give some indication
of the table fieldnames? I'm not fluent in Spanish but I can read it if that's
an issue.

 

[John Spencer]

You need to restrict the sum to the current month.

SELECT DSum("cash","SALES","Id <=" & [Id] & " Mes='FEB'") AS VENTAS
, SALES.id
, SALES.mes
, SALES.cash
FROM SALES
WHERE (((SALES.mes)="FEB"))
ORDER BY SALES.id;

Or better so you don't have to hard code the month.

SELECT DSum("cash","SALES","Id <=" & [Id] & " Mes='" & [Mes] & "'") AS
VENTAS
, SALES.id
, SALES.mes
, SALES.cash
FROM SALES
WHERE (((SALES.mes)="FEB"))
ORDER BY SALES.id;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Table name: SALES
Fields:
mes (Text)
cash (Currency)

The table has this records:
mes cash
ene 10
ene 5
feb 12
feb 6
mar 50
mar 60

Query: SELECT DSum("cash","SALES","Id <=" & [Id]) AS VENTAS, SALES.id,
SALES.mes, SALES.cash
FROM SALES
WHERE (((SALES.mes)="FEB"))
ORDER BY SALES.id;

The result must do (i want this)
VENTAS mes cash
12 feb 12
18 feb 6

And now, the results are:
VENTAS mes cash
27 feb 12
33 feb 6

Thanks,
David8

Yes, i've a month field and a year field, but for the moment i've only
records for 1 year, so i write this and i've the same results.
RUNNINGFIELD: DSuma("CASH";"SALES";"Id <=" & [Id] Y [mes]="FEB")
Is correct this?

You can tell if it works - I can't!

It does makes some assumptions and has some normalization problems. If you get
data for another year, it will mix FEB 2009 data with FEB 2010 data; it does
assume that ID is strictly increasing; it requires a separate query every
month... Is there some reason for using separate year and month fields rather
than a date field (which WILL sort chronologically)?

Could you please post the COMPLETE SQL of the query and give some indication
of the table fieldnames? I'm not fluent in Spanish but I can read it if that's
an issue.