Running Total Cost

  • Thread starter Thread starter Brian T
  • Start date Start date
B

Brian T

Hello All,

I have a database that tracks projects. I have a table with inital details
of the project:

Project Number Title Budget
--------------------------------------------------
20100409 New Stairs $10,000

and a table with changes to the project:

Change # Desc. Cost
--------------------------------------------------------
1 Change to Fiberglass $2,500
2 Add concrete walk $ 500

How do I write a query that would show the new total cost to look like this:

Change # Desc. Cost Rev. Budget
 
UNTESTED UNTESTED UNTESTED
Assuming the table names are tblProj and tblProjMods --
SELECT [Project Number], Nz([Change #], 0) AS Change_Num, Nz([Title],
[Desc.] AS [Title or Description], Nz([Budget], 0) + Nz([Cost], 0) AS
Req_Funds, (SELECT Sum( Nz([Budget], 0) + Nz([Cost], 0) FROM FROM tblProj AS
[XX] LEFT JOIN tblProjMods AS [YY] ON [XX].[Project Number] = [YY].[Project
Number] WHERE [YY].[Change #] >= tblProjMods.[Change #]) AS [Rev. Budget]
FROM tblProj LEFT JOIN tblProjMods ON tblProj.[Project Number] =
tblProjMods.[Project Number]
GROUP BY [Project Number], [Change #], [Title], [Budget];
 
Karl,

Thanks for the help. I have changed the names to fit my structure:

SELECT [Project Number], Nz([CO Number], 0) AS Change_Num, Nz([Title],
[Desc.] AS [Description], Nz([Award Cost], 0) + Nz([Cost], 0) AS
Req_Funds, (SELECT Sum( Nz([Award Cost], 0) + Nz([Cost], 0) FROM Projects AS
[XX] LEFT JOIN ChangeOrders AS [YY] ON [XX].[Project Number] = [YY].[Project
Number] WHERE [YY].[CO Number] >= ChangeOrders.[CO Number]) AS [Rev. Budget]
FROM Projects LEFT JOIN ChangeOrders ON Projects.[Project Number] =
ChangeOrders.[Project Number]
GROUP BY [Project Number], [Change #], [Title], [Award Cost];

An error of a missing operator or syntax pops-up.


















KARL DEWEY said:
UNTESTED UNTESTED UNTESTED
Assuming the table names are tblProj and tblProjMods --
SELECT [Project Number], Nz([Change #], 0) AS Change_Num, Nz([Title],
[Desc.] AS [Title or Description], Nz([Budget], 0) + Nz([Cost], 0) AS
Req_Funds, (SELECT Sum( Nz([Budget], 0) + Nz([Cost], 0) FROM FROM tblProj AS
[XX] LEFT JOIN tblProjMods AS [YY] ON [XX].[Project Number] = [YY].[Project
Number] WHERE [YY].[Change #] >= tblProjMods.[Change #]) AS [Rev. Budget]
FROM tblProj LEFT JOIN tblProjMods ON tblProj.[Project Number] =
tblProjMods.[Project Number]
GROUP BY [Project Number], [Change #], [Title], [Budget];


--
Build a little, test a little.


Brian T said:
Hello All,

I have a database that tracks projects. I have a table with inital details
of the project:

Project Number Title Budget
--------------------------------------------------
20100409 New Stairs $10,000

and a table with changes to the project:

Change # Desc. Cost
--------------------------------------------------------
1 Change to Fiberglass $2,500
2 Add concrete walk $ 500

How do I write a query that would show the new total cost to look like this:

Change # Desc. Cost Rev. Budget
--------------------------------------------------------------------
1 Change to Fiberglass $2,500 $12,500
2 Add concrete walk $ 500 $13,000

Thanks
Click to expand...
Click to expand...
 
You omitted a closing parenthesis --
Nz([Title], [Desc.] AS [Description],
Nz([Title], [Desc.]) AS [Description],

The GROUP BY must contain all selected fields that are not averaged,
counted, first, last, max, min, or summed.

Also if it is a calculated fields like -- Nz([CO Number], 0) it is placed
in the group by but not the alias (Change_Num). Additionally - Nz([Award
Cost], 0) + Nz([Cost], 0) in just this manner.

Your GROUP BY would look like this --
GROUP BY [Project Number], Nz([CO Number], 0), Nz([Title], [Desc.]),
Nz([Award Cost], 0) + Nz([Cost], 0), (SELECT Sum( Nz([Award Cost], 0) +
Nz([Cost], 0) FROM Projects AS [XX] LEFT JOIN ChangeOrders AS [YY] ON
[XX].[Project Number] = [YY].[Project Number] WHERE [YY].[CO Number] >=
ChangeOrders.[CO Number]);


--
Build a little, test a little.


Brian T said:
Karl,

Thanks for the help. I have changed the names to fit my structure:

SELECT [Project Number], Nz([CO Number], 0) AS Change_Num, Nz([Title],
[Desc.] AS [Description], Nz([Award Cost], 0) + Nz([Cost], 0) AS
Req_Funds, (SELECT Sum( Nz([Award Cost], 0) + Nz([Cost], 0) FROM Projects AS
[XX] LEFT JOIN ChangeOrders AS [YY] ON [XX].[Project Number] = [YY].[Project
Number] WHERE [YY].[CO Number] >= ChangeOrders.[CO Number]) AS [Rev. Budget]
FROM Projects LEFT JOIN ChangeOrders ON Projects.[Project Number] =
ChangeOrders.[Project Number]
GROUP BY [Project Number], [Change #], [Title], [Award Cost];

An error of a missing operator or syntax pops-up.


















KARL DEWEY said:
UNTESTED UNTESTED UNTESTED
Assuming the table names are tblProj and tblProjMods --
SELECT [Project Number], Nz([Change #], 0) AS Change_Num, Nz([Title],
[Desc.] AS [Title or Description], Nz([Budget], 0) + Nz([Cost], 0) AS
Req_Funds, (SELECT Sum( Nz([Budget], 0) + Nz([Cost], 0) FROM FROM tblProj AS
[XX] LEFT JOIN tblProjMods AS [YY] ON [XX].[Project Number] = [YY].[Project
Number] WHERE [YY].[Change #] >= tblProjMods.[Change #]) AS [Rev. Budget]
FROM tblProj LEFT JOIN tblProjMods ON tblProj.[Project Number] =
tblProjMods.[Project Number]
GROUP BY [Project Number], [Change #], [Title], [Budget];


--
Build a little, test a little.


Brian T said:
Hello All,

I have a database that tracks projects. I have a table with inital details
of the project:

Project Number Title Budget
--------------------------------------------------
20100409 New Stairs $10,000

and a table with changes to the project:

Change # Desc. Cost
--------------------------------------------------------
1 Change to Fiberglass $2,500
2 Add concrete walk $ 500

How do I write a query that would show the new total cost to look like this:

Change # Desc. Cost Rev. Budget
--------------------------------------------------------------------
1 Change to Fiberglass $2,500 $12,500
2 Add concrete walk $ 500 $13,000

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