Form based on crosstab - hiding columns (1 Viewer)

[Martynwheeler]

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

 

[arnelgp]

see the open event of the form.

 

[Martynwheeler]

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]

@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

Extended File Properties

This shows a simple method of obtaining all the extended file properties for any type of file. There are over 320 extended properties including the dimensions of pictures and the duration of video files

www.isladogs.co.uk

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:

School Data Analyser

School Data Analyser is used to manage and analyse student, staff and school data.

www.isladogs.co.uk

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]

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.

 

[Martynwheeler]

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]

You could of course use a side db for any temp tables and not bloat your db.?

 

[Martynwheeler]

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]

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]

Side db = temporary local backend

Adding an AutoNumber field to a linked table

When adding a linked table to a csv file, is it possible to add an AutoNumber field to be used as a PK while working with the records? I can't see any option for adding a PK to the linked table in the wizard? Thanks.

www.access-programmers.co.uk