Running Sum in a Query

Need Help with the "Running Sum" Column in my Query.
Thanks alot - Joe
sSARC No Of sSARC Running Sum
1/12/2005 2 2
1/13/2005 2 4
1/14/2005 3 7
1/18/2005 2 9
1/20/2005 1 10
My SQL statement is:
SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
Tbl_SiteCriteria.BechtelSiteID =
Tbl_FunctionSiteAquisition.BechtelSiteID
GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
HAVING (((Tbl_SiteCriteria.Status)="Active") AND
((Tbl_SiteCriteria.Scope)="UMTS"))
ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];Joe,
Try this: It should be close.
Select A.[sSA Release to Construction] sSarc,
Count(S.BechtelSiteID) [No Of sSARC],
(Select Count(*) From Tbl_SiteCriteria iS
Join Tbl_FunctionSiteAquisition iA
On iS.BechtelSiteID = iA.BechtelSiteID
Where iS.Status = 'Active'
And iS.Scope = 'UMTS'
And iA.[sSA Release to Construction] <=
A.[sSA Release to Construction]) RunningSum
From Tbl_SiteCriteria S
JOIN Tbl_FunctionSiteAquisition A
ON S.BechtelSiteID = A.BechtelSiteID
Where S.Status = 'Active'
And S.Scope = 'UMTS'
Group By A.[sSA Release to Construction]
Order By A.[sSA Release to Construction]
"JoeC" wrote:

> Need Help with the "Running Sum" Column in my Query.
> Thanks alot - Joe
>
> sSARC No Of sSARC Running Sum
> 1/12/2005 2 2
> 1/13/2005 2 4
> 1/14/2005 3 7
> 1/18/2005 2 9
> 1/20/2005 1 10
>
> My SQL statement is:
> SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
> Tbl_SiteCriteria.BechtelSiteID =
> Tbl_FunctionSiteAquisition.BechtelSiteID
> GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
> HAVING (((Tbl_SiteCriteria.Status)="Active") AND
> ((Tbl_SiteCriteria.Scope)="UMTS"))
> ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps this:
SELECT FSA.[sSA Release to Construction],
Count(SC.BechtelSiteID) AS [No Of sSARC],
(SELECT Count(*)
FROM Tbl_SiteCriteria As S1
INNER JOIN Tbl_FunctionSiteAquisition AS F1
ON S1.BechtelSiteID = F1.BechtelSiteID
WHERE S1.BechtelSiteID = SC.BechtelSiteID
AND F1.[sSA Release to Construction] <=
FSA.[sSA Release to Construction]) As RunningCount
FROM Tbl_SiteCriteria AS SC INNER JOIN Tbl_FunctionSiteAquisition AS FSA
ON SC.BechtelSiteID = FSA.BechtelSiteID
WHERE SC.Status='Active' AND SC.Scope='UMTS'
GROUP BY FSA.[sSA Release to Construction], SC.Status, SC.Scope
ORDER BY FSA.[sSA Release to Construction]
Naming a column "BechtelSiteID" is strange & indicates possible problems
w/ your DB design: as in - you've indicated that some table(s) will
only hold data for one company. This is attribute splitting (re: -
CELKO--). Perhaps it would be best if you reviewed a book or articles
on database design, particularly Normalization.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjzYcYechKqOuFEgEQKgcgCdEHMJRSQ08anS
JF4K0QGUABWc4xMAoLZw
n9bt/brqWtgkhJa+nJLfkhB6
=ZcrJ
--END PGP SIGNATURE--
JoeC wrote:
> Need Help with the "Running Sum" Column in my Query.
> Thanks alot - Joe
>
> sSARC No Of sSARC Running Sum
> 1/12/2005 2 2
> 1/13/2005 2 4
> 1/14/2005 3 7
> 1/18/2005 2 9
> 1/20/2005 1 10
>
> My SQL statement is:
> SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
> Tbl_SiteCriteria.BechtelSiteID =
> Tbl_FunctionSiteAquisition.BechtelSiteID
> GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
> HAVING (((Tbl_SiteCriteria.Status)="Active") AND
> ((Tbl_SiteCriteria.Scope)="UMTS"))
> ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];
>|||Was unable to get these statements to work - (just learning SQL)
can we try with this statement.
SELECT Tbl_FunctionSiteAquisition.sSARC,
Count(Tbl_FunctionSiteAquisition.BechtelSiteID) AS [No Of sSARC]
FROM Tbl_FunctionSiteAquisition
GROUP BY Tbl_FunctionSiteAquisition.sSARC
HAVING (((Tbl_FunctionSiteAquisition.sSARC) Is Not Null))
ORDER BY Tbl_FunctionSiteAquisition.sSARC;
I thank you all for your help and timely responces.
The SiteID is multi unique locations of job sites.
JoeC wrote:
> Need Help with the "Running Sum" Column in my Query.
> Thanks alot - Joe
>
> sSARC No Of sSARC Running Sum
> 1/12/2005 2 2
> 1/13/2005 2 4
> 1/14/2005 3 7
> 1/18/2005 2 9
> 1/20/2005 1 10
>
> My SQL statement is:
> SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
> Tbl_SiteCriteria.BechtelSiteID =
> Tbl_FunctionSiteAquisition.BechtelSiteID
> GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
> HAVING (((Tbl_SiteCriteria.Status)="Active") AND
> ((Tbl_SiteCriteria.Scope)="UMTS"))
> ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];|||First of all, ending queries with a semi-colon is done in Access not SQL
Server... Where are you running this ?
Second, Can't help any more w/o more information. What error are getting
from running the following?
Select A.[sSA Release to Construction] sSarc,
Count(S.BechtelSiteID) [No Of sSARC],
(Select Count(*) From Tbl_SiteCriteria iS
Join Tbl_FunctionSiteAquisition iA
On iS.BechtelSiteID = iA.BechtelSiteID
Where iS.Status = 'Active'
And iS.Scope = 'UMTS'
And iA.[sSA Release to Construction] <=
A.[sSA Release to Construction]) RunningSum
From Tbl_SiteCriteria S
JOIN Tbl_FunctionSiteAquisition A
ON S.BechtelSiteID = A.BechtelSiteID
Where S.Status = 'Active'
And S.Scope = 'UMTS'
Group By A.[sSA Release to Construction]
Order By A.[sSA Release to Construction
"JoeC" wrote:

> Was unable to get these statements to work - (just learning SQL)
> can we try with this statement.
> SELECT Tbl_FunctionSiteAquisition.sSARC,
> Count(Tbl_FunctionSiteAquisition.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_FunctionSiteAquisition
> GROUP BY Tbl_FunctionSiteAquisition.sSARC
> HAVING (((Tbl_FunctionSiteAquisition.sSARC) Is Not Null))
> ORDER BY Tbl_FunctionSiteAquisition.sSARC;
> I thank you all for your help and timely responces.
> The SiteID is multi unique locations of job sites.
> JoeC wrote:
>|||On Sun, 20 Mar 2005 08:03:05 -0800, CBretana wrote:

>First of all, ending queries with a semi-colon is done in Access not SQL
>Server... Where are you running this ?
(snip)
Hi Charlie,
Semicolons are not required in SQL Server, but they're not prohibited
either. IIRC, they will be required in some specific cases in SQL Server
2005. I wouldn't be surprised if the number of situations where
semicolons are required will rise even more in future versions.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)