Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

How can I filter a table in this situation?

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.

Parts table
      PartNumber      (plus other fields)
1)   partnumber1              . . .
2)   partnumber2              . . .

Parts_Categories table
      PartNumber          Category
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?
0
CNeeper
Asked:
CNeeper
  • 2
1 Solution
 
PadawanDBAOperational DBACommented:
Are you looking for something like this?

SELECT 
     P.*, 
     PC.Category
FROM 
     Parts as P 
          INNER JOIN Parts_Categories as PC ON P.PartNumber = PC.PartNumber
where
     C.Category = <category you're looking for>;

Open in new window

0
 
CNeeperAuthor Commented:
Thanks, but no, I don't think so. I've attached a screen grab of the form I'm playing with.

The form is configured to Record Source = Parts table. If there are 12 unique parts in the Parts table, I want exactly 12 parts to be listed in the datasheet portion of the split form (unless a filter is applied).

If I set the form's Record Source to a query, it would allow me to easily filter based on a user-selected category, but I would no longer see the exactly 12 unique Part Numbers. For instance, I could set the form's Record Source to the query I mentioned in my OP. It would let me filter on Category, but I'd then see multiple records for the same Part Number (one for each Category the Part Number is associated with).

I need to set the form's Record Source to a query that results in no more or less than the 12 unique Part Numbers and still lets me filter by Category, or I need some other way to accomplish the filter.
Parts-Screen-Grab.jpg
0
 
PatHartmanCommented:
You can only filter a recordsource by what it contains.  If you make the main form recordsource contain category, it will "duplicate" parts.  If you try to eliminate that duplication by using a totals query and not selecting any columns from the subtable, you will end up with a non-updateable form.  

Have you considered turning this upside down and going from the "child" to the parent so you select category in the main form and the subform contains parts.  

I'm not sure you can actually get this working with the built-in split form but it is easy enough to make your own split form.  I have many and a couple are three levels deep.  You can have all the functionality with only a couple of lines of code.  Only the splitter bar will present a problem.
0
 
CNeeperAuthor Commented:
I'm very new to Access and SQL and am learning as I develop this database. It didn't occur to me to turn this form upside down like you suggested, but I think that just might work. Thanks for giving me a different perspective!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now