Form based on crosstab - hiding columns (1 Viewer)

Martynwheeler

Member
Local time
Today, 07:58
Joined
Jan 19, 2024
Messages
82
I have a crosstab query that can hold student marks from a predefined number of tests. I want to display the query on a form so I have set the column headings of the query to the known values I expect from the test titles. This all works fine.

However, I would like to hide columns that contain no data. I can work out from the query that the crosstab is based on which columns will have no data - I can put the column headings into an array as the form loads (this will change over the course of time but I can always determine it).

Ideally I'd like to loop through the columns and hide those that that are not in my array.

I enclose the file I am working on.

the relevant form is "frmStudentGradeSheet" which is based on qselStudentGradeSheet (which in turn is based on two crosstabs)

Thank you in advance,

Martyn
 

Attachments

  • Student_Marksheet_v3.accdb
    1.2 MB · Views: 48

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:58
Joined
May 7, 2009
Messages
19,248
see the open event of the form.
 

Attachments

  • Student_Marksheet_v3.accdb
    3.5 MB · Views: 50

Martynwheeler

Member
Local time
Today, 07:58
Joined
Jan 19, 2024
Messages
82
see the open event of the form.
Thanks for this. It felt quite slow to load so I have dumped the query into a temporary table. I can now hide columns easily.
 

isladogs

MVP / VIP
Local time
Today, 07:58
Joined
Jan 14, 2017
Messages
18,269
@Martynwheeler
For info, forms/reports based on crosstab queries can have:
a) static column headers - all specified columns will be included whether or not they have data
b) dynamic column headers - these will only show columns where data exists. As the column headers will not be fixed, code is used to determine the headers 'on the fly'
I have various examples of this including a report in my article on extended file properties

From your posts, I believe you are UK based like myself.
You may find it worth looking at the web page for my main app for schools:

Its a commercial app and I'm not intending to suggest you should buy it,. However, you may find useful ideas on that web page or in the PDF of the program features
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:58
Joined
May 7, 2009
Messages
19,248
here is also an alternative, much faster loading than the previous one i upload.
i created a Temp query (instead of temp table that will Bloat your db), zzStudentGradeSH.

initially all Columns are hidden and only get UnHidden when it's column in zzStudentGradeSH is not Zero.
see the Open event of form frmStudentGradeSheet.
 

Attachments

  • Student_Marksheet_v3.accdb
    1.3 MB · Views: 48

Martynwheeler

Member
Local time
Today, 07:58
Joined
Jan 19, 2024
Messages
82
here is also an alternative, much faster loading than the previous one i upload.
i created a Temp query (instead of temp table that will Bloat your db), zzStudentGradeSH.

initially all Columns are hidden and only get UnHidden when it's column in zzStudentGradeSH is not Zero.
see the Open event of form frmStudentGradeSheet.
Thank you for that. It is much faster and has helped me a lot. I am stuck creating a temp table because I am doing a substantial amount of processing of the queried data. I tried putting the processing into the query but it slowed everything substantially (load time of > 10s for a form).

I am not sure I can get around it unless I start storing calculated values in a table.

I have a table of test marks (already at >4000 entries) which is selected and marks are processed into a unified score and grade. These are then summed to create a weighted average. If I dump all the processed marks into a temp table I am able to do all the weighting (which involves determining the median of a subset of the marks) and it takes < 1s.

Thanks again for your help

Martyn
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:58
Joined
Sep 21, 2011
Messages
14,528
You could of course use a side db for any temp tables and not bloat your db.?
 

Martynwheeler

Member
Local time
Today, 07:58
Joined
Jan 19, 2024
Messages
82
You could of course use a side db for any temp tables and not bloat your db.?
oooh, how do you do that?

I have currently split the db and the temporary tables are not in the backend. So they are created by the user on demand but never appear in the backend of the db. Is this an okay approach?

Martyn
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:58
Joined
Sep 21, 2011
Messages
14,528
Not something I have ever done, but have seen it mentioned on here several times.

Basically you have another DB with whatever tables you need. You copy that MASTER/Template DB to another DB where you are linked to those tables from the FE.
You carry out your processing.
The next time you need that DB, you copy again and repeat,
So it does not matter how much that side db gets bloated, you are replacing it with a fresh template copy each time.
 

ebs17

Well-known member
Local time
Today, 08:58
Joined
Feb 7, 2020
Messages
2,002
Side db = temporary local backend
 

Users who are viewing this thread

Top Bottom