Case or IIF?

N

NBullock

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?
 
B

Bob Barrows [MVP]

NBullock said:
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.
Click to expand...
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
 
J

Jerry Whittle

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.
 
M

Michel Walsh

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
 
M

Michel Walsh

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


Vanderghast, Access MVP
 
N

NBullock

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.
 
N

NBullock

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.
 
N

NBullock

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;
 
B

Bob Barrows [MVP]

I will try to reproduce this. Give me some time.
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;
Click to expand...
 
B

Bob Barrows [MVP]

Change it to this:

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

NBullock

Works!! Thank You!
Bob Barrows said:
Change it to this:

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


--
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.
Click to expand...
 
J

John Spencer

It is possible that you aren't using the correct set of wildcards. Try using
* in place of %.

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


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
N

NBullock

This worked fine, Thanks Bob.
SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)>0) AS Department
FROM Smdprtlst
Click to expand...
 
N

NBullock

Oops, I do have a problem:

ACCT = Accounting
CC = Cancer Treatment Center

ACCT comes up as Cancer Treatment Center


SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)>0) AS Department
FROM Smdprtlst
Click to expand...
 
N

NBullock

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

Works that same way
 
B

Bob Barrows [MVP]

Yes, you have a problem. A human is going to need to be involved in
assigning the departments to these printers ...
unless ... Is the printername exactly "ACCT"? If so, you could do this:

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

But if there's a third match to this pattern, all bets are off. You might as
well add a Department field to the Smdprtlst table and tell someone to fill
it in.
 
N

NBullock

Yikes.
The printer names always start with "I" followed by the department code,
driver and tray indicator . i.e. IACCTAT1 or ICC1AT1. The problem is that
the codes vary in length.

But, you last bit of code get most of them, so manula editing my not be a
big deal.
 
N

NBullock

This seems to work best. By putting "I" (what they all begin with) so, as
long as my codes don't start out the same ...

Thanks for all your help.

SELECT PrinterName, Nz(
(select Max(DepartmentName) From DepartmentCodes
where PrinterCode =PrinterName)
,(select Max(DepartmentName) From DepartmentCodes
where PrinterName like "I" & PrinterCode & '*')
) AS Department
FROM Smdprtlst;
 
Top