Solved

How can I filter a table in this situation?

Posted on 2013-11-22
4
215 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 36

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

828 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