Solved

How can I filter a table in this situation?

Posted on 2013-11-22
4
211 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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now