How to Sum and Divide two columns in a query

[FaurKris]

I found that there is something like Sum([col 1]), and I get that....

I have two columns that are populated via check boxes on the form called 'Sent' and 'Signed'. Once checked, the value in the table is a -1.

I need to divide the total sums to get a percentage of signed vs. not signed.

In Access, what would be the formula I enter in the query field (named %Completed) I made to display the answer? I tried:

%Completed:Sum([Signed])/Sum([Sent])

That doesn't work!

There is a need to display the %Compeleted result on the form. The result should be the % for ALL items on the form, so no matter which Supplier I scroll to, the % value does not change. And as I select and/or deselect between the Signed and Sent check boxes on each supplier, the % will update.

Is this confusing??

[NeoPa]

FaurKris: Is this confusing??

Absolutely (You asked)! I'm afraid you started somewhere in the middle and never got around to telling us much about the query you're working on.

Is it a GROUP BY query?

It would probably help if you posted the SQL for it to give us a clue as to what we're working with.

[FaurKris]

Absolutely (You asked)! I'm afraid you started somewhere in the middle and never got around to telling us much about the query you're working on.

Is it a GROUP BY query?

It would probably help if you posted the SQL for it to give us a clue as to what we're working with.

I'm very Green when it comes to Access.....

Maybe if I try to explain it differently???

I am querying customers information on a form. The form simply shows their contact information and allows me to add/edit the information. Then of course I can plot a report!

Well, I want to add to my form - a reference for me.

What I want to add are two check boxes and a text box which will display a math formula.

One check box is to indicate if I have sent my customer a notice. The other check box is to indicate if that same notice was sent back to me and signed.

The formula I need to display is the percentage of returned notices (Signed/Sent).

Just like many forms, I can use the scroll button on my mouse and scroll thru the whole list of my customers. As I do that, I can see which ones I have sent a notice to and which ones have returned their notice via the check boxes. And these I already have which are working just fine.

What I need help with is being able to see the percentage of returned notices (text box?) regardless of the customer I am viewing on the form.

I DO NOT NEED TO PRINT THIS INFORMATION TO THE REPORT. For my viewing pleaseure only.

Currently I do this in excel, which is a pain because I am having to update two lists of customer information that way....


I dont know how to post a SQL or what that really is... lol.

Any clearer?? I hope so!

[NeoPa]

That does make it a little clearer. Shame about the SQL, but let's see what we can do without it for now.

Forms have Header and Footer sections as well as the Detail section you're probably used to working within (even if you've never noticed it). If your form shows no Header or Footer section when in Design View, then it is probably a simple case of that not showing. To show these sections toggle Form Header/Footer from the View menu.

I would create a TextBox control in your (now visible) Footer section and set its Control Source to something like :

=Sum(IIf([ReturnedNotices],1,0)/(Sum([Sent],1,0)+IIf(Sum([Sent],1,0)=0,1,0))

I don't know the names of your controls so you may need to substitute your names for these.

[FaurKris]

That does make it a little clearer. Shame about the SQL, but let's see what we can do without it for now.

Forms have Header and Footer sections as well as the Detail section you're probably used to working within (even if you've never noticed it). If your form shows no Header or Footer section when in Design View, then it is probably a simple case of that not showing. To show these sections toggle Form Header/Footer from the View menu.

I would create a TextBox control in your (now visible) Footer section and set its Control Source to something like :

