Access Crosstab Headers - Properties

I have created an Access crosstab query - where the column headers are month/year. In properties - column headings - I have manually entered the order I want mm/yyyy to appear since this is a text field.

The intent is to make this automated, so someone other than me can use this as well.

Therefore, in column headings I have entered values that may not be on the current crosstab output, but are included for future reports.

Unfortunately, the empty future columns appear as well.

Is there a way to limit the columns to those that have matching data values (and not all the column header values) when running the crosstab query output.

Thank you.
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exp vgAuthor Commented:
I have also tried placing "Is Not Null" in the criteria without success.

Thank you.
0
exp vgAuthor Commented:
I have also tried to create an extra column for this text date, and indicate Where is Not Null
0
Rey Obrero (Capricorn1)Commented:
open the crosstab query in SQL view

change this part

Pivot [fieldname]

with  

Pivot [fieldname] In("01/2015","02/2015","03/2015","04/2015","05/2015","06/2015","07/2015")
...and so on

if the format of your date field is not like this "01/2015", change it accordingly..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
The only way I've been able to do this is through code.  Create a procedure that creates a distinct list of the values that should fall in the range you desire.

Your main query will look something like:

TRANSFORM Sum([SomeField]) as Expr1
SELECT RowHeaderField
FROM yourTable
PIVOT Format([DateField], "mm/yyyy")

What you need to do is append an IN clause to that SQL so that it looks like:

TRANSFORM Sum([SomeField]) as Expr1
SELECT RowHeaderField
FROM yourTable
WHERE [DateField] BETWEEN [StartDate] and [EndDate]
PIVOT Format([DateField], "mm/yyyy") IN ("01/2015", "02/2015", ...")

So, to create the list for the IN clause, you need to do something like:
Public Sub XTabColumnHeadings(StartDate as Date, EndDate as Date)

    Dim strSQL as string, strIn as string 
    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Dim qdf as DAO.Querydef
    
    strSQL = "SELECT Format([DateField], 'mm/yyyy') as ColHeads, Min([DateField]) as FirstDate " _
                 & "FROM yourTable " &
                 & "WHERE [DateField] BETWEEN #" & StartDate & "# AND #" & EndDate & "# "
                 & "GROUP BY Format([DateField], 'mm/yyyy')
                 & "ORDER BY Min([DateField])"
    set db = currentdb
    set rs = db.openrecordset(strsql, , dbfailonerror)

    While not rs.eof
        strIn = "," & chr$(34) & rs!ColHead & chr$(34)
        rs.movenext
    Wend
    rs.close
    set rs = nothing

    strIn = Mid(strIn, 2)   'removes the leading comma

    set qdf = db.querydefs("YourQueryName")
    if instrrev(qdf.SQL, "In (") = 0 Then  
         strSQL = qdf.SQL
    Else 
        strSQL = LEFT(qdf.SQL, instrrev(qdf.SQL, "In (" ) - 1)
    EndIf

    qdf.SQL = strSQL & " In (" & strIn & ")"
    qdf.close
    set qdf = nothing

End Sub

Open in new window

You would then call this code immediately prior to running the query.  

However, this presents another problem (another question perhaps), in that the columns in your report will not have the correct ControlSource, so you will have to add code to the report which will change the control source, and probably the caption of each of the columns in the report that will vary as a result of the Crosstab.  You will also have to set the visible property of the columns that are in the report, but for which there is no data so that they don't display.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
I tried the pivot code without success, with the dates formatted.
0
exp vgAuthor Commented:
This may be a very creative approach - but is there a way to attach a character/letter to the text date field, but not make this visible in output?

This would most likely solve all problems.
0
Dale FyeCommented:
What wasn't successful?

did you put break-points in the code to determine what was going on at each step and to determine the values of the various products as the code executed?

strSQL
strIn
qdf.SQL

Without seeing your query SQL and the values of these intermediate steps, I really cannot help much further.  If you could post a sample database with limited (sanitized data), this query, the code I provided and the form or other method you are using to open the CrossTab, it would be significantly easier.
0
exp vgAuthor Commented:
I hope this helps - here is the SQL code:

TRANSFORM Count([ENC4_with EMP CLIENT_Structure].Encounter) AS CountOfEncounter
SELECT [ENC4_with EMP CLIENT_Structure].[Loc Name]
FROM [ENC4_with EMP CLIENT_Structure]
WHERE ((([ENC4_with EMP CLIENT_Structure].Encounter) Is Not Null))
GROUP BY [ENC4_with EMP CLIENT_Structure].[Loc Name]
PIVOT [ENC4_with EMP CLIENT_Structure].Date;
0
exp vgAuthor Commented:
This was the original before modification
0
exp vgAuthor Commented:
Please find the de-sensitized file attached.
Cross-Tab---Column-Heading.xlsx
0
John TsioumprisSoftware & Systems EngineerCommented:
a small sample depicting the problem would be most helpful
0
exp vgAuthor Commented:
Thank you everyone.
0
Dale FyeCommented:
I assume, that since you awarded points, you were able to get it to work.
0
exp vgAuthor Commented:
I am trying a different approach - I format the month year field as text, then I separate out this field to make each of these a month and a year in numeric, sort. The date was column field, but to do this had to make it my row field.

I am now working on transposing this table - may be posting soon if I cannot figure out this last step.

Thanks again for your help.
0
Dale FyeCommented:
OK, but if you want a working example, take a look at the attached database.

This uses a form to select start and end dates for the query.  The button checks to make sure that you have values selected in the two combos, and that the end date is greater than the start date.

It then calls the function I posted above, with several modifications to generate the In (    ) clause, and then opens the query.
xtab-with-variable-column-headings.accdb
0
exp vgAuthor Commented:
Awesome - thanks so much - I will definitely have a look.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.