Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

HOW TO CREATE A FILTER QUERY?

Posted on 2011-09-15
7
Medium Priority
?
401 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 1000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 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