Create crosstab report from Crosstab query (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 15:53
Joined
Oct 29, 2018
Messages
21,358
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
Hi Stephen. Glad to hear you're making good progress. Normally, you won't know how many column the crosstab query will produce, but you should know beforehand how many textboxes you have on the report, because it obviously have a limited amount of space, plus you would have to place those textboxes yourself at design time.


The usual approach is to name the textboxes with a number suffix, so you can loop through them.
 

GoodyGoody

Registered User.
Local time
Today, 22:53
Joined
Aug 31, 2019
Messages
120
Hi DBGuy, thanks. I was thinking that the actual text box names are being overridden. Or is that not the case? I have text boxes in my report txtRaceData1,2,3,4 etc. Do those names remain available to me? I'm still not exactly clear what technically the whole pre-processing has done to the report controls. Perhaps someone could explain a bit more of the theory? Thanks Stephen
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:53
Joined
Oct 29, 2018
Messages
21,358
Hi DBGuy, thanks. I was thinking that the actual text box names are being overridden. Or is that not the case? I have text boxes in my report txtRaceData1,2,3,4 etc. Do those names remain available to me? I'm still not exactly clear what technically the whole pre-processing has done to the report controls. Perhaps someone could explain a bit more of the theory? Thanks Stephen
Hi Stephen. No, the textbox names stay the same. What gets changed are the Control Sources of those textboxes to the new column names from the Crosstab query.


So, you would loop through the column names of the Crosstab query and assign each one to the Control Source property of the Textboxes in your report.
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
Hi Stephen
Just to confirm that is exactly what my dynamic crosstab reports do

For example in the extended properties report example database, the report has
a) 36 labels lbl1, lbl2...lbl36 in the page header for the crosstab column headers
b) 36 checkboxes chk1, chk2...chk36 in the detail section for the data (you would have textboxes instead for your data values)
c) 36 unbound textboxes Tot1,Tot2 ...Tot36 in the report footer for displaying total number of records for each crosstab column (you may not need that feature)



By default each of these items are hidden and made visible only if the crosstab column has data

You can add as many columns as you can fit on a page.
In my exam results example I have 70 (very narrow) crosstab columns.

There are 2 'fixed' columns before the crosstab data and as column numbering is zero based that means the crosstab data starts at column '2'
The report open event code loops through each of these to populate them starting at column '2'.
The code

Code:
Set Rs = CurrentDb.OpenRecordset(strSQL)

    'Populate column headers - start at column 2 
    'show columns only where data exists
    For intI = 2 To Rs.Fields.Count - 1
       Me("Lbl" & intI - 1).Caption = Rs.Fields(intI).Name
       Me("Lbl" & intI - 1).Visible = True
          
    Next intI

    'Populate checkbox controls
    For intI = 2 To Rs.Fields.Count - 1
        Me("chk" & intI - 1).ControlSource = Rs.Fields(intI).Name
        Me("chk" & intI - 1).Visible = True
    Next intI
    
    
    'Populate totals
    'each filled value = -1 (true) so do -SUM to get total
    For intI = 2 To Rs.Fields.Count - 1
        Me("Tot" & intI - 1).ControlSource = "=-SUM([" & Rs.Fields(intI).Name & "])"
        Me("Tot" & intI - 1).Visible = True
    Next intI

I hope the idea is starting to get a bit clearer now
 

Attachments

  • CrosstabReportDesign.PNG
    CrosstabReportDesign.PNG
    20.5 KB · Views: 256
  • CrosstabReport.PNG
    CrosstabReport.PNG
    49.6 KB · Views: 108
Last edited:

GoodyGoody

Registered User.
Local time
Today, 22:53
Joined
Aug 31, 2019
Messages
120
Thanks Isladogs and DBGuy. Yes, it helps to understand the theory (for me anyway) otherwise it's just monkey say monkey do so I appreciate the lesson in the underlying principles. Actually quite straightforward really once I got what exactly was happening. I did have a bit of an issue with parameters but sorted that too using Querydef.parameters etc first before opening the recordset. All I need to know now in the report is how many columns in total I have. Is THAT possible to know like I can query a recordset using rst.fields.count can I do the same on a report? If not I'm just going to have to pass the number of Crosstab query columns there are so I can then know how many fields I need to check for no data as EVERY column that could contain data MUST have data (i.e. a team needs to be in all included races in the series (in this instance anyway: more variables to follow... :) ) Thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:53
Joined
Oct 29, 2018
Messages
21,358
Thanks Isladogs and DBGuy. Yes, it helps to understand the theory (for me anyway) otherwise it's just monkey say monkey do so I appreciate the lesson in the underlying principles. Actually quite straightforward really once I got what exactly was happening. I did have a bit of an issue with parameters but sorted that too using Querydef.parameters etc first before opening the recordset. All I need to know now in the report is how many columns in total I have. Is THAT possible to know like I can query a recordset using rst.fields.count can I do the same on a report? If not I'm just going to have to pass the number of Crosstab query columns there are so I can then know how many fields I need to check for no data as EVERY column that could contain data MUST have data (i.e. a team needs to be in all included races in the series (in this instance anyway: more variables to follow... :) ) Thanks again
Hi. To avoid having to do the querydef.parameters every time you need a recordset, consider using this generic recordset next time.

