在报告中计算从Grand Total中减去一个“组”总数 [英] Calculation in report to subtract one 'group' total from Grand Total

问题描述

我正在使用Access 2003中的库存数据库。我正在编写一份报告，我可以在提交州销售税文件时打印。我在本报告中需要的数字是总销售额（不含税），税收销售额以及包含税的总销售额，这些数据均在特定时间段内收集。


我有一个查询（qrySalesTax）来查找指定日期范围内的所有销售额，同时计算总销售额和包含税的总销售额。我有一个基于Queary的报告，按类别分组，然后按月分组，每个分组的总数和整个日期范围的总毛额。


我想要什么要做的是从总销售总额中减去服装类别的总销售额，以获得税收销售额。


我尝试创建一个单独的查询来计算服装销售总额，然后将其包含在qrySalesTax中，以便在查询中单独列出总服装销售额，但查询重叠我和其他类别交易在同一条线上获得了服装交易的自行车清单，所以我放弃了这个想法。我还使用了sepearate查询中的Total Clothing Sales作为销售税报表页脚部分中未绑定框的控制源，但在预览中查看时，该字段仅显示#Error。


对于如何做到这一点的任何建议将不胜感激。你可能会告诉我这个程序比较新。此外，我还没有真正理解代码，所以如果我需要使用代码来促进这一点，我将需要一些非常详细的指导。感谢您对此的所有帮助！

I am working on an Inventory Database in Access 2003. I am working on a report that I could print when its time to file our State Sales Tax paperwork. The figures I need for this report are Total Sales(not including tax), the Amount of Sales Subject to Tax, and the Total Sales Including Tax, all gathered for a specific time period.

I have a query (qrySalesTax) to find all sales within a specified date range that also calculates the Total Sales and the Total Sales Including Tax. I have a report based on the Queary that is grouped by Category, then by Month, with totals for each grouping and a Gross Total for the entire date range.

What I would like to do is to subtract the Total Sales for the category Clothing from the Gross Total Sales to arrive at the Amount of Sales Subject to Tax.

I tried creating a seperate query to calculate the Total Clothing Sales and then including it in qrySalesTax So that the Total Clothing Sales were listed seperately in the query, but the queries ''overlapped'' and I got cycling listings for the clothing transactions on the same lines as the other category transactions so I abandonded this idea. I also used the Total Clothing Sales from the sepearate query as a control source for an unbound box in the footer section of the Sales Tax Report but when viewed in preview the field had only #Error displayed.

Any advice for how to do this would be appreciated. As you can probably tell I''m relatively new to this program. Also I have no real understanding of code yet so if I need to use code to facilitate this I will need some very detailed guidance. Thanks for all your help with this!

推荐答案

您的问题只能通过分组来解决。两个查询在相同的字段上。

对于某个类别没有找到数据（没有销售）可能会出现问题。

这是已解决的问题。通过使用包含所有和外连接的tblCategories与持有税额的表格。制作tblCategoriesMaster，将显示缺少数量的零'。


获得想法？


Nic; o）

You''re problem can only be solved by "Grouping" both queries on the same fields.
There might be a problem when for a certain Category no data is found (no sales).
This is "solved" by using a tblCategories that holds all and "outerjoin" that with the table holding the tax amounts. Making tblCategories "Master", will show zero''s for the missing amounts.

Getting the idea ?

Nic;o)

Nic; o）


我再次缺乏训练。你建议我分组两个队列在同一个领域。你如何组成一个队列？


为了澄清两个队列都基于相同的字段，类别字段中只有一个不同的条件。 （我实际上创建了一个genaric queary来查找我想要的所有字段的所有类别;然后我保存为创建一个精确的副本。然后我修改了Category类别中的标准只有一件衣服而另一件不是衣服。我还添加了在仅限服装查询的所有计算字段中的前缀CLO。


根据我对您的回复的理解，我将创建第三个查询以将报告基于已分组两个查询在相同的字段上。


如果某个类别没有销售，请回复您对潜在问题的评论，现在tblCat是我存储类别和税率信息的地方所以我不知道如何将它们加入到外连接中。将表连接到自身似乎是多余的。我的想法是，因为它们在同一个表中，所以''join''已经存在。是吗对吗？


我真的很感谢你的帮助！

Nic;o)

My lack of training is shining through again. You suggest that I "grouping" both quearies on the same field. How do you group in a queary?

Just for clarification both quearies are based on the same fields, there is just a different criteria in the Category field. (I actually created a genaric queary to find all categories for all the fields that I wanted; then I saved as to create an exact copy. Then I modified the criteria in the Category fields one only clothing and the other not clothing. I also added the prefix CLO to all the calculated fields in the Clothing only query.

From my understanding of your reply, I would create a third query to base the report on that ''grouped'' both queries on the same fields.

In response to your comments on the potential problems if a category has no sales, right now tblCat is where I am storing the Category and the Tax Rate Information so I don''t know how I would join them in an outer join. It seems redundant to join a table to itself. My thought would be that since they are in the same table this ''join'' already exists. Is that correct?

I really appreciate your help with this!

GroupBy查询可以是通过按下E来创建查看按钮。

新的行将出现在持有带有GroupBy的组合框的标准之上。

所有分组字段应该有GroupBy，但是你的金额应该保持Sum。

这样你就得到了GroupBy的每个组合。字段总和。

仅使用类别为每个类别提供一笔金额，添加一个额外的字段将拆分进一步求和。

只是试着看看效果。


通常需要更多的数据，但请记住，组合框的值总是需要被选中（也称为聚合函数，如Sum，Avg，Max，First等。


Nic; o）

GroupBy queries can be created by pressing the "E" looking button.
A new line will appear above the criteria holding a combobox with "GroupBy".
All "groupingfields" should have GroupBy, but your amount should hold "Sum".
Thus you''ll get for each combination of the "GroupBy" fields the sum.
Only using Categories give a sum per Category, adding an additional field will "split" the summing further.
Just try to see the effect.

Often more data is needed, but keep in mind that always a value from the combobox needs to be selected (also called "aggregation function) like Sum, Avg, Max, First, etc.

Nic;o)

这篇关于在报告中计算从Grand Total中减去一个“组”总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！