Create crosstab report from Crosstab query (1 Viewer)

[GoodyGoody]

Hi, I have got a crosstab query working very nicely showing the column headings I want. However, when I go to create an Access report from it Access tells me that I have no PIVOT fields defined. Below is the query. I added the bit in red so that I couold gnerate a report but a) I really donn;t want to have to specify fixed column headings as they will change depending on the series and SURELY Access is better than that and secondly anyway when I run this report I get no data in the columns so clearly my syntax is wrong but serarchign the web doesn't come up with much help. I want the variable RACENAME to be teh column heading not a hard-coded name. Please tell me this is possible!:

PARAMETERS forms!frmPrintSeriesTeamResults!cmbSeriesID Short;
TRANSFORM Sum(qrySeriesTeamResultsInput.[TeamTotPos]) AS SumOfTeamTotPos
SELECT Club.[Club Name], qrySeriesTeamResultsInput.[Team], Sum(qrySeriesTeamResultsInput.[TeamTotPos]) AS [Total Of TeamTotPos]
FROM ((qrySeriesTeamResultsInput INNER JOIN Club ON qrySeriesTeamResultsInput.TeamClubID = Club.ID) INNER JOIN (RaceName INNER JOIN RaceEvent ON RaceName.ID = RaceEvent.RaceName) ON qrySeriesTeamResultsInput.TeamRaceEventID = RaceEvent.ID) INNER JOIN Series ON qrySeriesTeamResultsInput.TeamSeriesID = Series.SeriesID
GROUP BY Club.[Club Name], qrySeriesTeamResultsInput.[Team]
PIVOT RaceName.RaceName IN ("FforestFields", "Croft Castle", "Queenswood", "RotherWas", "Presteigne");

 

[theDBguy]

Hi. Unfortunately, if you want to display the result of a crosstab query on a report, you will have to VBA code to dynamically assign the column names in your textboxes. There are some demos available to show you how to do this.

 

[CJ_London]

seems to me you have a number of issues

1. RaceName.ID = RaceEvent.RaceName - looks like you are comparing numbers with text - perhaps you are using lookups in your table which is a bad idea.
2. RaceName.RaceName - fields should not have the same name as the table - is this supposed to be raceevent.racename?
3. "FforestFields" - is that a correct spelling?

with regards no data, suggest start with a simple select query to confirm you have the data, then transform it