=Sum(IIf([ReturnedNotices],1,0)/(Sum([Sent],1,0)+IIf(Sum([Sent],1,0)=0,1,0))

I don't know the names of your controls so you may need to substitute your names for these.

Cool! You understand what I need to do...

When I paste the code (after right clicking the text box/Properties/Data/Control Source/pasting the code in the empty box and clicking ok) I get an error saying "The expression you entered has a function containing the wrong number of arguments."

I should say that I deleted the "ReturnedNotice s" text that was already in the control source dialog box before pasting your code.

I tried it without deleting the existing text and was told that I am "missing an operand, invalid character, missing quotes."

What did I do wrong???


And thanks for helping!!!

[FaurKris]

Attached screen shot. Maybe this will help too?

[FaurKris]

Didn't know it would reduce the picture by so much....

[NeoPa]

Unfortunately, the resolution is too low for me to get much of meaning I'm afraid.

What I can see of the second screenshot is that it says :

Something=Blahblahbla

instead of :

=Blahblahbla

as shown in my post.

Perhaps posting the actual text of what you tried would help us to make progress.

[NeoPa]

Forget that last post. I so rarely work in Design View I'm a little rusty.

You probably want :

Completed: Sum(IIf([ReturnedNotices],1,0))/(Sum(IIf([Sent],1,0))+IIf(Sum(IIf([Sent],1,0))=0,1,0)))

PS. Updated as I found the formula had a bunch of mistakes in it anyway. Apologies for my sloppy post.

[FaurKris]

Unfortunately, the resolution is too low for me to get much of meaning I'm afraid.

What I can see of the second screenshot is that it says :

Something=Blahblahbla

instead of :

=Blahblahbla

as shown in my post.

Perhaps posting the actual text of what you tried would help us to make progress.

This is what I tried first, "=Blahblahb la "

Completed: Sum(IIf([Signed],1,0))/(Sum(IIf([Sent],1,0))+IIf(Sum(IIf([Sent],1,0))=0,1,0)))

I am now only getting an operand error. I'm missing a parenthesis somewhere? I'm trying to understand exactly what the formula is doing so I can figure it out.... lol.

[NeoPa]

I've got it now. Doh! There was an extra closing parenthesis even in the last version.

Try this :

Completed: Sum(IIf([Signed],1,0))/(Sum(IIf([Sent],1,0))+IIf(Sum(IIf([Sent],1,0))=0,1,0))

It's basically saying check each value of [Signed] and return a 1 where checked and a 0 where unchecked. Next Sum all these values for the value A. It's also saying the same except with [Sent] for B. From there the formula is :

Completed: A/(B+IIf(B=0,1,0))

[FaurKris]

I've got it now. Doh! There was an extra closing parenthesis even in the last version.

Try this :

Completed: Sum(IIf([Signed],1,0))/(Sum(IIf([Sent],1,0))+IIf(Sum(IIf([Sent],1,0))=0,1,0))

It's basically saying check each value of [Signed] and return a 1 where checked and a 0 where unchecked. Next Sum all these values for the value A. It's also saying the same except with [Sent] for B. From there the formula is :

Completed: A/(B+IIf(B=0,1,0))

I appreciate all your help!

I am still getting an operand type error regardless of what I do....

I'm unsure exactly how to procede! I've probably got something else wrong somewhere...


Thanks again!

[FaurKris]

I've got it now. Doh! There was an extra closing parenthesis even in the last version.

Try this :

Completed: Sum(IIf([Signed],1,0))/(Sum(IIf([Sent],1,0))+IIf(Sum(IIf([Sent],1,0))=0,1,0))

It's basically saying check each value of [Signed] and return a 1 where checked and a 0 where unchecked. Next Sum all these values for the value A. It's also saying the same except with [Sent] for B. From there the formula is :

Completed: A/(B+IIf(B=0,1,0))

This is what comes up when I enter the code directly into the Query. Vendor Data is my Table...

[NeoPa]

How about you pop the actual formula you used as text in the post. Trying to work from a tiny picture of an error message pop-up is not good.

[FaurKris]

Sorry. I used teh exact code you posted.

Completed: Sum(IIf([Signed],1,0))/(Sum(IIf([Sent],1,0))+IIf(Sum(IIf([Sent],1,0))=0,1,0))

I also tried Googling and making small changes and such. I even verified the names of the columns. There is Sent, Signed and Completed. So to get a percent completed, I need to sum and divide Signed by Sent.

I feel like a 4th grader! Haha! Too bad I cant just use Excel...