Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

HOW TO CREATE A FILTER QUERY?

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
cssc1
Asked:
cssc1
2 Solutions
 
plummetCommented:
If a user selects Red and Green, do they have all 6 colours available or just RedGreen? (And GreenRed?)
0
 
dqmqCommented:
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
 
cssc1Author Commented:
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 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
cssc1Author Commented:
LSMConsulting:
   You bring up some interesting points. Can youi provide a simple example?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
cssc1Author Commented:
LSMConsulting:
   Thanks for spending time to help me. The example really explains it well. Also, you are right, you must mix it throughly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now