创建一个透视表查看报告??请打开灯.... [英] Creating a pivot table looking report??..please turn on the lights....
问题描述
我在过去几天一直在尝试创建一个查询，它将为我提供创建报告所需的所有值。
背景：
报告与我所做的任何事情都有所不同，但我希望那里的人有。在视觉上它看起来如下：
I have been trying for the last several days to create a query that will give me all of the values I need to create a report.
Background:
The report is different than anything I have done but I am hoping that for someone out there has. Visually it looks like the following:
产品名称_周1__周2__周3__周4__周5__经济GGP__PriorGWP
产品1________45______56_______0_______0_______12_______300_________250__
产品2________60______66_______0_______0_______11______ _250 _________ 225 __
产品3________45______56_______ ________ 0_______ 12______ _300_________225__
产品4________0_______0________0_______0_______0_______ _50__________40___
产品5________45______56_______0_______0_______12______ _412_________335__
产品6________0_______0________0_______0_______0_______ _41__________32___
等??
Product Name__Week 1__Week 2__Week 3__Week 4__Week 5__BudgetGWP__PriorGWP
Product 1________45______56_______0_______0_______12______ _300_________250__
Product 2________60______66_______0_______0_______11______ _250_________225__
Product 3________45______56_______0_______0_______12______ _300_________225__
Product 4________0_______0________0_______0_______0_______ _50__________40___
Product 5________45______56_______0_______0_______12______ _412_________335__
Product 6________0_______0________0_______0_______0_______ _41__________32___
Etc??
来自同一个表的所有值都是 NOT ：
All of the values are NOT coming from the same table:
列标题的值第1-5周来自一个名为？tblStaticAllForecast的表？ 。此表包含我的产品名称字段，而第1-5周是一个名为？FWeek的字段？用户可以在其中选择数字1-5的下拉列表。
列标题BudgetGWP的值来自一个名为？tblbudget？的表。此表包含我的产品名称字段。
列标题PriorGWP的值来自一个名为？tblPrior？的表。此表包含我的产品名称字段。
The values for column headings Weeks 1-5 are coming from one table called ?tblStaticAllForecast?. This table includes my Product Name field and Weeks 1 -5 is a field called ?FWeek? in which the user can select a drop down list of numbers 1-5.
The values for column heading BudgetGWP are coming from one table called ?tblbudget?. This table includes my Product Name field.
The values for column heading PriorGWP are coming from one table called ?tblPrior?. This table includes my Product Name field.
所有表都有一个名为？GWP的字段？本报告中的值基于。正如您在我显示的报告中看到的那样，我有一些场景，其中有些产品在几周内或仅在某些星期内没有销售。
我需要显示其中的所有产品在我的情况下有22个。
我还需要显示一个名为第1,2,3周的列标题，当时我只有一个名为FWeek的字段。
现状：
就像我说我一直在努力处理这类报道。我一直沿着交叉表查询道路来构建此报告的一部分。我想出了如何使用？In Clause？创建我需要的列标题（第1,2周，第1周），填充没有找到值的零（在我的GWP总和表达式中使用？Nz？）但是如何将所有产品列入清单中。以下是我到目前为止在交叉表查询中的内容：
All of the tables have a filed called ?GWP? of which the values in this report are based upon. As you can see on the report I displayed I have scenarios where there are products of which nothing was sold across the weeks or in certain weeks only.
I need to display all products of which in my case there are 22.
I also need to display a column heading called Week 1, 2, 3, ,, when I only have a field called FWeek.
Current State:
Like I said I have been struggling with this type of report. I have been going down the crosstab query road to build a part of this report. I figured out how to use the ?In Clause? to create the column headings I needed (Week 1 , 2, 3, ,,), populate zeros where no values were found (Using ?Nz? in my sum expression of GWP) but how to get ALL products to list has eluded me. Below is what I have so far on the crosstab query:
推荐答案
您好，Keith。
只要所有三个数据集都包含[ProductName]的唯一值，您就可以完全加入它们。
问候，
Fish
Hi, Keith.
As soon as all three datasets contain unique values of [ProductName] you may just join them altogether.
Regards,
Fish
基思。
尽快所有三个数据集都包含[ProductName]的唯一值，您可以将它们全部加入。
问候，
鱼
Hi, Keith.
As soon as all three datasets contain unique values of [ProductName] you may just join them altogether.
Regards,
Fish
谢谢鱼：
在我的无知中，[的独特价值是什么意思产品名称]？我还会沿着Crosstab查询路走下去吗？
基思。
Thanks Fish:
In my ignorance, what is meant by unique values of [ProductName]? Would I still go down the Crosstab query road??
Keith.
谢谢Fish ：
在我的无知中，[ProductName]的唯一值是什么意思？我还会沿着Crosstab查询路走吗？
基思。
Thanks Fish:
In my ignorance, what is meant by unique values of [ProductName]? Would I still go down the Crosstab query road??
Keith.
好吧。交叉表查询就可以了。我猜[LOB]字段包含所谓的产品名称字段。在你提供的布局样本中。我对吗？它与其余两个表中的[ProductName]字段是否匹配？
如果是这样，如果其余两个表中的每个表都包含[ProductName]字段中的唯一值，那么您可以将它们与交叉表连接起来查询（我想通过外部联接）。
问候，
鱼
Well. Crosstab query is just ok. I guess [LOB] field contains what is called "Product Name" in layout sample you''ve provided. Am I right? Does it match [ProductName] field in the rest two tables?
If so, and if each of the rest two tables contain unique values in [ProductName] field , then you may just join them with the crosstab query (I guess via outer join).
Regards,
Fish
这篇关于创建一个透视表查看报告??请打开灯....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
