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]

@NeoPa
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]

@NeoPa
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 "ReturnedNotices" 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]

@NeoPa
This is what I tried first, "=Blahblahbla "

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]

@NeoPa
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]

@NeoPa
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...

[NeoPa]

Are you sure this is the actual code that gave that error?

I've gone through it again with a fine-toothed comb and it looks perfect to me. If that's the exact code then I don't know what it's complaining about.

[FaurKris]

Within my form:
I right click the text box for the field "Completed".
Enter into Properties.
Click Data.
Click the 3 little dots next to Control Source.
Delete the word "Completed" that is in the dialog box and paste your code - compeltely.
Then I click OK.

If I click off the Control Source slection, a dialog pops up saying "The expression you entered has invalid syntax. You may have entered an operand without an operator."

If I simply close the properties menu, the code I pasted in there is erased or something. I know this because when it didn't work, I reopened properties and the code is gone...


Now, because the check boxes produce a negative 1 within the table/query, I altered the formula to show that too...

Like I said, I do appreciate your assistance. If it cannot be solved I will just have to try and think of another way...

[NeoPa]

I'll tell you what. Drop a copy of your database as an attachment and I'll look at it for you. I'll need instructions as to where to look of course. I'll also include some instructions I've used before indicating how best to go about attaching a database.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[FaurKris]

@NeoPa
Form. At the bottom, Completed.
Although the form, in this state, has all the information removed, you can still scroll thru. There are already some check marks... Filling in the Completed box with the percentage of Sent AND signed is the goal. So when I begin, most of the customers are checked with a SENT. The Completed percentage will be 0%. As the SIGNED copies come back and I check that box, the percentage will rise.

A just thought of another caveat! Darnit!!

Each Desitnation Plant is seperate. So the Sent, Signed, and Completed fields are dependant on the plant... Example, I may have 47 plant ONE and only 3 plant FOUR. So the 'Completed' feild needs to somehow read which 'FIS Destination Plant' is selected and show the percentage just for those.... Is that even possible? I would guess there aould be an additional IF/THEN statement somewhere.

This way, I can see a percentage of Signed for each of the 6 plants...

Make sense? Sorry, this last part just occured to me... lol.

[NeoPa]

I'll download when I get home and then all you say will make much better sense to me. I'll post later with any thoughts or help I come up with.

[NeoPa]

A few things to start with are :

1. The footer section, as with the header section when used, is for unbound controls. Controls that can reflect totals of various items across many records. The detail section is for showing details of the current record.
2. The [Completed] control cannot be populated by the query. I had no idea you were trying to work along such lines (having detailed info along with totals info in the same query). It really makes no logical sense at all I'm afraid. We will need to look at this again from scratch. Probably some AfterUpdate processing but we'll get to that when I've seen your reaction to this post.
3. As for your caveat, this makes life a little more complicated it's true, but probably handlable as we know the requirement. I'd suggest the plant name be redisplayed in the Footer along with the calculated field though. As a more friendly interface rather than program logic.

I'm afraid what I saw of your database rather makes what was discussed before all pretty redundant. This is not the end of the world though. We can rebuild it, but it would be somewhat different that's all (and possibly take a bit longer).

Let me know what you think of what I've said so far.