473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

Brilstern
208 100+
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
Expand|Select|Wrap|Line Numbers
  1. {VBA} SELECT each control in family from tblControls and iterate to a checkbox.
  2. Check current memory to see if any are already selected
  3. Fill previously selected
User selects checkboxes
Expand|Select|Wrap|Line Numbers
  1. {VBA} write checkbox selection to memory after submit or new family is selected
  2.  
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!
Jul 26 '16 #1

✓ answered by 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.

14 1551
jforbes
1,107 Expert 1GB
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()
Jul 26 '16 #2
zmbd
5,501 Expert Mod 4TB
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?
Jul 26 '16 #3
PhilOfWalton
1,430 Expert 1GB
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
Jul 27 '16 #4
Brilstern
208 100+
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.
Attached Files
File Type: zip Sample.zip (1.88 MB, 75 views)
Jul 27 '16 #5
nico5038
3,080 Expert 2GB
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)
Jul 27 '16 #6
Brilstern
208 100+
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.
Jul 27 '16 #7
jforbes
1,107 Expert 1GB
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.
Attached Images
File Type: png FilteringSample.png (10.3 KB, 227 views)
Jul 27 '16 #8
PhilOfWalton
1,430 Expert 1GB
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,
Expand|Select|Wrap|Line Numbers
  1. tblSubjectFamilies
  2.     SubjectFamiliesID      Auto    PK
  3.  
  4. tblControls
  5.     ControlsID             Auto    PK
  6.     SubjectFamiliesID              FK
  7.  
  8. tblControlsEnhancements
  9.     ControlsEnhancementsID Auto    PK
  10.     ControlsID                     FK
  11.  
  12. tblCCI
  13.     CCIID                 Auto     PK
  14.     ControlsEnhancementsID         FK
  15.  
Probably need a bit of code to check that field "Identifyer" in the tblSubjectFamilies is consistent in the other tables, and so on down the string of tables.

Phil
Jul 27 '16 #9
Brilstern
208 100+
@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 (tblControlsBoolean) 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 tblControlsBoolean 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.
Jul 28 '16 #10
zmbd
5,501 Expert Mod 4TB
@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
Jul 28 '16 #11
Brilstern
208 100+
@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.
Jul 28 '16 #12
jforbes
1,107 Expert 1GB
@Z - I ride on your coattails. =)
Jul 28 '16 #13
zmbd
5,501 Expert Mod 4TB
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!
Jul 28 '16 #14
Brilstern
208 100+
Just realized I never uploaded a working sample. I know its probably not the best or prettiest solution but it works. Thoughts?
Attached Files
File Type: zip Sample.zip (747.8 KB, 60 views)
Aug 19 '16 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: diskoduro | last post by:
Hi! I'm trying to get help to an unexpected problem that has appeared while I was writing a new application. I want to opeon a report of product sales by filtering previously from a listbox in a...
8
by: Armando | last post by:
Here's one that's going to leave me bald before long - I have a report (no headers or footers, small detail section) that runs normally when I open it from the database window. When I run it...
1
by: Jimmy Stewart | last post by:
I have a continuous form with a list of items from a table. One of the fields in the table is "print". this allows the user to select items from the list on the form for printing in a report. on...
0
by: AA Arens | last post by:
I have a create report function showing reports for every record that is listed in the tblCalls table. In my situation one report per call that comes from a company. I would like to have a...
4
by: roryok | last post by:
Hi, (Access 2002 & Windows XP Pro) I have a Select query and one of the columns has the following criteria: (from SQL view) HAVING (((order.orderDate)>=!! And (order.orderDate)<=!!)); This...
4
by: MOCaseA | last post by:
I am trying to create a series of combo-boxes that filter the viewable records by the values listed. There are several combo-boxes per form and several thousand records that will be filtered. See...
3
by: jmborron | last post by:
I have an Access report that is populated by a parameter query. I need to run the report for 250 different filters then save. How do I write a code that will run the report then save it as pdf250...
12
by: MsTGordon | last post by:
I have a form based on a query where the user can filter and sort in any of the fields of the form. I am able to print preview a report once the user filters the report but if the user sorts the...
2
by: Vaulcul | last post by:
Hello, I have a report that is supposed to show the teachers that have taught specific classes this year. I'm almost done, I just need the report to filter the data before opening. In setting...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.