Why am I asked to enter parameter value?

[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

Fieldercent: [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

 

[Tom Ellison]

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

Fieldercent: [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

 

[Tom Ellison]

Dear JZ:

I didn't review your previous answers, but hope I can offer something.

You create a column in this table which is aliased "SumOfHoursWorked1".

You then attempt to use this inside the query.

This will never work. A query isn't "executed from top to bottom". Any
column you create inside a query cannot be referenced within the same query.
It CAN be referenced from subsequent queries which use this query.

So, you have two choices. You can replace all references to
SumOfHoursWorked1 (other than the AS SumOfHoursWorked1) with:

Sum(MainProjectTable.HoursWorked)

which is how it is derived. Or, you can derive this column in a query, then
write another query that references this. I would usually use the former.

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

Please let me know if this helped, and if you need any other assistance.

Tom Ellison

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

Fieldercent: [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