如何在查询中对两列进行求和和除 [英] How to Sum and Divide two columns in a query
问题描述
我发现有一些像Sum（[col 1]），我得到了....
我有两列通过复选框填充称为已发送和已签名的表单。选中后，表格中的值为-1。
我需要将总金额除以获得已签名与未签名的百分比。
在Access中，我在查询字段中输入的公式（命名为％Completed）是为了显示答案？我试过了：
％已完成：总和（[签字]）/总和（[已发送]）
那不行！
需要在表单上显示％Compeleted结果。结果应该是表单上所有项目的％，因此无论我滚动到哪个供应商，％值都不会更改。当我在每个供应商的签名和已发送复选框之间选择和/或取消选择时，％将更新。
这令人困惑吗？
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??
推荐答案
FaurKris ：这有点令人困惑吗？
FaurKris: Is this confusing??
绝对（你问过）！我担心你是从中间的某个地方开始的，而且从来没有告诉我们你正在进行的查询。
它是GROUP BY查询吗？ />
如果您发布了SQL，它可能会有所帮助，以便为我们提供有关我们正在使用的工作的线索。
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.
@NeoPa
我在访问时非常环保.....
也许如果我尝试以不同的方式解释???
我在表单上查询客户信息。表单只显示其联系信息，并允许我添加/编辑信息。当然，我可以绘制一份报告！
嗯，我想添加到我的表格 - 我的参考。
我是什么想要添加的是两个复选框和一个显示数学公式的文本框。
一个复选框表示我是否已向客户发送通知。另一个复选框用于指示相同的通知是否已发回给我并签名。
我需要显示的公式是退回通知的百分比（已签名/已发送）。 br />
就像许多表格一样，我可以使用鼠标上的滚动按钮并滚动浏览整个客户列表。当我这样做时，我可以看到哪些已发送通知，哪些已通过复选框返回通知。这些我已经有了正常工作。
我需要帮助的是能够看到退回通知的百分比（文本框？），无论我正在查看的客户在表格上。
我不需要将这些信息打印到报告中。我只能观看。
目前我在excel中这样做，这很痛苦，因为我不得不更新两个客户信息列表....
我不知道如何发布一个SQL或者那是什么......哈哈。
更清楚？我希望如此！
@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!
这确实让它更清晰一些。关于SQL的耻辱，但让我们看看我们现在能做什么。
表格有标题和页脚部分以及详细信息部分，您可能习惯于在其中工作（即使您从未注意到它）。如果您的表单在设计视图中没有显示页眉或页脚部分，则可能是一个不显示的简单案例。要显示这些部分，请从视图菜单中切换表单页眉/页脚。
我会在您的（现在）创建一个TextBox控件可见）页脚部分并将其控制源设置为：
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 :
这篇关于如何在查询中对两列进行求和和除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
