Hi fine folks of Access Brain land!

I have been given this Db by my boss (who was given it by someone else!!! - can you see where this is going!) basically someone has built(!) this query that adds up how many times one of four yes/no boxes has been tick. So ultimately you can end up with an answer like Option 1 - 10 times, Option 2 - 6 times, Option 3 - 3 times, Option 4 - 0 times (the default value is 0) - this is taken from the datasheet view (I think that is the right word!)

What I have been asked to do is make these little, small figures add up so that in column number 5 (which doesn't exist at the moment) reads 19 (taking the figures from above)

I went into the design view and added Totalfigure: Nz([Option1],0) + Nz([Option2],0) + Nz ([Option3],0 +([Option4],0) into the Field section and .....

It didn't work!

please help a poor hassled worker keep his job!

Cheers Mundo all

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

That expression should work as long as you were referring to the field names in the table. If the other columns in your query are calculated expressions, then you will have to repeat them in your expression.

Just my 2 cents...

Oh nuts, I knew I would forget something - its the latest version of Office (if that helps!!)

Could you explain a little more about the expression please.

As much as I can work out, their is a piece of text in the design view in the field bit that says Terra: Sum(IIf([fd_Option1]=True,1,0)) it then has Expression in the Total box. This is repeated 3 other times for the other options.

I am hazarding a guess that this is what converts the Yes/No count to proper numbers!

Thanks muchly for any help

Hi,

Thanks for the additional information. Yes, instead of using [Terra] in your expression, try using the Sum(IIf(...)).

Just my 2 cents...

Thanks for your help, but I think I made a boob! I put the following in the Field part of the design view Expr1: Sum(IIf([fd_Option1]=True,1,0)+Sum(IIf([fd_Option2]=True,1,0)+Sum(IIf([fd_Option
3]=True,1,0)+Sum(IIf([fd_Option4]=True,1,0))
I also changed the Total box to Expression

I then get a response of cannot have aggregate function .....

What I have broken now!

Hi,


Sorry, didn't realize that you were using a Totals query. It might be simpler to leave your query alone and just create a new one based off it. You could then easily add your new column to it since it won't be a Totals query any more.

Just my 2 cents...

Sorry, you have totally lost me now! Make a new Query - erm help, what do I put where? Told you I was a newbie!

Okay, leave your original query alone and go to the Ribbon and click on the Create tab.

In the Queries group, click on the Query Design button.

From the Show Table dialog window, click on the Queries Tab and select the Totals query you were working with. Then click on the Add button and then the Close button.

In the Query Design Grid, drag all the fields from the table onto the grid below. Then, in a new column, add the expression you wanted to create earlier in the Totals query but this time, you should be able to use the field names instead of repeating the expression from the Totals query.

Hope that makes sense...

Just my 2 cents...