Solved

How can I filter a table in this situation?

Posted on 2013-11-22
4
214 Views
Last Modified: 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.

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
Comment
Question by:CNeeper
  • 2
4 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39669810
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
 
LVL 1

Author Comment

by:CNeeper
ID: 39670010
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
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39670193
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
 
LVL 1

Author Closing Comment

by:CNeeper
ID: 39670404
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question