creating an advanced listbox filter with multi-table query

[baulrich]

I have a listbox with that shows the results of a query of my company table. The listbox rowsource is set to show the results of that query.



I've already implemented a search box that allow users to enter a string of characters and then click search which tells the listbox to refresh by requery. To make this work, I have updated the "criteria" of the appropriate query fields to match the text box search string. For example company.company_name critera is (Like "*" & [Forms]![company-Filter]![SearchText] & "*")

I need to build a "filter" feature on this same form that allows users to filter the results in addition to just a text search. But the fields that the users' need to filter on are located in tables other than the company table. One field, subdivision_number is stored in table company_division. Table company has a 1 to many relationship with table company_division with PK company.id being present in both tables. Table division also has a 1 to many relationship with table company_division with PK divsion.subdivision_number existing in both tables. The other field users' need to be able to filter on is division.division_number.

I need help building a query that will filter the listbox based on the contents of form objects form.subdivision_filter and form.division_filter. Before the "filter" button is pressed, the listbox should show all records in table company. To accomplish this, I've been adding to criteria, the clause "or [forms]![form1]![division_filter] Is Null" to make sure all company records show, not just those with records in the company_division table. When the button is pressed, the query should filter based on the two form objects just mentioned. But, the listbox should always show only one entry for each company, even though that company.id may appear multiple times in the company_division table. This is the part I'm struggling with.

Note: the divisions are all 2 char text fields like 01, 02, 03. The subdivisions are all 5 char text fields like 01001, 03009. The first 2 chars of the subdivision are always equal to the division. If it makes this query more simple, I can add a calculated field to table company_division that will store the division number by "calculating" it since it is always the first 2 chars of the subdivision. This may make the query less verbose since it will only have to join one table instead of also jumping over to the division table.

I've attached the relationship diagram for these 3 tables.

If possible I'd like to accomplish this by using the access query designer, to avoid building the SQL commands with VBA. But that may not be possible.

thanks,

baulrich

[orange]

I must be missing something with your diagram - seems a Company Has Divisions and a Division would have Subdivsions.
You don't seem to have represented that in the picture.

Please tell us exaclty WHAT you are trying to do and how the picture supports tgat.

[baulrich]

seems a Company Has Divisions and a Division would have Subdivsions

These divisions and subdivisions are not entities with in a company. They are divisions of work with subdivisions and a company does not have divisions. It only has subdivisions via the 1 to many relationship of the company and company_subdivision tables. Subdivisions are then related to divisions. The diagram is definitely correct.

I've included a screenshot of the form, which should help present an idea of the goal. I should have included it at first.

Update: I've basically solved this problem by creating "nested" (not sure if that term is correct) queries. Query 1, using criteria from the form, filters based on the division_filter chosen by the user (but importantly includes all companies with no defined subdivisions). Query 2 uses the results of Query 1 and then filters using the subdivision_filter from the form. The final query 3 uses the results of query 2 to sort by company_name and filter on DISTINCT VALUES (was not aware of this feature). All of this was sort of trial and error, but it seems to have the desired effect.... allowing users to filter the company DB by division or subdivision.

Still interested to hear if there are better/more efficient ways to do the same thing.

Screenshot shows the listbox after a successful filter using subdivision.

thanks,

baulrich

[orange]

Glad you have it working.