Select Case

[kempo]

Hi - I have a table called TBLDescription and need some help in extracting certain data. These are the fields,
SBN
CHANNEL
TYPE
COUNTRY
DESCRIPTION

These are my options -
CHANNEL = PUBL or HED or ITPE
TYPE = AC-SHORT or AC-LONG or SMP
COUNTRY = GB or UK or US

I need to query this table and pull one unique SBN(Book Number) and 1 unique DESCRIPTION. This is the logic.

IF COUNTRY = "GB" AND CHANNEL = "PUBL" AND TYPE = "AC-SHORT"
THEN DESCRIPTION

**If there is no description then it needs to try and find a combination that does have a description. So the next step would be GB, PUBL, AC-LONG etc.

It is possible for all options to have a description, so in that case the AC-Short takes priority.

Any ideas on how I can accomplish this?

 

[dcx693]

If you spell out all the rules, then it's possible to have a description for all possible combinations of field values. From the sounds of it though, Description should not be a stored value in your table since it seems like it depends on the other field values within the same record. If that's the case, you're better off just calculating the value using a query that makes a call to a custom function (you could use a series of nested IIf functions, but that gets complicated fast).

The title of your post suggests you might already know how to handle this: using a Select Case type of construct within a custom function.

If that sounds right, write back and we can help you along.

 

[kempo]

Hi - Here is some sample data from the table. The purpose of this excercise is to create a report with one ISSN and one description. Which descrption to use is the key. I need to use a process of ellimination.

CHANNEL ISSN TYPE COUNTRY DESCRIPTION
PUBL X030X4903X ACADM-SHRT US VBA
PUBL X030X4903X ACADM-LONG US VBA
PUBL X030X4903X INET US VBA
PUBL X030X4903X P/R US VBA

CHANNEL ISSN TYPE COUNTRY DESCRIPTION
PUBL X030X4XXX0 ACADM-SHRT US
PUBL X030X4XXX0 ACADM-LONG US VB.NET
PUBL X030X4XXX0 INET US VB.NET
PUBL X030X4XXX0 P/R US VB.NET

If a descrption is found in the first criterea use it else go until you find a description.

 

[Mile-O]

Sounds like you'd be better served by a many-to-many relationship between your books table and your genre/subjects table.