If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Case or IIF?
Works!! Thank You!
"Bob Barrows [MVP]" wrote in message ... 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. |
#12
|
|||
|
|||
Case or IIF?
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 NBullock wrote: Works!! Thank You! "Bob Barrows [MVP]" wrote in message ... 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. |
#13
|
|||
|
|||
Case or IIF?
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. |
#14
|
|||
|
|||
Case or IIF?
This worked fine, Thanks Bob.
SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes where Instr(PrinterName,PrinterCode)0) AS Department FROM Smdprtlst "Bob Barrows [MVP]" wrote in message ... John Spencer wrote: It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. |
#15
|
|||
|
|||
Case or IIF?
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 "Bob Barrows [MVP]" wrote in message ... John Spencer wrote: It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. |
#16
|
|||
|
|||
Case or IIF?
SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*') AS Department FROM Smdprtlst; Works that same way "NBullock" wrote in message ... 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 "Bob Barrows [MVP]" wrote in message ... John Spencer wrote: It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. |
#17
|
|||
|
|||
Case or IIF?
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. NBullock wrote: SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes where PrinterName like '*' & PrinterCode & '*') AS Department FROM Smdprtlst; Works that same way "NBullock" wrote in message ... 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 "Bob Barrows [MVP]" wrote in message ... John Spencer wrote: It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#18
|
|||
|
|||
Case or IIF?
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. "Bob Barrows [MVP]" wrote in message ... 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. NBullock wrote: SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes where PrinterName like '*' & PrinterCode & '*') AS Department FROM Smdprtlst; Works that same way "NBullock" wrote in message ... 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 "Bob Barrows [MVP]" wrote in message ... John Spencer wrote: It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#19
|
|||
|
|||
Case or IIF?
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; "NBullock" wrote in message ... 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. "Bob Barrows [MVP]" wrote in message ... 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. NBullock wrote: SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes where PrinterName like '*' & PrinterCode & '*') AS Department FROM Smdprtlst; Works that same way "NBullock" wrote in message ... 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 "Bob Barrows [MVP]" wrote in message ... John Spencer wrote: It is possible that you aren't using the correct set of wildcards. It's more than possible ... whap to the side of my head -- 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. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
|
Thread Tools | |
Display Modes | |
|
|