Making one value equate to multiple values. 3

[nuct]

Hi, my problem is this.

I want the user to be able to state wether they are looking for a 'Home', 'Overseas' or 'EU' student. However students of each type may have one or more different values to indicate this fact. E.G. A 'Home' student may have either an A B or C.

I want it so that the user can enter, for example, 'Home' and return all the associated records, e.g. the students with A B and C in the relevant field.

Any help would be massively appreciated.

Simon.

 

[Cagliostro]

hew many tables do you have, and what are their structures? Ion Filipski

filipski@excite.com

 

[tlbroadbent]

One possibility is to build a SQL statement in VB code.

Dim strSQL as string

strSQL="SELECT * FROM Table_name WHERE col_name"
If txtCriteria="home" Then
strSQL = strSQL & " IN ('A', 'B', 'C')"
Else If txtCriteria="overseas" Then
.
.
.
Etc.

A more flexible alternative and one that I prefer would be to build a cross reference table of the selection criteria and corresponding values.
[tt]
CritCol ValCol
Home A
Home B
Home C
Overseas Q
Overseas S
EU K[/tt]

Create a search query that joined the first table to the new cross reference table and searched by the value entered by the user. See example below.

Select t1.*
From table1 As t1`
Inner Join tblCrit As t2
ON t1.colX=t2.ValCol
Where t2.CritCol=forms!frmSearch!txtCriteria Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Tim1]

Hi,

Depending on which RDBMS you are using, it will be slightly different. However, what you need is something like:

DECLARE @lv_student_origin VARCHAR(20),
@sql_cmd VARCHAR(300)

/* User input for origin goes into variable @lv_student_origin */

SELECT @sql_cmd = 'SELECT field1, field2, field3 etc FROM table_name WHERE id_field IN ('

IF @lv_student_origin = 'HOME'
SELECT @sql_cmd = @sql_cmd + '"A", "B", "C&quot'

IF @lv_student_origin = 'OVERSEAS'
SELECT @sql_cmd = @sql_cmd + '"D", "E", "F&quot'

IF @lv_student_origin = 'EU'
SELECT @sql_cmd = @sql_cmd + '"G", "H", "I&quot'

-- The above strings open with a single quote, then have double quotes around each -- individual letter

EXEC (@sql_cmd)

Hopefully that will give you what you want.

 

[tlbroadbent]

My reply was very Access specific. I apologize if it causes any confusion. I defintely need to pay attention to the forum.

Tim1 proposed a good solution for limited number of options. My 2nd suggestion would still work in another database excluding the reference to the Access form. Substitute a variable for the form name in the criteria. Or build the select statement in your client software.

Select t1.*
From table1 As t1`
Inner Join tblCrit As t2
ON t1.colX=t2.ValCol
Where t2.CritCol=@criteria Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[nuct]

Hi, the problem involves only one table called STUDENTS and the field that contains the letters that correspond to a students origin is called S_APPLICANT_RESIDENTIAL_CA and the RDBMS is Oracle. I quite like the idea of creating an extra cross reference table, i think i'll try it.

Cheers for the help,

Simon.

 

[arrowhouse]

The cross reference table seems the best solution to me - unless this is the only place your ever going to need it (unlikely i'd think).

If it is the only place then the case statement might be your best friend eg:
SELECT [whatever columns you need]
FROM [tables & join conditions]
WHERE ...[whatever conditions you have]
AND
CASE
WHEN student_type_field = 'A' THEN 'HOME'
WHEN student_type_field = 'B' THEN 'HOME'
WHEN student_type_field = 'C' THEN 'HOME'
...[etc for all your student_type_field values]
WHEN student_type_field = 'Q' THEN 'OVERSEAS'
ELSE 'Unknown'
END = @matching_parameter

@matching_parameter being the type of students which you want to see. You can also use this in the select so you can display the Home/Overseas clasification rather than the base 'student type'.

 

[Tim1]

Hiya,

If you are using Oracle, I do believe (from my limited knowledge of Oracle) that it contains a convert function or something similar that you can use, that works by you giving it the input value (or range of values) and the output value and it does the work for you.

Post your query in the Oracle forum, I am sure that somebody there should be able to help you.

Tim

 

[nuct]

Thanks guys, the cross reference table worked a treat.

Simon.