Solved

HOW TO CREATE A FILTER QUERY?

Posted on 2011-09-15
7
395 Views
Last Modified: 2012-08-13
HOW TO CREATE A FILTER QUERY


MS ACCESS OBJECTS
My DB has three tables:
1 – Car
2 – CarColor
3 – CarColorMix

PROBLEM BACKGROUND
The idea is the car buyer first selects the type of car (Ford, Chevy etc…) then selects the car color or colors (Red, Blue etc…) then the user selects the mixed colors which are based on the colors he/she previously selected. So, for example if the car buyer makes the following selections:
Care Type: Ford
Car Color/Colors:  Red and Green
The query needs to display the colors that can be made from Red and Green (the colors must be in the DB for the user to select)
EXAMPLE: Red could be – RedBlue, RedGreen, RedYellow
                   Green could be – GreenRed, GreenBlue, GreenYellow

PROBLEM
Create a query that filters the users color selections and gives the user (on a form with checkboxes) all the color mixes based on their color selection to pick from.

DB-Select.mdb
0
Comment
Question by:cssc1
[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
7 Comments
 
LVL 10

Assisted Solution

by:plummet
plummet earned 250 total points
ID: 36544009
If a user selects Red and Green, do they have all 6 colours available or just RedGreen? (And GreenRed?)
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36544290
Please reconsider a different table design, such that you can maintain colors and color combinations as rows instead of as columns.  In your design, if you get a new car, then you add a row to the car table.  But if you get a new color, then you must add a column to the car color table.  Adding a new car is a data change; adding a new color is a design change.  Big difference.

My suggestion is to maintain colors and color combinations as rows rather than as columns.

I've revised your sampe DB to illustrate what I mean. I also put together a quick and dirty form to illustrate one way to do the filtering process.  It's not exactly what you asked for and it's not exactly how I would do it, but it should give you some ideas.

 

 DB-Select.mdb
0
 

Author Comment

by:cssc1
ID: 36544479
plummet:

plummet:If a user selects Red and Green, do they have all 6 colours available or just RedGreen? (And GreenRed?)

ALL six colors
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 85
ID: 36544656
I don't see where Color and ColorMix are related, except that some of the field names are similar. This isn't the right way to build a database - your tables should have some method of relating one to another, if those tables are to be used in a relationship. For example, you should have a Join table to represent your ColorMix values. If you only allow a ColorMix to have 2 values, then you could build a table with two columns (Color1 and Color2), and store the ID values of the colors associated with that specific ColorMix. If the ColorMix can have more than two, or is variable, then you might need to use a different structure.

If you continue to force an association based soley on the Field names, how will you manage to work in updates when you must add new Colors, or new ColorMixes? You can add new fields to the "ColorMix" table, but you'd then have to alter all queries and code modules to take those new fields into account. Would you never allow a user to add a new color, or a new colormix? That would seem to be quite restrictive, and I can't imagine your users would put up with you (and only you) being able to add new Colors or Colormixes.
0
 

Author Comment

by:cssc1
ID: 36545271
LSMConsulting:
   You bring up some interesting points. Can youi provide a simple example?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 36548416
Sure - but what's the goal in this? Do you want to allow the user to select a ColorMix, and then do something later with the root values of that color? I'm not sure what your end goal is, although I would assume it has something to do with your other questions regarding Hazards and Categories.

In the attached db you'll find several tables. I've include tables to represent both a 2 Color ONLY join (where a single ColorMix can be made of 2 colors), and also tables to represent where a ColorMix can be made up of any number of Colors.

The tables are as follows:

Colors: the root colors, like Red, Blue, Green etc

ColorMix_2ColorsOnly: the table that holds a ColorMix that is defined by only 2 colors.

ColorMix_Multiple: Table that holds the ColorMix value where any number of colors could be used build that Color
ColorMix_Join: Table that holds all "root" colors that are included with each ColorMix from the above table

I've also included two queries to show you how they relate together:

qryColorMix_2ColorsOnly
qryColorMix_Multiple

Review the design of those queries for more details.
DB-Select.mdb
0
 

Author Comment

by:cssc1
ID: 36548648
LSMConsulting:
   Thanks for spending time to help me. The example really explains it well. Also, you are right, you must mix it throughly.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

627 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