How can I filter a table in this situation?
Posted on 2013-11-22
I have two tables, a Parts table and a Parts_Categories table. The two tables are joined by a common PartNumber field. In the Parts table, the PartNumber is unique. In the Parts_Categories table, the PartNumber is not unique and there are multiple rows with the PartNumber key that relate to the Parts table.
PartNumber (plus other fields)
1) partnumber1 . . .
2) partnumber2 . . .
1) partnumber1 CategoryA
2) partnumber1 CategoryB
3) partnumber1 CategoryC
4) partnumber2 CategoryB
5) partnumber2 CategoryC
I'm using a split form to view the Parts table data. On the form, I have an unbound field where I can select a specific category to filter by (ie. I can enter "CategoryB" into the field). I wish to filter the Parts table based upon whether PartNumber is associated with the specified category ("CategoryB" in this example).
I could easily accomplish this by basing the form on a query like:
SELECT Parts.*, Parts_Categories.Category
FROM Parts LEFT JOIN Parts_Categories ON Parts.PartNumber = Parts_Categories.PartNumber;
But that leaves me with a split form that shows the same PartNumber multiple times. I don't want that.
How can I filter the Parts table based upon a user-selected Category and still show only the unique PartNumbers in the split form?
I thought about creating a calculated field in the Parts table that would grab all the Categories from the Parts_Categories table and concatenate them into a comma-delimited string, but haven't figured out how to do that or if it's even possible. Doing that would allow me to have a single row for each PartNumber and still filter on whether or not the PartNumber was associated with the selected category.
Is there a good way to accomplish this?