Case or IIF?

Might there be a better way? I am trying to pull text from a field in one
table and then assign those records to that result. For example, if the
printer name contains "gen" it is a member of the "Medical" department or if
it contains "acct" then it is a member of the Accounting department - and so
on.

I do not know of any other way to make this comparison other than maybe a
case statement, but I don't know the syntax for that in SQL.

Can anyone help?

NBullock wrote:
Might there be a better way? I am trying to pull text from a field
in one table and then assign those records to that result. For
example, if the printer name contains "gen" it is a member of the
"Medical" department or if it contains "acct" then it is a member of
the Accounting department - and so on.

I do not know of any other way to make this comparison other than
maybe a case statement, but I don't know the syntax for that in SQL.

This is an Access group - CASE does not exist in Jet. Are you creating a
query to run directly in SQL Server? Your subject mentions both Case and
IIF so it is confusing what you want. Do you want to create a JetSQL
query (Access) or a T-SQL query (SQL Server)?

I'm also not clear what you mean by "assign those records to that
result": Are you saying you want it to return a string with the word
"Medical" or "Accounting" depending on what the printer field contains?

I would create a table called DepartmentCodes with two fields:
DepartmentName
PrinterCode
Start with the data you provided:
Medical gen
Accounting acct

Then, assuming the name of the table is Printers:
Select PrinterName,
(select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') As Deparment
From Printers

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

IIf works ok if nested only one deep, maybe two.

Case you can't do in SQL. You need to create it in a module and call the
function in a query.

There's a third choice: Create a table something like:

Printer Dept
gen Medical
acct Accounting

Then use that table to get the Department. It still can be done if 'gen' is
part of of a string if that's the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"NBullock" wrote:

Might there be a better way? I am trying to pull text from a field in one
table and then assign those records to that result. For example, if the
printer name contains "gen" it is a member of the "Medical" department or if
it contains "acct" then it is a member of the Accounting department - and so
on.

I do not know of any other way to make this comparison other than maybe a
case statement, but I don't know the syntax for that in SQL.

Can anyone help?

SELECT SWITCH ( printer LIKE "*gen*, (SELECT somefield FROM medical WHERE
.... ),
printer LIKE "*acct*", (SELECT somefield
FROM accounting WHERE ... ),
.., ...,
true, "unknown")
FROM somewhere


that can be very slow, though. That is using JET.


Vanderghast, Access MVP



"NBullock" wrote in message
...
Might there be a better way? I am trying to pull text from a field in
one table and then assign those records to that result. For example, if
the printer name contains "gen" it is a member of the "Medical" department
or if it contains "acct" then it is a member of the Accounting
department - and so on.

I do not know of any other way to make this comparison other than maybe a
case statement, but I don't know the syntax for that in SQL.

Can anyone help?

And note that each select statement in the SWITCH has to return just ONE and
only ONE row.


Vanderghast, Access MVP

Thanks - I will create the table and try as you suggested.

This is JetSQL.

What I mean by Assign those records to the result is, I that printers will
be grouped within the departments. That part is easy.


"Bob Barrows [MVP]" wrote in message
...
NBullock wrote:
Might there be a better way? I am trying to pull text from a field
in one table and then assign those records to that result. For
example, if the printer name contains "gen" it is a member of the
"Medical" department or if it contains "acct" then it is a member of
the Accounting department - and so on.

I do not know of any other way to make this comparison other than
maybe a case statement, but I don't know the syntax for that in SQL.

This is an Access group - CASE does not exist in Jet. Are you creating a
query to run directly in SQL Server? Your subject mentions both Case and
IIF so it is confusing what you want. Do you want to create a JetSQL
query (Access) or a T-SQL query (SQL Server)?

I'm also not clear what you mean by "assign those records to that
result": Are you saying you want it to return a string with the word
"Medical" or "Accounting" depending on what the printer field contains?

I would create a table called DepartmentCodes with two fields:
DepartmentName
PrinterCode
Start with the data you provided:
Medical gen
Accounting acct

Then, assuming the name of the table is Printers:
Select PrinterName,
(select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') As Deparment
From Printers

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Yes IIF can only be nested so far, I ran into that. Plus, it seems to be an
ugly way to go. I am creating a table trying to join to to it.



"Jerry Whittle" wrote in message
...
IIf works ok if nested only one deep, maybe two.

Case you can't do in SQL. You need to create it in a module and call the
function in a query.

There's a third choice: Create a table something like:

Printer Dept
gen Medical
acct Accounting

Then use that table to get the Department. It still can be done if 'gen'
is
part of of a string if that's the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"NBullock" wrote:

Might there be a better way? I am trying to pull text from a field in
one
table and then assign those records to that result. For example, if the
printer name contains "gen" it is a member of the "Medical" department or
if
it contains "acct" then it is a member of the Accounting department - and
so
on.

I do not know of any other way to make this comparison other than maybe a
case statement, but I don't know the syntax for that in SQL.

Can anyone help?

I have tried this, but it does not return a vaule for Department

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') AS Department
FROM Smdprtlst;





"NBullock" wrote in message
...
Might there be a better way? I am trying to pull text from a field in
one table and then assign those records to that result. For example, if
the printer name contains "gen" it is a member of the "Medical" department
or if it contains "acct" then it is a member of the Accounting
department - and so on.

I do not know of any other way to make this comparison other than maybe a
case statement, but I don't know the syntax for that in SQL.

Can anyone help?

I will try to reproduce this. Give me some time.

NBullock wrote:
I have tried this, but it does not return a vaule for Department

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') AS Department
FROM Smdprtlst;





"NBullock" wrote in message
...
Might there be a better way? I am trying to pull text from a field
in one table and then assign those records to that result. For
example, if the printer name contains "gen" it is a member of the
"Medical" department or if it contains "acct" then it is a member of
the Accounting
department - and so on.

I do not know of any other way to make this comparison other than
maybe a case statement, but I don't know the syntax for that in SQL.

Can anyone help?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Change it to this:

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst

NBullock wrote:
I have tried this, but it does not return a vaule for Department

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') AS Department
FROM Smdprtlst;





"NBullock" wrote in message
...
Might there be a better way? I am trying to pull text from a field
in one table and then assign those records to that result. For
example, if the printer name contains "gen" it is a member of the
"Medical" department or if it contains "acct" then it is a member of
the Accounting
department - and so on.

I do not know of any other way to make this comparison other than
maybe a case statement, but I don't know the syntax for that in SQL.

Can anyone help?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.