can you create new column in a query based off of data from two columns already there

[travismp]

I will need to create a new column in a query with data based off of two columns currently in my DB.

Current fields are: [TestType] & [TestReason].
New TEMP field will be [TestCombo]

TestType has 4 possible option via drop down.
TestReason has 21 possible options via drop down.
TestCombo will be one of 10 options depending on the data in TestType & TestReason.

examples: (here is an example of how the combo field will be populated. There will be ten total rules like this. one for each possible text option in the combo box.)
If [TestType] is "DRUG" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTDT".

If [TestType] is "ALCOHOL" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTAT".

If [TestType] is "DRUG" & [TestReason] is "N-PRE" or "N-OTHER" or "N-FUP" then [TestCombo] would be "NDDT".

The data does not need to be saved anywhere at all in the database, just generated when pulling this query. It will then be exported and dumped into another system.

Thanks. Here is a temp copy of the database with some junk data to maybe make it a little more clear.

 

[pbaldy]

I would create a function that accepted the 2 values as parameters and returned the appropriate value. Then in your query, call the function, passing the 2 field values. Within the function, I'd use Select/Case to evaluate the values (2 actually, one within the other).

 

[travismp]

Paul,

Thank you for the reply. That is over my head, but I will start reading up now. I understand that a function needs to be created in VB so I will starting reading so & see if I can make something work. It is good to know the best way to attack it so thank you for the blueprint.

I have no idea what you are talking about at all with this line: "Within the function, I'd use Select/Case to evaluate the values (2 actually, one within the other)."

 

[tehNellie]

you can use the switch() statment directly in the query SQL (or from design view):

SELECT SWITCH
(
  [TestType] = 'DRUG' AND [TestReason] = 'PRE', 'DODTD',
  [testType] = 'ALCOHOL' AND [testreason] = 'PRE' , 'DOTAT'
) as [testcombo]
FROM yourtable;

Both double and single quotes appear to work.

 

[pbaldy]

To me, when you need to evaluate multiple options, the Select/Case structure is easier to maintain than a series of If/Then/ElseIf statements. Given that you have 2 values to evaluate, I'd have a Select/Case on the first (TestType) and within each case of that I'd have another Select/Case evaluating the possibilities of TestReason.

Post if you get stuck. It isn't really that tough once you understand the structure of it, but that can be tricky to get past.

 

[travismp]

tehNellie,

Your style works pretty well & it is easy for me to follow. Can I add an or in there somehow?

[TestType] = 'DRUG' AND [TestReason] = 'PRE' or 'RAND' or "FUP', 'DODTD'

 

[travismp]

or do I need to make each one its own line

[TestType] = 'DRUG' AND [TestReason] = 'PRE', 'DODTD'
[TestType] = 'DRUG' AND [TestReason] = 'RAND', 'DODTD'
[TestType] = 'DRUG' AND [TestReason] = 'FUP', 'DODTD'

 

[tehNellie]

You should be able to use OR by including brackets ie:

SELECT SWITCH
(
  [TestType] = 'DRUG' AND ([TestReason] = 'PRE' OR [testreason] = 'RAND'), 'DODTD',
) as [testcombo]
FROM yourtable;

 

[pbaldy]

The reason I suggested the function rather than something like Switch directly in the query was maintainability/portability. If you need this anyplace else in the application, you'll have to recreate it there if you use Switch. If anything in the logic changes, you'll have to change it everyplace you have it. With a function, it's in one place you can call from anywhere and you only have to make changes in one place.

If this is something you really only need once, then Switch is easier. If it might be needed elsewhere, I'd go with the function. I suppose my opinion is colored by my experience, which has too often been that the "1-time" need changes over time, and I end up doing extra work. Now I do it the flexible way the first time.

 

[travismp]

Nellie,

This works perfect THANK YOU so much. great work, I told my coworker this would take me a month to figure out. instead I am done in one day that is GRETA!



Paul,

I see what you mean about only doing it one time and having the function around for other queries. Luckily I will only need it this one time. Thank you very much for the help today.


Once again thank you both, I will make everything else work from here.

Travis!