By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Crosstab Querty to return all blank fields

P: 15
Hi,
I have a crosstab query that always displays last 8 days worth of data(Date(),Date()-1,Date()-3, etc)).
Some of these columns are blanks and MS Access, by default, returns only those columns that has some values in it. How do I make sure that it displays even the blank columns with the dates in "yyyy/mm/dd" as column headers.My base query is like this.
I tried adding Date(),date()-1, etc in the Column headings in the properties, but no luck.

TRANSFORM IIf(Count([PQR Number]) Is Null,0,Count([PQR Number])) AS Expr2
SELECT [Closed Remedy PQR].[Report Category]
FROM [Closed Remedy PQR]
GROUP BY [Closed Remedy PQR].[Report Category]
PIVOT Format([Create-date],"yyyy/mm/dd");
.
Thanks,
Prasanna.
Oct 17 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You need an extra query/table that lists all the values that you need as your columns and then outer join it to your original table for your crosstab.
Oct 18 '07 #2

P: 15
You need an extra query/table that lists all the values that you need as your columns and then outer join it to your original table for your crosstab.
Hi,
Thanks for your suggestion. I have a table which lists last 8 days. How do I outer join and create the query? I am sorry If my question is simple and query might be complex :).
Thanks,
Prasanna.
Oct 18 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
You outer join by dragging the date field from one table onto the date field of the other table. Then you double click the link and tell it to include all records from the table where you listed the 8 dates. Other than that the crosstab query is the same except you use the date field from the new table as the column heading instead.
Oct 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.