With regards to checking how many columns in the query, as I mentioned earlier, there should be no need to worry about it. You could/should put as many columns can fit in your report (remember, you will have a limited space there, you won't have a limit in the query). Then, in your code, you simply limit the number of columns to assign to the report based on the number of textboxes you have.

For example, let's say you designed your report to have 30 textboxes. When you process your query, you basically just go from the first column all the way to the last one. If you hit 30 before you get to the last one, you'll have to stop because there won't be any more place left on the report to put the rest of the columns from your query. If you don't hit the limit, then you're good to go. If you hit the limit, you could add a label on your report header to let the user know there were missing data on the report.
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
Agree with DBG. You do not need to know the number of columns in your query output as the code handles that ...as long as there are enough

So with my example of 36 numbered controls for each section, it will handle anything up to 36 crosstab columns. If you have 37 columns, the code will error during the loop telling you it can't find Lbl37 in order to populate it with the column header.

My advice is to determine the likely maximum and add a few for safety margin. Use vertically aligned text in the column header so less width is needed. Consider using e.g. Arial Narrow for the textbox values for the same reason.

I normally use filter criteria instead of PARAMETERS but the principle is the same
 

GoodyGoody

Registered User.
Local time
Today, 22:53
Joined
Aug 31, 2019
Messages
120
Hi Guys, I didn;t realise I could use the generic recordset for ALL recordsets. I'll update the code. The reason for knowing the number of columns is that I only want to print the line on the report if ALL columns that SHOULD have data actually have data. If you can imagine for example that TEAM 'C' only ran in 2 of the 3 races then that line shouldn't print, hence why I need to know the number of fields that should have data (currently 3 races have been run in the series so I need to just check the first 3 text boxes. Clearly the other 2 columns do not currently have data (I have hard-coded 5 columns for now - working on the variations!). Does that make sense? Unless the reprt collection has a method for the number of columns, I can just pass the number of columns through from the VBA which does the pre-processing as I know it there from rst.fields.count so it's not a problem. It would be neater of course to pick the information up from a report method if it exists...
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
That is an extra complication and a somewhat unusual criterion for use with a crosstab query.

To manage that, I suggest you consider modifying the original query used for the crosstab so it only includes fields where none of the records are null. Then you will know that is also true for the crosstab without hard coding any fields.

Hope that helps
 

GoodyGoody

Registered User.
Local time
Today, 22:53
Joined
Aug 31, 2019
Messages
120
The reason I don't want to do that is because I want a report which shows all the races tha ANY team has been in for control purposes. SO....IS THERE a way of knowing how many columns there are on a report like in a recordset using rst.fields.count or must I pass the number as a parameter to the report? In advance of answers I have passed the number in the Openargs property :

varOpenArgs = rst.Fields.Count - 3 '3 is the number of initial columns in the query: Club, Team, Total
DoCmd.OpenReport strReportName, acPreview, , , , varOpenArgs

BUT when I try to assign this to a variable in the report Load or Open event it shows as Null? What am I doing wrong? (BTW, I'm testing if isnull(Me.Openargs) in the report)

Thanks
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
The reason I don't want to do that is because I want a report which shows all the races tha ANY team has been in for control purposes.

So why not use two different queries?
One for 'control purposes' & the other for your more restricted conditions?

BUT when I try to assign this to a variable in the report Load or Open event it shows as Null? What am I doing wrong? (BTW, I'm testing if isnull(Me.Openargs) in the report)
Sorry but doing that doesn't make sense to me
If you are opening a report using OpenArgs then testing for IsNull(Me.OpenArgs) will be False
 
Last edited:

GoodyGoody

Registered User.
Local time
Today, 22:53
Joined
Aug 31, 2019
Messages
120
Hi Isladogs, If I am passing data from the docmd.openreport why should me.Openargs return null? Anyway, I have a more pressing issue as real world data has shown a hole in the logic for the race series calculations.

I have looked on the web and the issue I have is common but all the solutions seem to be using dates or hard-coded data and ones that aren't are using T-SQL, Oracle or some other query language which seems to do other things Access sql won't. Take the Top 'n' records for a group within a recordset: I want the top 3 positions for EACH runner Sample data would be:

Runner, Race, Position
1234, 1, 6
1234,2,6
1234,3,7
1234,4,7
5678,1,20
5678,2,21
5678,3,8
5678,4,9
4321,1,1
4321,2,2
4321,3,1

Surely there must be a neat way of doing this in Access query? Thanks as ever for your help
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
I've answered your top 3 question in the other thread:
https://www.access-programmers.co.uk/forums/showpost.php?p=1662655&postcount=9

To answer the OpenArgs question, I've added the following code to a form:

Code:
Private Sub Form_Load()
    Debug.Print "1." & Me.OpenArgs
    Debug.Print "2." & IsNull(Me.OpenArgs)
End Sub

Opening the form with OpenArgs = "Test", gives this output:
Code:
DoCmd.OpenForm "Results",,,,,,"Test"

1.Test
2.False

When opening the form without OpenArgs, the output is
Code:
DoCmd.OpenForm "Results"

1.
2.True

Hope that helps
NOTE I've just corrected my previous answer. Sorry for any confusin
 
Last edited:

Users who are viewing this thread

Top Bottom