Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm trying to add a calculation to my query but I can't seem to get it to
work. I want to take [rtkProjectExpSummary.JTDRev]/[Profit] to get a
percentage but every time I try to put in a simple formula it won't run. Can
anyone show me how to add this to my query? I've listed the SQL below without
the formula that I can't get to work.

SELECT FormatProject(PR.prProject) AS [Project Number], PR.prName AS
[Project Name], PR.prProjMgr AS [Project Manager], PR.prStatus AS Status,
StdTrunc(Sum(rtkProjectExpSummary.JTDFeeBill)) AS [JTD Fee Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDConsBill)) AS [JTD Cons Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDReimbBill)) AS [JTD Reimb Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDOtherBill)) AS [JTD Other Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDTotBill)) AS [JTD Tot Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDRev)) AS [JTD Revenue],
StdTrunc(Sum([rtkProjectOH.JTDOH]+[rtkProjectLabSummary.JTDLaborCost]+[rtkProjectExpSummary.JTDReimbConsCost]+[rtkProjectExpSummary.JTDReimbOtherCost]+[rtkProjectExpSummary.JTDDirConsCost]+[rtkProjectExpSummary.JTDDirOtherCost]+[rtkProjectExpSummary.JTDIndCost]))
AS Spent, StdTrunc(Sum([rtkProjectExpSummary.JTDRev])-[Spent]) AS Profit

FROM (((PR INNER JOIN PR AS PRTask ON PR.prProject = PRTask.prProject) LEFT
JOIN rtkProjectLabSummary ON (PRTask.prProject =
rtkProjectLabSummary.Project) AND (PRTask.prTask =
rtkProjectLabSummary.Task)) LEFT JOIN rtkProjectExpSummary ON (PRTask.prTask
= rtkProjectExpSummary.Task) AND (PRTask.prProject =
rtkProjectExpSummary.Project)) LEFT JOIN rtkProjectOH ON (PRTask.prProject =
rtkProjectOH.Project) AND (PRTask.prTask = rtkProjectOH.Task)

WHERE (((PR.prTask)=' '))

GROUP BY PR.prName, PR.prProjMgr, PR.prStatus, PR.prProject

HAVING (((FormatProject([PR].[prProject]))="25102.00"));
 
Try the lomg way around by adding --
[rtkProjectExpSummary.JTDRev]/( StdTrunc(Sum([rtkProjectExpSummary.JTDRev])-
StdTrunc(Sum([rtkProjectOH.JTDOH]+[rtkProjectLabSummary.JTDLaborCost]+[rtkPr
ojectExpSummary.JTDReimbConsCost]+[rtkProjectExpSummary.JTDReimbOtherCost]+[
rtkProjectExpSummary.JTDDirConsCost]+[rtkProjectExpSummary.JTDDirOtherCost]+
[rtkProjectExpSummary.JTDIndCost])) *100
 
Do I just use what you you wrote or add that to my original SQL?

KARL DEWEY said:
Try the lomg way around by adding --
[rtkProjectExpSummary.JTDRev]/( StdTrunc(Sum([rtkProjectExpSummary.JTDRev])-
StdTrunc(Sum([rtkProjectOH.JTDOH]+[rtkProjectLabSummary.JTDLaborCost]+[rtkPr
ojectExpSummary.JTDReimbConsCost]+[rtkProjectExpSummary.JTDReimbOtherCost]+[
rtkProjectExpSummary.JTDDirConsCost]+[rtkProjectExpSummary.JTDDirOtherCost]+
[rtkProjectExpSummary.JTDIndCost])) *100

Secret Squirrel said:
Hello,

I'm trying to add a calculation to my query but I can't seem to get it to
work. I want to take [rtkProjectExpSummary.JTDRev]/[Profit] to get a
percentage but every time I try to put in a simple formula it won't run. Can
anyone show me how to add this to my query? I've listed the SQL below without
the formula that I can't get to work.

SELECT FormatProject(PR.prProject) AS [Project Number], PR.prName AS
[Project Name], PR.prProjMgr AS [Project Manager], PR.prStatus AS Status,
StdTrunc(Sum(rtkProjectExpSummary.JTDFeeBill)) AS [JTD Fee Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDConsBill)) AS [JTD Cons Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDReimbBill)) AS [JTD Reimb Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDOtherBill)) AS [JTD Other Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDTotBill)) AS [JTD Tot Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDRev)) AS [JTD Revenue],
StdTrunc(Sum([rtkProjectOH.JTDOH]+[rtkProjectLabSummary.JTDLaborCost]+[rtkPr
ojectExpSummary.JTDReimbConsCost]+[rtkProjectExpSummary.JTDReimbOtherCost]+[
rtkProjectExpSummary.JTDDirConsCost]+[rtkProjectExpSummary.JTDDirOtherCost]+
[rtkProjectExpSummary.JTDIndCost]))
AS Spent, StdTrunc(Sum([rtkProjectExpSummary.JTDRev])-[Spent]) AS Profit

