Solved

HOW TO CREATE A FILTER QUERY?

Posted on 2011-09-15
7
389 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

896 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

18 Experts available now in Live!

Get 1:1 Help Now