Is it possible to force empty columns to appear in a crosstab. (1 Viewer)

[wmphoto]

I have created a crosstab which feeds into other queries as it has been designed so that the row headings match the key of another table and the columns can be used as fields in another query.

The problem I have is, in order to use it most easily I need the same three columns to show, even when there is no data for them. There are only ever a few records which the query returns, and sometimes none, this creates a problem because when there is no data in the query, the columns disappear making a mess of any queries that are looking for those columns.

The only way around this I've found is to create null records with '0' in the value fields to force the columns to appear, but this isn't ideal as now my database is full of null records and I need to make sure all other queries aren't returning the nulls. It would be easiest for me if there were simply a way to force the crosstab to always show certain values as columns.

 

[sxschech]

If the names of the headers are always going to be the same, you can enter them into the query, so those cols will always show regardless of whether they have data or not.

If you are in design (graphical) view, then open the properties window of the crosstab and you will see a section called Column Headings. Type in the heading names here using quotes and commas such as: "Field1","Field2","Field3", etc...

If you are in SQL view, then after the PIVOT you would type:

In ("Field1","Field2","Field3", etc)

If the field names are not the same each time, then the above won't work as shown.

 

[wmphoto]

Thanks a lot, that works perfectly, just when I'd braced myself to be told it cant be done.

 

[sunnytaru]

If the names of the headers are always going to be the same, you can enter them into the query, so those cols will always show regardless of whether they have data or not.

If you are in design (graphical) view, then open the properties window of the crosstab and you will see a section called Column Headings. Type in the heading names here using quotes and commas such as: "Field1","Field2","Field3", etc...

If you are in SQL view, then after the PIVOT you would type:

In ("Field1","Field2","Field3", etc)

If the field names are not the same each time, then the above won't work as shown.

Hi I am a beginner with access, I was working on the Crosstab query and I had the similar issue. As you suggested I did Add under Property sheet the Column headings, how do I define it under the Design View for Column Heading. ? My calculations do not work when I do it the above way their is no value displayed, also getting Error on the Fields that have no value.
SQL
TRANSFORM CCur(Nz(Sum([CurrentBudget]*[MemberShare]/[SumofMemberShare]),0)) AS MC
SELECT TestProjectCostFieldsJPPQRY.ProjectID, ProjectWPTable.DocumentType, ProjectWPTable.WPNumber, ProjectWPTable.WPName, ProjectWPTable.CurrentBudget
FROM (TestProjectCostFieldsJPPQRY INNER JOIN ProjectWPTable ON TestProjectCostFieldsJPPQRY.ProjectID = ProjectWPTable.ProjectID) INNER JOIN SumProjectMemberShareQRY ON ProjectWPTable.ProjectID = SumProjectMemberShareQRY.ProjectID
GROUP BY TestProjectCostFieldsJPPQRY.ProjectID, ProjectWPTable.DocumentType, ProjectWPTable.WPNumber, ProjectWPTable.WPName, ProjectWPTable.CurrentBudget, ProjectWPTable.DocumentTypeID
ORDER BY TestProjectCostFieldsJPPQRY.ProjectID DESC , ProjectWPTable.DocumentType, ProjectWPTable.WPNumber DESC
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];

 

[theDBguy]

Hi I am a beginner with access, I was working on the Crosstab query and I had the similar issue. As you suggested I did Add under Property sheet the Column headings, how do I define it under the Design View for Column Heading. ? My calculations do not work when I do it the above way their is no value displayed, also getting Error on the Fields that have no value.
SQL
TRANSFORM CCur(Nz(Sum([CurrentBudget]*[MemberShare]/[SumofMemberShare]),0)) AS MC
SELECT TestProjectCostFieldsJPPQRY.ProjectID, ProjectWPTable.DocumentType, ProjectWPTable.WPNumber, ProjectWPTable.WPName, ProjectWPTable.CurrentBudget
FROM (TestProjectCostFieldsJPPQRY INNER JOIN ProjectWPTable ON TestProjectCostFieldsJPPQRY.ProjectID = ProjectWPTable.ProjectID) INNER JOIN SumProjectMemberShareQRY ON ProjectWPTable.ProjectID = SumProjectMemberShareQRY.ProjectID
GROUP BY TestProjectCostFieldsJPPQRY.ProjectID, ProjectWPTable.DocumentType, ProjectWPTable.WPNumber, ProjectWPTable.WPName, ProjectWPTable.CurrentBudget, ProjectWPTable.DocumentTypeID
ORDER BY TestProjectCostFieldsJPPQRY.ProjectID DESC , ProjectWPTable.DocumentType, ProjectWPTable.WPNumber DESC
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];

Hi. Welcome to AWF!

Just FYI, you are replying to a 10-year old thread. Did you try using the In() clause as described above? I don't see it in your SQL statement. You might want to start a new thread for your question.

 

[sunnytaru]

Hi. Welcome to AWF!

Just FYI, you are replying to a 10-year old thread. Did you try using the In() clause as described above? I don't see it in your SQL statement. You might want to start a new thread for your question.

Hello TheDBGuy,
Appreciate your response, I did start the new thread. I responded on this above as it was similar to my issue. I am not sure about the In() Clause as you suggested, I am still newbie., help please!

 

[theDBguy]

Hello TheDBGuy,
Appreciate your response, I did start the new thread. I responded on this above as it was similar to my issue. I am not sure about the In() Clause as you suggested, I am still newbie., help please!

I'll respond to your other thread instead. Cheers!

 

[Pat Hartman]

The solution you were given is correct but there is a "gotcha" you should keep in the back of your mind.

The Columns clause acts like criteria as you can see by looking at the actual SQL of the crosstab. Therefore, if you have a value in the data that does not match one of the column names specified, that row will not be selected. For example. Your shop currently only works Monday-Friday so you add column headings for those five days because you want to see them in logical order rather than alpha order. Sometime later, the workload increases and now you have a Saturday shift. No Saturday data will be selected for the crosstab until you modify the columns property to add Saturday.