Crosstab Count with Empty Columns

[Boxcar74]

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

[garethfx]

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

[MMcCarthy]

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.

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 IN ("6/1", "6/2", "6/3", "6/4", "6/5");
 

[Boxcar74]

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!!

[MMcCarthy]

If you have a table containing every day of the year you could try the following (assuming table name as tblDates)

TRANSFORM IIf(Sum([Count]) Is Null,0,Sum([Count])) AS Expr1
SELECT [Daily]. CATEGORY
FROM tblDates LEFT JOIN [Daily] 
ON tblDates.[Date] = [Daily].[DATE]
WHERE ((([Daily].CATEGORY)="HARDWARE") AND ((tblDates.[DATE]) Between Date()-13 And Date()-1))
GROUP BY [Daily RADAR_STORE].CATEGORY
PIVOT tblDates.[Date];
 

This might work

[Boxcar74]

That didn't work either. I appreciate the effort though.

I set up a small test DB and even renamed the tables and field to match the query.

One thing that could have thrown you off is I posted the first SQL line incorrectly.

It should have been:
TRANSFORM Count(Daily.[ID]) AS COUNT

So this is how I tried it:
TRANSFORM IIf(Count([ID]) Is Null,0,Count([ID])) AS Expr1
SELECT Daily.Category
FROM tblDates LEFT JOIN Daily ON tblDates.Date = Daily.Date
WHERE (((Daily.Category)="HARDWARE"))
GROUP BY Daily.Category, Daily.Date
PIVOT tblDates.Date;

So I’m counting the number of times the ID happen a given day. If nothing happens that day I don’t get a column heading.

As I mention someone I know said he can do it. He said it was too hard to explain over the phone. I’m meeting with him next week.

If I get an answer, I’ll post it.

Thanks Again

-- Boxcar

[MMcCarthy]

That didn't work either. I appreciate the effort though.

I set up a small test DB and even renamed the tables and field to match the query.

One thing that could have thrown you off is I posted the first SQL line incorrectly.

It should have been:
TRANSFORM Count(Daily.[ID]) AS COUNT

So this is how I tried it:
TRANSFORM IIf(Count([ID]) Is Null,0,Count([ID])) AS Expr1
SELECT Daily.Category
FROM tblDates LEFT JOIN Daily ON tblDates.Date = Daily.Date
WHERE (((Daily.Category)="HARDWARE"))
GROUP BY Daily.Category, Daily.Date
PIVOT tblDates.Date;

So I’m counting the number of times the ID happen a given day. If nothing happens that day I don’t get a column heading.

As I mention someone I know said he can do it. He said it was too hard to explain over the phone. I’m meeting with him next week.

If I get an answer, I’ll post it.

Thanks Again

-- Boxcar

Thanks Boxcar, that would be appreciated.

[Boxcar74]

OK, the answer I got is using two queries.
A UNION Subquery then a Crosstab Query !!!

So to review for this example: I’m running a crosstab query with dates as my columns heading, but if nothing happens (no record) on that day I end up with no column for that day. (see previous post for examples)

Here my setup. I tried to simplified it.

One table with all my data called DAILY and another with every day of the year call DAYS. In this query I’m looking for a count of records that that category equals Hardware; based on ID# for the last 13 days.

SQL for UNION: (I named it "Counts_For_Crosstabs_13_Days")

SELECT [DAILY].Date, [DAILY].Category, Count([DAILY].ID) AS CountOfID
FROM [DAILY]
GROUP BY [DAILY].Date, [DAILY].Category

HAVING ((([DAILY].Date) Between Date() -13 AND Date() -1)) AND [DAILY].Category = “Hardware”

UNION SELECT Days.Day, Null AS Cat, Null AS Tot
FROM Days
WHERE (((Days.Day) Between Date()-13 And Date() -1));

This query essentially adds a Null record for any day in the last 13 days.

NOTE: on the (UNION SELECT Days.Day, Null AS Cat, Null AS Tot) the NULL AS … is so the table has the same number of columns.

Then I run a Crosstab Query: (this one sums the counts)
TRANSFORM Sum(Counts_For_Crosstabs_13_Days.CountOfID) AS SumOfCountOfID
SELECT Counts_For_Crosstabs_13_Days.Category
FROM Counts_For_Crosstabs_13_Days
GROUP BY Counts_For_Crosstabs_13_Days.Category
PIVOT Format([Date],"Short Date");
(Sorry my queries don't look nice like mmccarthy's)

Now if I run the second query I get every day for the last 13 days no matter if there are record for that day.

My solution is very convoluted. But is works for me.

I hope this makes sense and I hope it works for anybody out there!!!

-- Boxcar

[Boxcar74]

One Last note:

Other options that were suggested to me:
1. Combining the two queries, where the Union query will function as a Crosstab query.

2. Use a VBA module: How to Count Values in Record or Recordset Across Fields
http://support.microsoft.com/kb/q142227/

I never got either of these to work, but give it a try.

As always thanks to all the Scripts folks that helped.

Good Luck!!!

-- Boxcar

[MMcCarthy]

Thanks Boxcar. The union query is a nice solution as long as you have a table for all the relevant days. Is your days table a full list of all 365 days of the year?

[Boxcar74]

Yes and Then some from 1/1/2007 to 12/31/2015 with everyday in between, accounting for leap year and all.

I know the db will be long gone or in a different format by then but I just fill the table when I created it. :-)

I found in the if in the first query I put: HAVING Between #1/1/2007# and Date()-1

I can use the Second (Crosstab) query to adjust the date parameters i.e. Between Date()-13 and Date()-1

But this does not work with the Category. If I specify category in the Crosstab query I lose the columns with no records.

There has to be a way to combine them (The Union and Crosstab that is).

Above my head at this point and I have it set up much more complex than I displayed. So I'm sticking with what work for now.

Thanks!!

---Boxcar

[MMcCarthy]

Yes and Then some from 1/1/2007 to 12/31/2015.

I know the db will be long gone by then but I just fill the table when i created it.
:)

Did you allow for the leap years?

[Boxcar74]

YES I just edited the post above!

[MMcCarthy]

See what this does ...

TRANSFORM Count([DAILY].ID) AS CountOfID
SELECT [DAILY].Category, 
FROM Days LEFT JOIN [DAILY]
ON Days.Day = [DAILY].Date
GROUP BY [DAILY].Date, [DAILY].Category
WHERE (((Days.Day) Between Date()-13 And Date() -1)) 
AND [DAILY].Category = “Hardware”
PIVOT Days.Day;
 

[Boxcar74]

I tired one like that before. With no luck.

I tried again with your format still no luck. When I get more time I will play with it more.

In therory it should work.

Thanks Again !!

-- Boxcar