Why am I asked to enter parameter value? (Second try)

  • Thread starter Thread starter JZ
  • Start date Start date
J

JZ

I posted this a couple of days ago and we were unable to resolve the
issue. Anyone else want to give it a try?

My query calculates the percent of hours worked on a project compared
to total hours worked. Here are the relevant fields:

Field: HoursWorked
Table: MainProjectTable
Total: Sum

Field: SumofHoursWorked
Table: qryTtlHrs

Field:Percent: [SumOfHoursWorked1]/qryTtlHrs!SumOfHoursWorked

When I run the query I'm asked to enter parameter value for
[SumOfHoursWorked1] even though the criteria row is empty for all of
these fields. I hit OK and the query runs perfectly. How do I get rid
of the Enter Parameter Message Box? Any help would be much
appreciated. Thanks in advance.

Here's the SQL:
SELECT DISTINCTROW ProjectList.[Primary Key],
MainProjectTable.ProjectID, ProjectList.AssignedEngineer,
Sum(MainProjectTable.HoursWorked) AS SumOfHoursWorked1,
qryTtlHrs.SumOfHoursWorked,
[SumOfHoursWorked1]/qryTtlHrs!SumOfHoursWorked AS [Percent]
FROM (qryTtlHrs INNER JOIN ProjectList ON
qryTtlHrs.EmployeeName=ProjectList.AssignedEngineer) INNER JOIN
MainProjectTable ON ProjectList.[Primary
Key]=MainProjectTable.ProjectID
GROUP BY ProjectList.[Primary Key], MainProjectTable.ProjectID,
ProjectList.AssignedEngineer, qryTtlHrs.SumOfHoursWorked,
[SumOfHoursWorked1]/qryTtlHrs!SumOfHoursWorked,
MainProjectTable.EmployeeName
HAVING (((MainProjectTable.EmployeeName)=[AssignedEngineer]));

-JZ
 
JZ said:
I posted this a couple of days ago and we were unable to resolve the
issue. Anyone else want to give it a try?

My query calculates the percent of hours worked on a project compared
to total hours worked. Here are the relevant fields:

Field: HoursWorked
Table: MainProjectTable
Total: Sum

Field: SumofHoursWorked
Table: qryTtlHrs

Field:Percent: [SumOfHoursWorked1]/qryTtlHrs!SumOfHoursWorked

When I run the query I'm asked to enter parameter value for
[SumOfHoursWorked1] even though the criteria row is empty for all of
these fields. I hit OK and the query runs perfectly. How do I get rid
of the Enter Parameter Message Box? Any help would be much
appreciated. Thanks in advance.

Here's the SQL:
SELECT DISTINCTROW ProjectList.[Primary Key],
MainProjectTable.ProjectID, ProjectList.AssignedEngineer,
Sum(MainProjectTable.HoursWorked) AS SumOfHoursWorked1,
qryTtlHrs.SumOfHoursWorked,
[SumOfHoursWorked1]/qryTtlHrs!SumOfHoursWorked AS [Percent]
FROM (qryTtlHrs INNER JOIN ProjectList ON
qryTtlHrs.EmployeeName=ProjectList.AssignedEngineer) INNER JOIN
MainProjectTable ON ProjectList.[Primary
Key]=MainProjectTable.ProjectID
GROUP BY ProjectList.[Primary Key], MainProjectTable.ProjectID,
ProjectList.AssignedEngineer, qryTtlHrs.SumOfHoursWorked,
[SumOfHoursWorked1]/qryTtlHrs!SumOfHoursWorked,
MainProjectTable.EmployeeName
HAVING (((MainProjectTable.EmployeeName)=[AssignedEngineer]));
Click to expand...
Hi JZ,

I did not go back and look at earlier post
so maybe already covered following?

You *can* "reuse" a SELECT clause alias
in the SELECT clause, but you cannot
use this alias in GROUP BY clause.

try

SELECT DISTINCTROW
ProjectList.[Primary Key],
MainProjectTable.ProjectID,
ProjectList.AssignedEngineer,
Sum(MainProjectTable.HoursWorked) AS SumOfHoursWorked1,
qryTtlHrs.SumOfHoursWorked,
[SumOfHoursWorked1]/qryTtlHrs.SumOfHoursWorked AS [Percent]
FROM
(qryTtlHrs
INNER JOIN
ProjectList
ON
qryTtlHrs.EmployeeName=ProjectList.AssignedEngineer)
INNER JOIN
MainProjectTable
ON ProjectList.[Primary Key]=MainProjectTable.ProjectID
GROUP BY
ProjectList.[Primary Key],
MainProjectTable.ProjectID,
ProjectList.AssignedEngineer,
qryTtlHrs.SumOfHoursWorked,
MainProjectTable.EmployeeName
HAVING
(((MainProjectTable.EmployeeName)=[AssignedEngineer]));
 
Thanks Gary and Tom,

It was actually a combination of your advice that fixed the problem. I
changed the alias to the Sum() function AND deleted it from the GROUP
BY clause. Much appreciated.

-JZ
 
Back
Top