Full Version: Help With Adding In A Query (i Think!)
ImStupid
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!

Cheers Mundo all
theDBguy
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...
ImStupid
Oh nuts, I knew I would forget something - its the latest version of Office (if that helps!!)

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
theDBguy
Hi,

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

Just my 2 cents...
ImStupid
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!
theDBguy
Hi,

QUOTE (ImStupid @ May 12 2012, 08:33 AM)
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!

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...
ImStupid
Sorry, you have totally lost me now! Make a new Query - erm help, what do I put where? Told you I was a newbie!
theDBguy
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...