Duplicate Records in a filtered query

[toyshoppe]

I'm trying to construct a subform selection list for a group of products that needs to be filtered by control on the parent form.

The problem is when I include the Category field in the subform's query it creates a product id row for each category it belong to.

What is the best way to construct a query(s) that will produce a list of Product ID's that can FILTERED by the product category and not product duplicates?
The only way I can see to do it is to use a subquery to first filter (using criteria's extracted from the controls on the parent form) and then query that query which will remove records with duplicate fields.
Thanks Jay

 

[RickSpr]

I don't understand. If your subform's query has a WHERE clause that matches the category to the main form's category field, then how could it be returning the same product ID with different categories? All the returned rows should have categories that match the one chosen on the main form.

Do you mean you're getting the same product ID with the same category multiple times? If that's it, you need to normalize the subquery's table.

You could use the DISTINCT keyword in the query to eliminate duplicates (an abbreviated version of what you had envisioned), but that might not work for you, depending on what the user would do with the list shown in the subquery. Maybe you just want them to click a row, and you'll take the product id from it. That could work. But if you want to get a key for the subtable out of it, using DISTINCT won't work because one row in the result table corresponds to potentially many rows in the underlying table.

Could you be a little more specific, giving the main form's and subform's queries and the master and child link fields? Also, how will the user use the subform? Rick Sprague

 

[toyshoppe]

Rick,
Your 3rd paragraph hit the mark. I'm only using to the result for the product id. I tried DISTINCT, but it requires that I include all of the fields that will be returned and if I filter by category I have to return that field and therefore DISTINCT will produce multiple rows, one for each category that the product belong to. I avoided this problem by using two queries as I described previously and I used DISTINCT in the second query to remove the dupicate rows, but I only needed to have the product ID field returned, so DISTINCT worked. I guess when I think more about what I'm trying to do, it is kind of an unnatural act as you elude to in your last sentence of paragraph three.

Is there a way to use DISTINCT on only some of the fields in a query?

In the beginning the main form had no query and the subform was a table of products. On the main form there is a cbo control that the user can select the category for the filter. I then use the VB afterupdate event on this control to build the filter for the subform after which I requery the subform. The user then can double click a product id on the subform to open the product detail form.

Thanks, Jay

 

[RickSpr]

I can't figure out what you're doing without more specifics. But let me suggest what I think you need to be doing.

First, the subform's RecordSource should be the table that contains product id's and categories (or perhaps a query that orders the table by product id). The subform should also have a Filter property that restricts the records shown to those for the category on the main form. If the main form is called Form1, and the category is in cboCategory, the subform's Filter property would be set to:
Category=Forms![Form1]![cboCategory]

You also need to set the FilterOn property to True. Unfortunately, you can't do that in Design View; for some reason Microsoft left that out of the properties window. So what I would do is, in the main form's Open event procedure, set the subform's FilterOn property to True:
Me.subformcontrol.Form.FilterOn = True

It seems to me that that should give you, in the subform, only the products for the selected category. A product ID should only appear once, unless the product ID/category combination occurs multiple times in the subquery's record source. (If that's the case, you'll have to write a Distinct query to use as the RecordSource for the subform.)

Try this and tell me if anything is still wrong. Rick Sprague