I work for a non-profit and we have a program where we take our clients out to do small community service work and in exchange they are paid for their time working. The issue that I am running into right now with Microsoft Access is that I want to create a query that allows us to check the number of times any particular client has worked in a particular Calendar year.

We use two linked tables, one of which is the master list with client information & the other is the list of times they have put in for selection on that particular day (we usually have ~40 people show up but can only take 20 a day). So at the moment we use a Yes/No data type on the list of times they have put in for selection to keep track of this information. The column is called "Unused Surplus", just for the sake of providing as much information as possible.

My goal is to have a query running that shows the first name, last name, and # of times worked for a client within a calendar year. Any assistance would be hugely appreciated.

*I have tried to create a query using : "Times Worked: Sum(IIf([Unused Surplus]="No",1,0))" Just wasn't successful unfortunately.

CodePudding user response：

If [Unused Surplus] is Yes/No field type, don't enclose "No" in quote marks. Using quote marks defines a literal string, not a Boolean constant. Boolean constants are Yes, No, True, False. and have values of -1 and 0. Consider:

Times Worked: Sum(IIf([Unused Surplus]=No,1,0))

Times Worked: Sum(IIf([Unused Surplus]=False,1,0))

Times Worked: Sum(IIf([Unused Surplus]=0,1,0))

Times Worked: Sum(IIf(Not [Unused Surplus],1,0))

If you really want to count the Yes values, consider:

Times Worked: Abs(Sum([Unused Surplus]))