473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Crosstab Querty to return all blank fields

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
3 2141
Rabbit
12,516 Expert Mod 8TB
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
pkj7461
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
12,516 Expert Mod 8TB
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

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

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently...
12
by: jkearns | last post by:
Hello, I made a report from a crosstab query following the steps onlined in MSDN's Solutions.mdb example. I now have a dynamic crosstab report (great!), but with one minor problem. I cannot get...
2
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic...
4
by: deejayquai | last post by:
I've worked through the MS KB example and it doesn't really help me as I missing the basics of what the code is doing. I've posted a couple of times here in thsi group but with no success. Could...
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
2
by: Keith Wilby | last post by:
I have a report that is based upon a crosstab query which return only the columns (fields) it has data for. When my report runs it sometimes fails because some of the text boxes don't have a field...
14
ollyb303
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.