尝试计算查询以提取报告 [英] Trying to calculate on query to pull a report
问题描述
Hello All，我一直试图根据我昨天收到的回复来解决这个问题，但是我觉得我在这个过程中迷失了。我有一个表单，我有6个计算字段加上信息字段，如日期，类型等。但是，我现在倾向于使用查询这些字段来计算而不是在表单上计算。因此，我只是在表格中计算出以下字段;
表格字段1.名称GST = [每单位成本] * [数量] * 0.1
表格字段2.每件商品名称GST = [消费税] / [数量]
表格字段3.名称每件商品运费= [运费] / [商品及服务税总额]
表格字段4.名称总成本订单bf GST = [每单位成本] * [数量]
表格字段5.名称总GST包含= [总成本或商品及服务费用] + [消费税]
表格字段6.名称总运费+消费税= [运费] + [总消费税含税]
现在这些工作在表格上但不是查询。从我昨天收到的另一个查询答案建模
Hello All, I have been trying to work this out myself based on a response that I received yesterday which worked however I think I am lost in the process. I have a form which I have 6 calculated fields plus information fields like date, type etc. However, I now am gravitating to using the Query for these fields to calculate instead of calculating on the form. So simply the fields below are what I have calculated on the form;
Form Field 1. Name GST =[cost per unit]*[Qty]*0.1
Form Field 2. Name GST Per Item = [GST]/[Qty]
Form Field 3. Name Freight Cost per item =[freight cost]/[Total GST incl]
Form Field 4. Name Total cost order bf GST =[cost per unit]*[qty]
Form Field 5. Name Total GST incl = [total cost oder bf GST&freight]+[GST]
Form Field 6. Name Total Freight+GST =[freight cost] + [Total GST incl]
Now these work on the form but not on the query. modelled from an answer i received yesterday for another query
推荐答案
Heart01，
听起来你有点过于野心勃勃地直接编辑sql。不是太野心勃勃，而是太快了。慢慢来，我花了很长时间才能达到可以编写sql命令而无需拙劣工作的程度。
让我们为你编写sql代码...在上面输入你输入的sql命令并在Access查询编辑器中查看它。你会看到如何在不输入所有sql代码的情况下完成同样的事情。您将看到一列如下所示：Expr1：[Qty] - [QtyOut]。
您还会看到分组行可见，此查询已分组通过类型柱。通过右键单击查询编辑器的下半部分来打开和关闭该分组;您可以在此处选择分组，平均值，最小值，最大值和分组附带的其他选项。
在SQL代码视图和GUI查询编辑器之间来回切换（通过右键单击查询编辑器的上半部分）。在一个地方做一个改变，看看它是如何改变另一个地方的外观。你会学到很多关于SQL的知识。
我认为你会发现将所有这6个计算添加到你的查询中真的很轻松。不是一开始，但很快。试试吧。
Jim
Heart01,
It sounds like you are being a little too ambitious with jumping in the editing the sql directly. Not too ambitious, but too quick. Take it slow, it took me a very long time to reach the point where I could code the sql commands without botching the job.
Let Access code the sql for you ... take that sql command you typed above and look at it in the Access query editor. You''ll see there how you can accomplish the same thing without typing all that sql code. You''ll see one column looks like this: Expr1:[Qty]-[QtyOut].
You''ll also see the Grouping row is visible, and this query is grouped by the "Type" column. You turn that grouping on and off by right-clicking in the bottom half of the query editor; that''s where you can select Sums, Averages, Min, Max and other options that come with Grouping.
Go back and forth between the SQL code view and the GUI Query editor (by right-clicking in the top half of the query editor). Make a change in one place and go see how that changed the appearance in the other. You''ll learn a lot about SQL like that.
I think that you''ll see that adding all 6 of those calculations to your query will really be a breeze. Not at first, but soon. Try it.
Jim
吉姆，我知道我的情况很好，并且深入了解我。在6小时的大部分时间里，你一直在做你现在建议的事情，我显然不明白我在做什么。看来信息刚刚浮现在我脑海中。我在查询的SQL中有以下内容：
Hi Jim, I understand that I am in the thick of it and jumpig in at the deep end..I have been doing what you suggested now for the better part of 6 hours and I am clearly not understanding what Im doing. It appears info is just now floating around in my head. I have in the SQL of the query the following;
嗨Deb
聚合函数可能会变得复杂，当我有时间时，我必须将其中一些规则放在一起以获得Insight。
有一个您必须记住的一些基本规则。
- SELECT中未聚合（求和，计算等）的任何字段都必须包含在Group By子句中。
- 所有聚合字段将按所有非聚合字段分组。我的意思是，如果我运行以下查询..
Hi Deb
Aggregate functions can get complicated, I''ll have to put some rules together one of these days for an Insight when I get the time.
There are a few basic rules that you have to keep in mind.
- Any field in the SELECT that is not aggregated (summed, counted, etc.) must be included in the Group By clause.
- All aggregated fields will grouped by all non aggregated fields. By this I mean if I run the following query ..
这篇关于尝试计算查询以提取报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
