User input

[OtarBoy]

I am doing a query to return the average of a value. The average is obtained by dividing by the number of days worked which is entered by the user. But i did not create a variable for the DAY. So when i execute the query, it will prompt me twice to enter the value. How can i prevent this?

SELECT DISTINCT MONTH(DATE) AS [MONTH], YEAR(DATE) AS [YEAR],
(SELECT SUM(QUANTITY)/(10*DAY)*100 FROM EMPLOYEE2 WHERE NAME="A" AND DAY=[Enter the day for A:]) AS A,
(SELECT SUM(QUANTITY)/(9*DAY2)*100 FROM EMPLOYEE2 WHERE NAME="B" AND DAY2=[Enter the day for B:]) AS B,
(SELECT SUM(QUANTITY)/(9*DAY3)*100 FROM EMPLOYEE2 WHERE NAME="C" AND DAY2=[Enter the day for C:]) AS C,
(SELECT SUM(QUANTITY)/(7*DAY4)*100 FROM EMPLOYEE2 WHERE NAME="D" AND DAY2=[Enter the day for D:]) AS D,
(SELECT SUM(QUANTITY)/(8*DAY5)*100 FROM EMPLOYEE2 WHERE NAME="E" AND DAY2=[Enter the day for E:]) AS E,
(SELECT SUM(QUANTITY)/(10*DAY6)*100 FROM EMPLOYEE2 WHERE NAME="F" AND DAY2=[Enter the day for F:]) AS F,
(SELECT SUM(QUANTITY)/(8*DAY7)*100 FROM EMPLOYEE2 WHERE NAME="G" AND DAY2=[Enter the day for G:]) AS G
FROM EMPLOYEE2
WHERE MONTH(DATE)=[Enter the month:] AND YEAR(DATE)=[Enter the year:];

[andi_g69]

Is DAY a field in your table? In this case I suspect that Access is irritated by the field name DAY because there is an inbuild function with this name...

Use [DAY] instead as you did for month and year

[OtarBoy]

Originally posted by andi_g69
Is DAY a field in your table? In this case I suspect that Access is irritated by the field name DAY because there is an inbuild function with this name...

Use [DAY] instead as you did for month and year

No. DAY is not a field in my table. Cos the it is meant for user input and is empty at first. So i only declare it in the query. Can i do this? Thats y the query will prompt me twice for the input. Once for the value of DAY and another time for the parameter message.

[KnooKie]

A query within a query may solve this for you. You can carry the value from one query into the next. See your other post answer.