you may want to look at your bracketing in your FROM clause. It may be OK but I would expect to see 3 ( immediately after FROM and drop the one before RaceName.

Edit: just seen DBG's response - I agree with his comments

 

[GoodyGoody]

Hi DBGuy, can you send me links to the examples please? Access won't even let me create a report without the PIVOT .. IN statement completed for some reason

 

[isladogs]

I have several apps with dynamic crosstab reports including one with this example app Extended File Properties using VBA.

Hopefully it will be obvious how to use the code in your own apps.

If not try googling 'dynamic crosstab report headings access vba' and you should find other examples/code

 

[GoodyGoody]

Well, all, just an update. So the answer to the first question regarding why access won't allow you to create a report based on a crosstab query without the PIVOT...IN being complete is, it's just a bug in Access! Access has no problem running a report without jsut a simmple PIVOT [field_name] but it seems you have to go thorugh the hoop of adding spurious 'IN' fields in and then just deleting them. All this faffing with complex workarounds seems to me to be incredible. What I am now doing is including the ID code of the record which generate the column heading (in this case the race) and then justs dynamically creating the column heading when the report prints.

So, the last question I have is 'Are the columns in an Access report available in a collection somewhere such that I could dynamically loop through the columns and thus have a dynamic non hard-coded report. So let us say there are 8 races in this particular series and each column represents a race could I loop through the collection something like:

First print the column headings
ColumnCount = 0
Do While Not columncount > Me.ColumnCountMax
sqlString = "SELECT RaceName from RaceTable where RACEID = " & Me.Column(columncount).label
Set rs1 = db.OpenRecordset(sqlString, dbOpenDynaset)
Me.Column(columnCount).label = rs1!RaceName
Me.Column(columnCount).label.visible = true
columncount = columncount + 1
End do

And then do something similar with the report control sources too when print the actual data too? Don't worry about the fine detail of the VBA, the main question is are the Report Column label names and report control source names available in a collection?

If I could do this then I could set a limit for the Access report and then export reports with more columns to Excel...

Thanks for your helo as ever
Stephen

 

[CJ_London]

access won't allow you to create a report based on a crosstab query without the PIVOT...IN being complete is, it's just a bug in Access!

not sure what version of access you are using but it's not a bug. You can create a report based on a crosstab without specifying column headings (the IN part of the pivot)- but what will be created is fixed to whatever columns are generated. Next time you run it you may get different columns and if some are missing you will get a #Name error on the report - and of course, any additional columns will be ignored. If you are having to include an 'IN' clause the implication is either your data is too complex, perhaps contains reserved words or is referencing a form or returning too many columns.

the easiest way to 'relabel' is to give your labels a name like lbl1, lbl2 etc

then you can iterate through them using something like

for i=1 to 5
  me("lbl" & i).caption=whatever
next i

don't forget you will also need to reassign the controlsource for the controls containing data as well.

with regards your proposed method to get the 'whatevers' you need to ensure the records are returned in the same order as the columns - best way is to order both queries on the same field(s)

Alternatively as suggested in post #2, google 'access dynamic crosstab' or similar. Your requirement is a common one and there are plenty of examples out there.

 

[isladogs]

Agree with CJL.
You do NOT need to go through the ridiculous procedure you described

Did you look at the example report in the link I provided in post #5?
That will show you how to do this.

 

[theDBguy]

Hi DBGuy, can you send me links to the examples please? Access won't even let me create a report without the PIVOT .. IN statement completed for some reason

Hi. Sorry for the delay, I was out of pocket yesterday. I hope this is not too late yet, but here goes.


Dynamic Report from a Crosstab Query


Hope it helps...

 

[GoodyGoody]

TBH I'm utterly confused. Clearly this is above my paygrade. The 'workarounds' seem almost incomprehensibly complicated and why is looping through the columns so ridiculous Isladogs especially when compared with the alternatives? None of the links seem to have led to a comprehensible solution. I really don;t get why Access has made this so complicated. So, DBGuy, that link you posted just takes me to another forum where some guy is posting nested sql that I have no chance of unpicking.

IF I can know the max number of columns and IF I can interrogate a collection and get the column labels and IF I can get the controlsource for each column then it would work really nicely without going through the interwoven hoops of everything I have read here. I have a working crosstab query that took 2 minutes to put together that works in every scenario. Does anyone have a simple worked example that I could use as a template as to how to make the report work as a generic report? If someone could answer the specific questions too it would be a big help.

Thanks

 

[theDBguy]

TBH I'm utterly confused. Clearly this is above my paygrade. The 'workarounds' seem almost incomprehensibly complicated and why is looping through the columns so ridiculous Isladogs especially when compared with the alternatives? None of the links seem to have led to a comprehensible solution. I really don;t get why Access has made this so complicated. So, DBGuy, that link you posted just takes me to another forum where some guy is posting nested sql that I have no chance of unpicking.

IF I can know the max number of columns and IF I can interrogate a collection and get the column labels and IF I can get the controlsource for each column then it would work really nicely without going through the interwoven hoops of everything I have read here. I have a working crosstab query that took 2 minutes to put together that works in every scenario. Does anyone have a simple worked example that I could use as a template as to how to make the report work as a generic report? If someone could answer the specific questions too it would be a big help.

Thanks

Hi. First of all, I think you agree that Access provides a way to create a dynamic query using a crosstab, but it's not possible to do the same with a report without using code, correct? Otherwise, the simplest approach is to export the crosstab query into Excel, and there's no more issue.


As for giving you a working sample, why not consider just posting a copy of your own db, so we can give you an exact example that will work for you without you having to adapt it further to your own structure. You can still learn from it, and it would be probably make more sense because you already understand the structure.


Just a thought...

 

[isladogs]

I'm sorry you're finding this hard to do but rather than blaming MS/Access for making it difficult, you need to study examples and adapt those to get it to work. If you specify exactly what you are having trouble with, we should be able to assist

I was referring to this:

but it seems you have to go through the hoop of adding spurious 'IN' fields in and then just deleting them. All this faffing with complex workarounds seems to me to be incredible

That is simply untrue

As previously stated creating a crosstab report with column headings where data exists is simple - no code required
However, if you want to specify headings whether or not data exists, then additional code is required

Method 1 - fixed column headings in the property sheet
This works well for e.g. a monthly report with headings Jan, Feb, Mar ... Dec even if no data exists for one or more months

Method 2 - dynamic column headings where the headings will depend on the report criteria
e.g. school exam results crosstab report where the column headings (subjects will vary from one year or school to another

Managing something like that is much more complex. There are various approaches but the one I gave is generic. In other words it can be adapted to any crosstab report.

You need to know the number of fixed columns on the left - 8 in the above screenshot. The remaining column headers are those from the crosstab data.

If needed you can also have totals or averages for each column in the report footer. The code manages that as well

If you don't like the method I use, you can find others, but all will have similar approaches to managing dynamic column headings.

Good luck. If you have specific questions, do come back.

 

[GoodyGoody]

Hi Isladogs, sorry no I couldn't make any sense of your example. It seemed highly complex and didn't seem to relate to what I need to do. I'm sure it does I am clearly not experienced enough to understand it which is rather because as your output shows, like the school report, that is exactly what I want. When I type in the search criteria most reports use dates not random names for the column headings and once again all seem incredibly esoteric. Quite frankly at this stage I would be happy to export the crosstab to Excel but the data needs a bi of manipulation because only teams with results in all races qualify for the team prize. I really do want to crack this.

So let's take this one step at a time. Do I need to change my crosstab query or is it all done in VBA when printing the report? Attached is cut down DB for the report which all seems to hang together. Input greatly appreciated. Works fine as is but need the hard coding of column names to go and ideally to have no limit (or a high limit) to the number of columns (like Isladogs School report)...

 

[theDBguy]

So let's take this one step at a time. Do I need to change my crosstab query or is it all done in VBA when printing the report?

Hi. As far as I know, you can only display a crosstab query, that changes column names, on a report by using VBA. If you can change your crosstab query to "always" use the same column names, then you won't need VBA.

 

[isladogs]

You shouldn't need the IN part in red in post #1.
You may well not need the PARAMETERS line either.

The dynamic headings are all handled using vba.

The links in this search provide a variety of solutions that may help Dynamic crosstab report headings

It may be worth uploading a sample copy of your database with the relevant objects for this task i.e. tables/queries etc and just enough data so it can be tested. If you have a draft report include that as well.

Then someone, not necessarily me, can walk you through getting this done. I'm about to sign off for tonight

 

[GoodyGoody]

DB uploaded

I need the parameters because the user selects the series they want to report on. Access Reprots ( as someone else pointed out) doesn't let you use the report wizard on crosstab queries unless you have completed the 'IN' part of PIVOT but I have no idea why because if you just put bogus 'IN' column headings in, create the report and then delete them again it works fine. Anyway, that's sorted but all of the examples seem to be talking of what seem hideously complex temp tables etc

 

[isladogs]

DB uploaded

I can't see it! When you reply please tell us what to look at in your dB and what you want as your report output.

EDIT I now see its been added to post #13

 

[GoodyGoody]

Current report shows the actual report output.

 

[CJ_London]

Access Reprots ( as someone else pointed out) doesn't let you use the report wizard on crosstab queries unless you have completed the 'IN' part of PIVOT

don't use the wizard, just select the crosstab query and then click report on the create ribbon.

 

[GoodyGoody]

Well wouldn't ya know I managed to work it out so thank you for your patience. It took a bit of working out as to how to set the parameter up in VBA but that works perfectly now too. So the only thing left to do is for me to change the hard-coded column names in the PrintDetail event to a generic bit of code that will loop through the column values in the same way but without having the column names hard-coded. So what I currently have is:

If IsNull(Me.[Fforest fields XC - MEN]) Or IsNull(Me.[Croft Castle XC race -MEN]) Or IsNull(Me.[Queenswood XC Race -MEN]) Then
Cancel = vbCancel
Me.MoveLayout = False
End If

So I need to know how many fields there are in total to loop through and then refer to each field in turn to see if it is null or not. Is there a way of doing that without hardcoding the field names like I have here?

Thanks
Stephen