Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I filter a table in this situation?

Posted on 2013-11-22
4
Medium Priority
?
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 39

Accepted Solution

by:
PatHartman earned 2000 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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