Searching related records but only want to display primary

[DB88]

Hello,

I'm new to Access so please bear with me. I've built a search form that is bound to a query. Using a VBA code, I filter the query to show only those records that meet the user's criteria. It works great. However, I would like to improve the display. The table, Products has a 1 v Many relationship to Features using the junction table, LI_ProdFea. All three tables are in the query, because users search for products based on their features. If a product has more than one feature then it appears multiple times in the form but I just want it to appear once. More specifically:

The table, Feature has two fields: ID and FeatureName


The table, Product has two field: ID and ProductName
The table, LI_ProdFea has three fields: ID, kf_Feature and kf_Product
Product is connect to LI_ProdFea by ID=kf_Product. LI_ProdFea is connected to Feature by kf_Feature=ID

So, for product1 with ID=1, that is waterproof (ID =10) and battery operated (ID=11) there are two records in LI_ProdFea:
ID kf_Product kf_Feature
--- ------------ ------------
1 1 10
2 1 11

My search form will display product1 twice, once for waterproof and once for batter operated. But I just want to display it once. How do I do that?

[ranman256]

Then you must remove what is repeating...the [FEATURE]
you get id=1 twice...once for waterproof, once for battery.
Show only [product] and set query property [unique values] = yes
to get 1 record.

[DB88]

But if I don't show [Feature] in the query then I can't use it in my filter

[June7]

All records will display until filtered. No way around that. Why is that an issue?

[DB88]

I just want a unique list of products on my form. But my results after a filter look like this:
Product
--------
product1
product1

product is displayed twice, once for waterproof and once for batter operated. I just want to display it once. I get what ranman256 is saying, if I uncheck "show" in the query builder for [feature] then I will see a unique list of all products as the query result. BUT I can't filter if the field doesn't exist!!!

Basically I would like group by Product and then hide the details. I don't think forms can group, so I will have to build a report. But the real question is CAN I HIDE THE DETAILS?

[ranman256]

If you dont have it , dont filter it.

[DB88]

I have to filter it; that is the whole point of the search form!

I want my users to select multiple features and see a unique list of products that have those features.

[June7]

Then can't use a form based on a query that joins the two tables.

Could use dependent combo and/or listboxes

or

an unbound combobox that lists the Feature/Product combinations and a form bound to Products table that is filtered based on the ProductID in the combobox.

[DB88]

I don't think what I'm trying to do is being communicated well. Let me try again:

Lets say I have 20 Products, 5 Features and 35 LI_ProdFea records. So, the query without filtering is 35 records. The user selects from a unbound ListBox the features waterproof and battery operated. (Note: right now I've only built an "or" search). They click the button to run my VBA code and now there are 14 results from the filter. In those 14 results, products are repeated because some products have both features. I don't want to display the repeats, I just want to produce a unique list of products wich would be 6 records.

This may seem trival now but when I include the ability to search based of fields in multiple related tables, these duplicates become unruly.

I've only been using Access for about 3 weeks, so I'm following Allen Browne's search criteria example (http://allenbrowne.com/ser-62.html) with his multi-select list box for filter example (http://allenbrowne.com/ser-50.html). I've created a query that has all of the tables I need for filtering. Then I build a VBA code to create the filter. What I'm getting from the responses is that this method will produce my desired filtered list but there is no way organize the records (aka create a subsummary and hide details) so that only a unique products show on this form. Can I add another step or form/report or query that would pull the filtered data but display it how I want? Or do I need to scratch my current plan? Because if all I end up with is a list full of duplicates, I mite as well just throw all the query fields on a datasheet and make my users filter like they would in excel.

[DB88]

What about incorporating a pivot table? If the results of the query and filter where exported to excel, then I could create a pivot table. I'd only enable the product field in the table and the result would be a unique list.

Could I mimic the same idea in Access? Could I change my continuous form into a pivot table and only enable the product field?

[John_G]

If your form that displays the results of the search contains [Feature], then you are very likely going to get the duplicates, because the query records are different for each feature. If you don't want to see the duplicates, then you can't display the data that causes them.

What you can do is this:

When the user clicks "Search", generate a "Select" SQL statement (based on the user's criteria) that will retrieve only the records you want to see. To eliminate duplicates, use "Select DISTINCT". Reset the form's recordsource to that new SQL, then requery it (Me.Requery)

HTH

John

[DB88]

Thanks John_G!!

I got it to work with very little trouble. You Rule!!

[June7]

Point is, if Products and Features are included in a simple SELECT query then the ProductID will duplicate, period.

Several methods have been suggested to do what you want. And yes, your idea of a pivot could result in one row for each product with features as columns. How many features are there? In Access this would be a CROSSTAB query or a Pivot view of table/form. Pivot view has dynamic filter functionality. Suggest you play with and see what you think. Be aware MS has removed pivot view functionality from Access 2013.