Solved

HOW TO CREATE A FILTER QUERY?

Posted on 2011-09-15
7
388 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
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 84
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 84

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

708 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

16 Experts available now in Live!

Get 1:1 Help Now