Dsum calculation by date

[Guest]

I have been trying to calculate a running sum for consecutive dates. My SQL
for the query is as follows:

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
(DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled] <= " & qrySKEmergenceStats.date_sampled)) AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

The query produces blank results. Thanks for your help!

 

[Allen Browne]

Explicitly format the date as m/d/y (as required by JET), and add the #
delimiter:

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
(DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats",
"[date_sampled] <= " & Format(qrySKEmergenceStats.date_sampled,
"\#mm/dd/yyyy\#"))
AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

If you do not mind a read-only results (e.g. if it is going to a report), it
would be more efficient to use a subqeury rather than DSum().

 

[Guest]

Try this

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled] <= #" & qrySKEmergenceStats.date_sampled & "#") AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

 

[Guest]

YAHOO, it worked! Thanks so much!

Try this

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled] <= #" & qrySKEmergenceStats.date_sampled & "#") AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

I have been trying to calculate a running sum for consecutive dates. My SQL
for the query is as follows:

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
(DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled] <= " & qrySKEmergenceStats.date_sampled)) AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

The query produces blank results. Thanks for your help!

 

[Guest]

Another question, same topic. Now I would like to chart the query
(preferably in a form), but it will only chart the COUNT and not the actual
SUM. Thanks!

YAHOO, it worked! Thanks so much!

Try this

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled] <= #" & qrySKEmergenceStats.date_sampled & "#") AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

I have been trying to calculate a running sum for consecutive dates. My SQL
for the query is as follows:

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
(DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled] <= " & qrySKEmergenceStats.date_sampled)) AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

The query produces blank results. Thanks for your help!

 

[Guest]

What do you mean by subquery? Read only results would be fine in this
scenerio. I would like to create a chart (preferably a Pivot Chart) with the
results of the cumulative sum.

Explicitly format the date as m/d/y (as required by JET), and add the #
delimiter:

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
(DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats",
"[date_sampled] <= " & Format(qrySKEmergenceStats.date_sampled,
"\#mm/dd/yyyy\#"))
AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

If you do not mind a read-only results (e.g. if it is going to a report), it
would be more efficient to use a subqeury rather than DSum().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have been trying to calculate a running sum for consecutive dates. My
SQL
for the query is as follows:

SELECT qrySKEmergenceStats.DATE_SAMPLED, qrySKEmergenceStats.SumOfSKPERHR,
(DSum("[qrySKEmergenceStats].[SumOfSKPERHR]","qrySKEmergenceStats","[date_sampled]
<= " & qrySKEmergenceStats.date_sampled)) AS CUMSUM_SKPERHR
FROM qrySKEmergenceStats;

The query produces blank results. Thanks for your help!

 

[Allen Browne]

A subquery is a complete SELECT query statement within another query.

Here is Microsoft's introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

WOAH, this is a whole new chapter for me...
Now I have spent the entire morning trying to figure the out the required
syntax to get it to work and keep getting errors. Any suggestions on how to
get me on the right track? I greatly appreciate your patience and help!

 

[Allen Browne]

Yes, subqueries are great when you discover them. Be patient with yourself:
it does take some effort to find your way through them.

In essence a subquery is a complete SELECT query statement inside another
query.

General Tips for Subqueries
======================
1. If you are returning a value from the subquery (it is in the SELECT
clause of the main query), the query results will be read-only, and the
subquery cannot return multiple values. To achieve that, in the subquery use
TOP (with an ORDER BY that can't return multiples), First(), Max(), etc, or
return the primary key.

2. You can use a subquery in the WHERE clause, typically with EXISTS or ANY.
This also means you can use a subquery in the Filter of a form or report, to
filter by a field from another table.

3. The subquery can refer to fields in the main query, e.g.:
SELECT Client.*,
(SELECT Count("*")
FROM Order
WHERE Order.ClientID = Client.ClientID) AS OrderCount
FROM Client;

4. If you need to refer to another record in one of the tables that is
already in the main query, alias the table, e.g.:
SELECT Employees.*,
(SELECT Dupe.FirstName & " " & Dupe.LastName
FROM Employees AS Dupe
WHERE Dupe.EmployeeID = Employees.ReportsTo) AS Boss
FROM Employees;

5. Where you return a value from a subquery to use in a report, you may
strike a "Multi-level group by error". It is the sorting and grouping in the
report that causes this problem, and the only way around it is to use
another approach such as a stacked query--a query that uses another query as
an input "table".

6. There are some subqueries that will cause JET (the data/query engine in
Access) to crash. This is true in all versions of Access, and has never been
fixed. Just be aware that it could happen, and keep good backups as you
develop in case everything goes pear-shaped.

 

[Guest]

Hi Allen, I am a casual user of Access and I have almost the same problem
that I cant for weeks resolv, I need to accumulate my values by week and show
a linear chart. For exemplo:
week sign drop sf
1 5 2 3
2 4(new #+5) 1(+2) 2(+3)

I dont know if I am clear, but please help me, for my research it seems the
same but I am so novice with access, can u help me?Tia

 

[Allen Browne]

It will be something like this:
SELECT Date_Sampled
(SELECT Sum(SumOfSKPERHR)
FROM qrySKEmergenceStats AS Dupe
WHERE Dupe.Date_Sampled <= qrySKEmergenceStats.Date_Sampled)
AS CumSum_Skperhr
FROM qrySKEmergenceStats;

 

[Guest]

Allen, you are my hero for the day. It works and it charts in a Pivot Chart!
Thank you so much for your guidance and patience.