Most efficient of querying a multiple condition table with wildcards (1 Viewer)

[jan@BRU]

Hello,
I have a question for something, which only seems obvious on the surface.

I have a lookup table that contains information (let's say minimum time for something) depending on the characteristics of some criteria (let's say material, shape, color, weight). The lookup table doesn't contain a record for all combinations of these criteria, but rather uses blank entries as wild cards. The following is an example:

[FONT=&quot]material/shape/color/weight/time[/FONT]
[FONT=&quot]wood/square/red/heavy/20[/FONT]
[FONT=&quot]plastic/circle/blue/heavy/30[/FONT]
[FONT=&quot]wood//red//50[/FONT]
[FONT=&quot]wood///light/40[/FONT]
[FONT=&quot]metal////100[/FONT]
[FONT=&quot]metal/circle/yellow/light/20[/FONT]
[FONT=&quot]////200[/FONT]


I now want to query this table and it should return the record (time), which satisfies the most criteria. So e.g. if I query for:

material = wood, shape = square, color = red, weight = heavy

it should return 20

if I query for:

material = wood, shape = triangle, color = red, weight = heavy

it should return 50

if I query for:

material = wood, shape, triangle, color = red, weight = leight

two entries satisfy this with two criteria, it could return 40 or 50, in which case I want to return the minimum.

I'm wondering, how to best do this. The "criteria" entries are in another long table. In reality, there is not only 4 criteria, but about 20, some of which are the details of others.

I could think of 2 solutions:

- Write a function in visual basic, which does several a dlookups (grrr....) or creates recordsets, which it searches.

- Create a complicated query, which I'm not exactly sure how to go about...

On the first, I'm worried about performance, as the criteria table could have many entries, and it would have to do many lookups/searches

On the second, I'm - as I said - puzzled on how to do that, and how to go about the wildcards.

Any advise would be highly appreciated...

Many thanks

Jan

 

[namliam]

I think I understand your requirement and think it cannot be done via a "simple" query

Because....
What do you do / want to heppen when you have say "wood ultra heavy" but in your lookup table you have:
Wood Light
Wood Medium
Wood Heavy

In a query you would get 3 records returned, but I dont think you want that.

I think you will have to resort to a function that opens a recordset of your function to find the closest match possible.

Or an alternative may be to use a "group by" query and get the min() of the value(s) you fetch.

The tricky part thought of either solution... what is the best match?

 

[jan@BRU]

The answer to this is easy: When none of the specified criteria are met: then takethe last entry, which doesnt have any criteria... 200 in that case.

Still I don't know how to do the query/function.

 

[dkinley]

I don't know if 'most efficient' ... but at this link I proposed a solution for a user that had user-selected criteria based combo boxes for the query criteria ...

http://www.access-programmers.co.uk/forums/showthread.php?t=155344

There is a diagram on my last post (page 2) that lays out the functionality of this process.

In this manner you can set criteria on specific fields only or mix and match field criteria.

Note: If it is a multiple-table query, you will need to use left-joins for the data to populate correctly.

Hope that helps.

-dK

 

[jan@BRU]

Although it seems a very straightforward thing, I guess, I can't express myself clearly enough.

There is a 'test' table, that would provide combinations like:

wood/square/red/heavy
plastic/circle/blue/light
metal/square/yellow/heavy
wood/triangular/red/light
...


and there is a 'criteria' table that either has "full" criteria set out, like:

wood/square/red/heavy/20 ... or
metal/circle/blue/heavy/30

if there is a full match between 'test' and 'criteria', it's easy, return the criteria record that has the match, e.g. 20 for the first record in 'test'.

If there isn't a full match, say there is a criteria entry, saying:

wood////50
metal/square///60
metal//yellow//70
metal////80

so everything that is wood, without being wood/square/red/heavy, should return 50, everything that is metal/square/don't care/don't care, should return 70, everything that is metal/don't care/yellow/don't care should return 70 and everything that is metal, without being metal/circle/blue/heavy or metal/square/don't care/don't care or metal/don't care/yellow/don't care should return 80.

So basically a variable set of 'test' records should be matched with a fixed set of criteria, where the criteria can have "wildcards" to avoid having to specify all possible permutations. The more specific criteria take precedence over the more generic ones...

Help! sorry for the confusion

 

[dkinley]

Okay .. I am with you now .... I am not sure if this completely solves your problems because I do not know all of the outcomes you have and the basis for assigning a certain outcome to a combination of characteristics. And I am leaving you some homework to see if this will work.

I've attached a demo of what you are trying to do. It makes prevalent use of the switch command. I've combined different coding techinques (not programming but telecommunications) in passing messages except I have went way outside of those bounds to make it easier to work with.

The first 4 columns of the query normalize everything down to a single digit. The 5th column combines all of the single digits into a number set of sorts. The 6th column then evaluates the expression and assigns a value based on where the number set is on a number line (for evaluation).

It makes heavy use of the switch function which should be apparent once you look at it.

Your homework is to create a matrix of sort (pen and paper/spreadsheet) so you can get the outcome you want. The thousandths place is the material, the hundredths is the shape, the tens is the color and the ones is the weight. They can be in different order but it seemed in your example this might be the best way to go.

So once you plot these in your matrix, you can determine what set of numbers on a number line is needed to satisfy a given outcome (ie, what makes one be a 50 verses a 60). We know the answer in English, but now you have to translate it into numbers. That is, unless you want to do a whole bunch case and if/thens in a module.

Anyhow, you wanted a simple query - here it is. However, it's the homework that will decide if it is successful or not.

Hope this helps.
-dK