交叉计数与空列 [英] Crosstab Count with Empty Columns
问题描述
好的，我有另一个有趣（ 讽刺） 问题我希望有人可以帮助我。
简而言之，我使用 COUNT 函数进行交叉表查询，其中Dates为列标题，不同类别为行我无法检索完整的记录集，因为有些天没有记录。
我的问题是，在某一天没有数据的某些情况下（给定标准根本没有记录）。我想念输出中的白天或白天。
因此，出于报告目的（在Excel中），我必须查看所有日期（有时是YTD）并添加日期和当天的零值。
所以我得到了。
类别6/1 6/3 6/4 6/6
打印机3 2 3 8
我没有检索到计数（因此没有专栏）6月2日和6月5日
我想要的是：
类别6/1 6/2 6/3 6/4 6/5 6/6
打印机3 0 2 3 0 8
这是我的SQL代码：
TRANSFORM IIf（Sum（[Count]）是Null，0，Sum（[Count]））AS Expr1
SELECT [Daily]。类别
来自[每日]
WHERE（（（[Daily] .CATEGORY）=" HARDWARE"）AND（（[Daily]。日期（）日期（） - 13和日期（） - 1））
GROUP BY [每日RADAR_STORE] .CATEGORY
PIVOT [每日] .DATE;
----------------------------------- -----------------------------------
我试过了IIF（ISNULL ..和NZ功能但没有运气。
请帮助!!! 任何建议都将不胜感激！
谢谢，
Boxcar
Ok I have another fun (sarcasm) Question I hope someone can help me with.
In a nutshell I have a Crosstab query using the COUNT function with Dates as the column heading and different categories as the rows and I can?t retrieve a full record set because some Days have no records.
My problem is that is some cases for a given day(s) that has NO data, (no records at all for the given criteria). I miss the day or day?s in my output.
So for reporting purposes (in Excel) I have to look at all the days (sometimes YTD) and add the Date and a zero value for that day.
So for example I get.
Category 6/1 6/3 6/4 6/6
Printer 3 2 3 8
I retrieve no count (hence no column) for June 2 and June 5
What I want is:
Category 6/1 6/2 6/3 6/4 6/5 6/6
Printer 3 0 2 3 0 8
Here is my SQL Code:
TRANSFORM IIf(Sum([Count]) Is Null,0,Sum([Count])) AS Expr1
SELECT [Daily]. CATEGORY
FROM [Daily]
WHERE ((([Daily].CATEGORY)="HARDWARE") AND (([Daily].DATE) Between Date()-13 And Date()-1))
GROUP BY [Daily RADAR_STORE].CATEGORY
PIVOT [Daily].DATE;
----------------------------------------------------------------------
I tried the IIF(ISNULL.. and NZ function but no luck.
Please help!!! Any suggestions would be appreciated!
Thanks,
Boxcar
推荐答案
因为你实际上并没有说数据是如何进入表格的，所以它有点难以排序。但是，一种方法是将一个defaut vaule分配给ZERO的记录，这样你总是会有daya来解释和你可以清楚地识别NIL销售/用法
Gareth
As you dont actually say how the data gets in to the table its a little dificult to sort. However, one way is to allocate a defaut vaule to the record of ZERO that way you will always have daya to account for AND you can clearly identify NIL sales/usages
Gareth
不幸的是，执行此操作的唯一方法是在PIVOT之后使用IN语句以允许所有日期。我猜想这将是无法管理的。
Unfortunately the only way to do this would be to use an IN statement after the PIVOT to allow for all dates. I would guess that this would be unmanagable.
是的看起来那样 :(
我厌倦了参加一年中每个日期的桌子。
但仍然没有运气。
关于表，我正在执行任何ID号（主键）的COUNT以及一天中发生的时间。
所以简单地把 TABLE列 放在：
INSTANCE_ID / DATE / CATEGORY / More数据等.....
我知道有人说他已经做了某种方式。
当我发现我会发布它。 （如果他能做到的话）
我非常确定mmccarthy是正确的。
一如既往地感谢输入!!
Yeah it looks that way :(
I tired joining a table with every date of the year.
But still no luck.
In regards to the table I am doing a COUNT of any ID number (Primary Key) and how many time it occurs on a day.
So simply put the TABLE Columns would be:
INSTANCE_ID / DATE / CATEGORY / More data etc.....
I know someone that says he has done it some way.
When I find out I''ll post it. (If he can do it)
I?m pretty sure mmccarthy is right on this one.
As always thanks for the input!!
这篇关于交叉计数与空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
