Help w - Good Old Running Counts in a Query

  • Thread starter Thread starter JoeC
  • Start date Start date
J

JoeC

sSARC Counts "Need a running Count"
1/12/2005 2 2
1/13/2005 2 4
1/14/2005 3 7
1/18/2005 2 9
1/20/2005 1 Ect.

The Sql Statement from the Query is:

SELECT Tbl_FunctionSiteAquisition.sSARC,
Count(Tbl_FunctionSiteAquisition.BechtelSiteID) AS CountOfBechtelSiteID
FROM Tbl_FunctionSiteAquisition
GROUP BY Tbl_FunctionSiteAquisition.sSARC
HAVING (((Tbl_FunctionSiteAquisition.sSARC) Is Not Null))
ORDER BY Tbl_FunctionSiteAquisition.sSARC;

Basiclly we have One Table Tbl_FunctionSiteAquisition.
We are showing only the records that have Dates populated.
The Dates are sorted by Ascending order and are grouped by the
BechtelSiteID to give me the Counts per that date.
The BechtelSiteID is the Primary Key.

I need to have a running total for plotting a Curve line in a Chart /
Graph (Base Line)

Thank to all the wizards for your help.

Ps. Go Red Soxs
 
Hi Joe,

You can use DCount() or a correlated subquery in a calculated expression to
return the value that you are looking for.

a correlated subquery would be:

(SELECT Count(*) FROM Tbl_FunctionSiteAquisition AS VT WHERE VT.sSARC <=
Tbl_FunctionSiteAquisition.sSARC AND VT.sSARC IS NOT NULL)

DCount would be:

DCount("sSARC","Tbl_FunctionSiteAquisition","sSARC <= #" & sSARC & "# AND
sSARC IS NOT NULL")

Actually the "AND sSARC IS NOT NULL" on the DCount may not be needed because
I think that it does not count null values unless using * (I'm not completely
sure because I usually use correlated subqueries).

In either case, you can just add the expression to your SELECT list in SQL,
or add it as the source of a new field in your query if using the query
design view.

Hopefully that will help, post back if it doesn't work (it's not uncommon to
have typos when typing these out).

Ted Allen
 
Back
Top