Calculate multiple fields for one result

I have never created a single query that is this complicated.

I am lost as to what I am doing wrong. Anyone out there that could help would be greatly appreciated.

I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.

The current task at hand is to determine if each member has achieved or meet certain requirements each year.

They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?

I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.

So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.

So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F

I keep getting “You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)”

In this case it keeps complaining Callsign.

You did not enter an aggregate function in the TRANSFORM statement.



Here’s the statement I created:

SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal

FROM [Work Hours]

WHERE (((Year([Date Worked]))=(Year(Date()))))

GROUP BY [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].ReqSubTotal

HAVING (([Work Hours].SubMtg)=Sum((([Work Hours].[Work Code])=4) + (([Work Hours].[Work Code])=5) + (([Work Hours].[Work Code])=6) +(([Work Hours].[Work Code])=8) + (([Work Hours].[Work Code])=9))) AND

(([Work Hours].SubEvnt)=Sum((([Work Hours].[Work Code])=2) + (([Work Hours].[Work Code]) =12))) AND

(([Work Hours].SubNCO)=Sum(([NCOflag]=True)) AND

(([Work Hours].SubNet) =Sum((([Work Hours].[Work Code]) =10) +(([Work Hours].[Work Code]) =11) + (([Work Hours].[Work Code]) =14) +( ([Work Hours].[Work Code])=15))) AND

(([Work Hours].[ReqSubTotal]) =CBool(([SubMtg]>=8) And ([SubNet]>=5) And ([SubNCO]>=1) And ([SubEvnt]>=1)))

ORDER BY [Work Hours].Callsign;

It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don’t see what is wrong with it. :banghead:

Calculate multiple fields for one result