Report Filtering: How do I Manage 100s of Filters?

[Brilstern]

This question directly relates to the same data in this Post.

Scenario:
I am trying to create a form that allows me to filter a dataset at a very granular level. To do this I need to be able to select records and their child records individually, with the number of records being a little over 250.

Possible Solution:
Right now I am toying with the idea of creating an unbound combo box that will select a family of records (on average 20 or so) and display them to be selected as checkboxes. So in theory you would select a family and the checkboxes would be dynamically filled to allow selection, then when you change the family it would write the selection to memory, building out the filter.

Pseudo Code:
User selects Family

{VBA} SELECT each control in family from tblControls and iterate to a checkbox.
Check current memory to see if any are already selected
Fill previously selected

User selects checkboxes

{VBA} write checkbox selection to memory after submit or new family is selected

On submit, compile all checkbox selections and parse for filter.

I know this method is time consuming and I will need to put in quite a bit of error handling.

So. I am curious if anyone else has other ideas. Thanks!

[jforbes]

If you are planning on putting a bunch of Unbound Checkboxes on a Continuous Form to allow you to select which records to use, you are going to be disappointed. Access only creates one instance of an Unbound control when it places it on the Form. It will paint it multiple times, but it is essentially the same instance painted over and over. Which means when you check the Checkbox, it will check it for all controls on the Form. Same goes for Unchecking.

I think you would benefit by using an Array to store the selected values. Show a list of records in a Continuous Form that have a Button that when clicked, inserts the current Primary Key into the Array. Then you can write a VBA routine that converts the Array to a Where Clause utilizing IN()

[zmbd]

Could also use the tempvars collection to add the primary key along the same lines as the array. Once done then iterate thru the collection to build the where condition. Advantage here is that if there is an error the selections are retained and can be used directly in the query or other forms.

Could use a listbox and use the add method to insert the PK and human friendly information in columns. Then iterate thru the listbox items to build the conditional. Advantage here is that one could include logic to remove a selected item from the list.

Really difficult to determine what it is exactly that you are after for the end product.

Is there a maximum number of items to be selected per family record?

Do you only want to see the selections for each family as you change family?

[PhilOfWalton]

Can you send a sample of the data you want to search and the results you want to get (I realise this may all be pseudo data)?

Phil

[Brilstern]

Please see the attached file.

So to clarify - The data structure is as such:

There are Subject Families (AC,AT,...)
In each of these Subject Families are Controls (AC-1,AC-2,AT-1,...)

I would like to have the option to select specific controls to filter my report with.

So in theory I would go to the AC Subject Family and select AC-3, AC-6 and AC-8. Then go to AT and select AT-4, AT-5, and AT8, etc. Finally filtering my report for the selected controls.

Between the 26 control families there are 282 controls, ranging anywhere from 44 Controls in a Family to 2 controls in a Family.

[nico5038]

Hi Stevan,

I normally point my users to the Access right-click filter possibilities or the filter form when an OR relation is needed.
I've once created a "de-luxe" version allowing the user to store the applied filter in a table with a user supplied name, making it possible to recall the filter. Just making the filter visible and editable gave them all filter power needed.

Idea ?

Nic;o)

[Brilstern]

Nico,

That's definitely doable.. The only concern I have there is just the multitude of data. Filtering for controls is not easy because there are 282, and rarely would it makes sense to filter for a "Contains" or "Does not Contain."

Someone might want a report with AC-12, AT-3, IR-1, PM-4,... That means running through a list in some form or faction. I am trying to make it as user friendly and as simple as possible on the user interface. The complexity on the back end doesn't bother me as long as there is little room for error and or adequate error handling.

[jforbes]

I think you are over complicating this. Just because there is a lot of data doesn't mean that it has to be difficult. In this case, what complicates the filtering the most is that the data isn't exactly normalized.

Either way, you could go with something like this:

Where the user clicks a create Filter button that displays the above Form. They select the Family and include the entire family in the Filter if needed. When the user clicks the Available Subject Families, then the Controls for Subject Family would refresh to the available controls for the selected Family. The user could then select specific controls to include.

After they give it a Name, and click Save Filter, VBA would build up the Where Clause and save it in a table with the name. Then you can make a ComboBox to display the Filter Names and allow the user to select the Filter and the Where Clause that would be passed to the Report on Open.

[PhilOfWalton]

Sorry to butt in at this late stage, but am I missing something?

Firstly in the sample database, many of the table columns are hidden.

Secondly the field names are very confusing with both duplicate field names in different tables and different names for the PK and FK in different tables. Added to that the field names give no indication which table they come from.
All very confusing

Thirdly the Relationships looks all wrong.
To my mind there are simply 4 tables in a simple string.

Using our usual conventions, and ignoring the data fields,

tblSubjectFamilies
    SubjectFamiliesID      Auto    PK

tblControls
    ControlsID             Auto    PK
    SubjectFamiliesID              FK

tblControlsEnhancements
    ControlsEnhancementsID Auto    PK
    ControlsID                     FK

tblCCI
    CCIID                 Auto     PK
    ControlsEnhancementsID         FK

Probably need a bit of code to check that field "Identifyer " in the tblSubjectFamil ies is consistent in the other tables, and so on down the string of tables.

Phil

[Brilstern]

jforbes

the picture helped me understand what you were talking about.

Phil

unfortunately I haven't really taken the time to normalize the data.

This data set is an exact import from an online version of this data, without changing any titles or relationships as of right now.

Now that being said the relationships are not as simple as you think. I really got down into the weeds on this thread. But I am not focusing on that as I have solved that issue.

The data will not change, which does allow for some 'cheating', although I avoid it simply as a way for me to work on my access skills.

To the issue of the filter:

I have figured out a solution that is kind of a combination of many of the suggestions.

I have created a new table (tblControlsBoo lean) that has fields control number and a boolean. I created a continuous form that is based on an inner join on the control number field of tblControlsBool ean and tblControls. This allows me to update the table boolean which is where my selection values are saved. This also allows for an error free data selection. Now the user selects the controls they want, (and they can filter the current selection pane by subject family if so desired) and then generate a filtered report by pushing the tables values into a string. When I get back to work tomorrow I will send an updated sample.

[zmbd]

@Stevan: JForbes suggestion is exactly what I was trying to point out in Post#3; however, you didn't answer my question - thus, I wasn't able to flesh it out.

@Phil: Context for the thread: Joining Multiple Tables with "LIKE" Data
Just saw the link in Stevan's last post... :) However, I'll leave it here too.

Agreed with the confusing field names

[Brilstern]

zmbd

I attempted to answer your question in Post#5. I'll attempt again.



Each family record (subject family) is made of anywhere from 2 - 44 items (controls). The user should be able to select none from a subject family, all from a subject family, or some from a subject family [this being iterated in all subject families].

I would like the user to be able to see what they select in each subject family, even if they leave that subject family and come back. So currently I am using check boxes and a Boolean field on a new table.

I really like the look of select boxes that allows you to select and add control on jforbes post but I set it up already with the user being able to see the control title when selecting, and now I don't want to lose it because I like it haha.

I'll post what I did tomorrow.

Once again thanks for all the help and the ideas guys. Looking forward to your thoughts on my solution.

[jforbes]

@Z - I ride on your coattails. =)

[zmbd]

J, just about as often as I've ridden yours :0)

Anyway, your form in post#8 was almost exactly what I was thinking of; however, I didn't have the saved filter aspect in mind - nice job!

[Brilstern]

Just realized I never uploaded a working sample. I know its probably not the best or prettiest solution but it works. Thoughts?