Calculating Cumulative Numbers

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

Kirk P.

Here's my data:

GrantID FiscalYr EstForf CumEstForf
1 2001 1000 1000
1 2002 500 1500
1 2003 700 2200
2 2001 1500 1500
2 2002 600 2100
2 2003 500 2600

What I would like is to create a field called CumEstForf
that would cumulate EstForf within each GrantID by Fiscal
Year. Can someone provide the SQL text to get this query
to work?

Thanks!
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
Sum([Self].[EstForf]) AS [CumEstForf]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[GrantID] = [Self].[GrantID]
WHERE
[Self].[FiscalYr] <= [Your Table].[FiscalYr]
GROUP BY
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf]

Another approach might be:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
(SELECT
Sum([Self].[EstForf])
FROM
[Your Table] AS [Self]
WHERE
[Self].[GrantID] = [Your Table].[GrantID]
AND
[Self].[FiscalYr] <= [Your Table].[FiscalYr]) AS [CumEstForf]
 
Your SQL statement worked perfectly, but I asked the wrong
question:

What I'm really looking for is a way to calculate the
difference in EstForf from year to year for each GrantID
grouping.

When completed, the data should look as such:

GrantID FiscalYr EstForf Diff
Any suggestions for that one?

-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
Sum([Self].[EstForf]) AS [CumEstForf]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[GrantID] = [Self].[GrantID]
WHERE
[Self].[FiscalYr] <= [Your Table].[FiscalYr]
GROUP BY
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf]

Another approach might be:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
(SELECT
Sum([Self].[EstForf])
FROM
[Your Table] AS [Self]
WHERE
[Self].[GrantID] = [Your Table].[GrantID]
AND
[Self].[FiscalYr] <= [Your Table].[FiscalYr]) AS [CumEstForf]

Here's my data:

GrantID FiscalYr EstForf CumEstForf
1 2001 1000 1000
1 2002 500 1500
1 2003 700 2200
2 2001 1500 1500
2 2002 600 2100
2 2003 500 2600

What I would like is to create a field called CumEstForf
that would cumulate EstForf within each GrantID by Fiscal
Year. Can someone provide the SQL text to get this query
to work?

Thanks!
Click to expand...


.
Click to expand...
 
In that case, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf]-
Nz((SELECT
TOP 1
[Self].[EstForf]
FROM
[Your Table] AS [Self]
WHERE
[Self].[GrantID] = [Your Table].[GrantID]
AND
[Self].[FiscalYr] < [Your Table].[FiscalYr]
ORDER BY
[Self].[FiscalYr] DESC),
[Your Table].[EstForf]) AS [Diff]
FROM [Your Table];

Your SQL statement worked perfectly, but I asked the wrong
question:

What I'm really looking for is a way to calculate the
difference in EstForf from year to year for each GrantID
grouping.

When completed, the data should look as such:

GrantID FiscalYr EstForf Diff
Any suggestions for that one?

-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
Sum([Self].[EstForf]) AS [CumEstForf]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[GrantID] = [Self].[GrantID]
WHERE
[Self].[FiscalYr] <= [Your Table].[FiscalYr]
GROUP BY
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf]

Another approach might be:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
(SELECT
Sum([Self].[EstForf])
FROM
[Your Table] AS [Self]
WHERE
[Self].[GrantID] = [Your Table].[GrantID]
AND
[Self].[FiscalYr] <= [Your Table].[FiscalYr]) AS [CumEstForf]

Here's my data:

GrantID FiscalYr EstForf CumEstForf
1 2001 1000 1000
1 2002 500 1500
1 2003 700 2200
2 2001 1500 1500
2 2002 600 2100
2 2003 500 2600

What I would like is to create a field called CumEstForf
that would cumulate EstForf within each GrantID by Fiscal
Year. Can someone provide the SQL text to get this query
to work?

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
In that case, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf]-
Nz((SELECT
TOP 1
[Self].[EstForf]
FROM
[Your Table] AS [Self]
WHERE
[Self].[GrantID] = [Your Table].[GrantID]
AND
[Self].[FiscalYr] < [Your Table].[FiscalYr]
ORDER BY
[Self].[FiscalYr] DESC),
[Your Table].[EstForf]) AS [Diff]
FROM [Your Table];

Your SQL statement worked perfectly, but I asked the wrong
question:

What I'm really looking for is a way to calculate the
difference in EstForf from year to year for each GrantID
grouping.

When completed, the data should look as such:

GrantID FiscalYr EstForf Diff
Any suggestions for that one?

-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
Sum([Self].[EstForf]) AS [CumEstForf]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[GrantID] = [Self].[GrantID]
WHERE
[Self].[FiscalYr] <= [Your Table].[FiscalYr]
GROUP BY
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf]

Another approach might be:

SELECT
[Your Table].[GrantID],
[Your Table].[FiscalYr],
[Your Table].[EstForf],
(SELECT
Sum([Self].[EstForf])
FROM
[Your Table] AS [Self]
WHERE
[Self].[GrantID] = [Your Table].[GrantID]
AND
[Self].[FiscalYr] <= [Your Table].[FiscalYr]) AS [CumEstForf]

Here's my data:

GrantID FiscalYr EstForf CumEstForf
1 2001 1000 1000
1 2002 500 1500
1 2003 700 2200
2 2001 1500 1500
2 2002 600 2100
2 2003 500 2600

What I would like is to create a field called CumEstForf
that would cumulate EstForf within each GrantID by Fiscal
Year. Can someone provide the SQL text to get this query
to work?

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top