FROM (((PR INNER JOIN PR AS PRTask ON PR.prProject = PRTask.prProject) LEFT
JOIN rtkProjectLabSummary ON (PRTask.prProject =
rtkProjectLabSummary.Project) AND (PRTask.prTask =
rtkProjectLabSummary.Task)) LEFT JOIN rtkProjectExpSummary ON (PRTask.prTask
= rtkProjectExpSummary.Task) AND (PRTask.prProject =
rtkProjectExpSummary.Project)) LEFT JOIN rtkProjectOH ON (PRTask.prProject =
rtkProjectOH.Project) AND (PRTask.prTask = rtkProjectOH.Task)

WHERE (((PR.prTask)=' '))

GROUP BY PR.prName, PR.prProjMgr, PR.prStatus, PR.prProject

HAVING (((FormatProject([PR].[prProject]))="25102.00"));
Click to expand...
Click to expand...
 
Just add -- include AS Percent to label the field of the output.

Secret Squirrel said:
Do I just use what you you wrote or add that to my original SQL?

KARL DEWEY said:
Try the lomg way around by adding --
[rtkProjectExpSummary.JTDRev]/( StdTrunc(Sum([rtkProjectExpSummary.JTDRev])-
StdTrunc(Sum([rtkProjectOH.JTDOH]+[rtkProjectLabSummary.JTDLaborCost]+[rtkPr
ojectExpSummary.JTDReimbConsCost]+[rtkProjectExpSummary.JTDReimbOtherCost]+[
rtkProjectExpSummary.JTDDirConsCost]+[rtkProjectExpSummary.JTDDirOtherCost]+
[rtkProjectExpSummary.JTDIndCost])) *100

Secret Squirrel said:
Hello,

I'm trying to add a calculation to my query but I can't seem to get it to
work. I want to take [rtkProjectExpSummary.JTDRev]/[Profit] to get a
percentage but every time I try to put in a simple formula it won't run. Can
anyone show me how to add this to my query? I've listed the SQL below without
the formula that I can't get to work.

SELECT FormatProject(PR.prProject) AS [Project Number], PR.prName AS
[Project Name], PR.prProjMgr AS [Project Manager], PR.prStatus AS Status,
StdTrunc(Sum(rtkProjectExpSummary.JTDFeeBill)) AS [JTD Fee Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDConsBill)) AS [JTD Cons Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDReimbBill)) AS [JTD Reimb Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDOtherBill)) AS [JTD Other Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDTotBill)) AS [JTD Tot Billing],
StdTrunc(Sum(rtkProjectExpSummary.JTDRev)) AS [JTD Revenue],
StdTrunc(Sum([rtkProjectOH.JTDOH]+[rtkProjectLabSummary.JTDLaborCost]+[rtkPr
ojectExpSummary.JTDReimbConsCost]+[rtkProjectExpSummary.JTDReimbOtherCost]+[
rtkProjectExpSummary.JTDDirConsCost]+[rtkProjectExpSummary.JTDDirOtherCost]+
[rtkProjectExpSummary.JTDIndCost]))
AS Spent, StdTrunc(Sum([rtkProjectExpSummary.JTDRev])-[Spent]) AS Profit

FROM (((PR INNER JOIN PR AS PRTask ON PR.prProject = PRTask.prProject) LEFT
JOIN rtkProjectLabSummary ON (PRTask.prProject =
rtkProjectLabSummary.Project) AND (PRTask.prTask =
rtkProjectLabSummary.Task)) LEFT JOIN rtkProjectExpSummary ON (PRTask.prTask
= rtkProjectExpSummary.Task) AND (PRTask.prProject =
rtkProjectExpSummary.Project)) LEFT JOIN rtkProjectOH ON (PRTask.prProject =
rtkProjectOH.Project) AND (PRTask.prTask = rtkProjectOH.Task)

WHERE (((PR.prTask)=' '))

GROUP BY PR.prName, PR.prProjMgr, PR.prStatus, PR.prProject

HAVING (((FormatProject([PR].[prProject]))="25102.00"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top