CODEWITHSUNDEEP
sqlms-access
Desert Spider
Desert Spider

Reputation: 778

Parameter Value Request on Query Refresh

I have an SQL that is working and returning the information i need from MS-Access, however it keeps asking for me to enter paramter values for "Annual Accrual" and "Used".

    SELECT SchedulingLog.UserID, SchedulingLog.Category AS Type, 
           Sum(SchedulingLog.Value) AS Used, SchedulingLog.Category, 
           qry_YearsOfService.[Annual Vac Days], [Annual Vac Days]+[Used] AS [Days Left]
    FROM   SchedulingLog INNER JOIN qry_YearsOfService ON 
           SchedulingLog.UserID = qry_YearsOfService.UserID
    GROUP BY SchedulingLog.UserID, SchedulingLog.Category, SchedulingLog.Category, 
             qry_YearsOfService.[Annual Vac Days], [Annual Vac Days]+[Used]
    HAVING (((SchedulingLog.Category) Like "Vac*"));

I dont want the option to enter parameter, it should just calculate. What am I missing?

Here is the SQL for the Years of Service query

    SELECT Roster.UserID, Roster.[WM DOH], Round((Date()-[WM DOH])/365,2) AS YearsOfService, 
Max (tblAccrual.WeeksAccrual) AS [Annual Accrual], Roster.Schedule, 
[Annual Accrual]*[Schedule] AS [Annual Vac Days]
    FROM Roster, tblAccrual
    WHERE ((([tblAccrual]![Years])<Round((Date()-[WM DOH])/365,2)))
    GROUP BY Roster.UserID, Roster.[WM DOH], 
Round((Date()-[WM DOH])/365,2), Roster.Schedule, [Annual Accrual]*[Schedule];

Upvotes: 1

Views: 118

Answers (3)

HansUp
HansUp

Reputation: 97131

I think you may have 2 different problems.

There is no field named "Annual Accrual" in that SQL. Perhaps it is included in qry_YearsOfService but spelled incorrectly(?). Show us the SQL for qry_YearsOfService if you can't find the problem.

Your GROUP BY clause references [Used], which is an alias defined in the field expression list. I suspect the db engine interprets it as a parameter in the GROUP BY because it is quite restrictive about allowing you to use field aliases later in a query. I think you need to change that. Show us brief samples from SchedulingLog and qry_YearsOfService if you want help sorting that out.

Apart from those issues, your field list includes SchedulingLog.Category twice, the first time aliased as Type and the second time unaliased. I don't understand the point of that. Including the same field twice may not create a problem, however Type might because it's a reserved word. If you keep that as the alias, enclose it in square brackets just to be safe.

Upvotes: 3

Beth
Beth

Reputation: 9617

I think it's asking for [Annual Accrual] because you have [Annual Accrual]*[Schedule] in the group by clause of your first query. Try taking that out.

Asking for [Used] for the same reason, as you have [Annual Vac Days]+[Used] referenced in the group by clause of your second query.

Upvotes: 4

Tom Collins
Tom Collins

Reputation: 4069

You're not using "Annual Accrual" anywhere in this query, so it must be called from qry_YearsOfService, or an even further nested query.

As for "Used", change your reference to Sum(SchedulingLog.Value) in the Group By.

Upvotes: 1

Related Questions