Split Field into Two Types 1

[work4livinsean]

Hi everyone,

I am trying to create a query that will split a field into two types. The field has numerous certifications that are being kept (in text format). I would like to name some certifications 'CI' and others 'External'. Since there are many different types of certifications I prefer not to type in every certification and the corresponding 'CI' or 'External' into a table. Then compare it in a query. Rather, all the 'CI' certifications start with 'CI - '. Is there a way to match 'CI' certifications in a query and then match anything that is not 'CI' as 'External'?

Any help or suggestions is greatly appreciated! Thanks in advance.

 

[Golom]

Is this what you mean?

Select IIF(Left([Certification],2)="CI", "CI", "External")
       As [CertificationType], ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[work4livinsean]

I am guessing that is a Sequel Statement then, correct? I am a little new to this...I have written a sequel statement before but not one to create a query. Can you please explain or point me to a website, I would really appreciate that.

As for as the actual code there, I have a certification named 'Cisco'. I am guessing I need to change the code to look like this -->

Select IIF(Left([Certification],4)="CI -", "CI", "External")
As [CertificationType], ...

Thanks for your reply!

 

[Golom]

You did say that you are "... trying to create a query ..." and Access queries are always SQL statements (not "Sequel" ... that was an early precursor to SQL.)

You could do this in Query Designer by typing directly in SQL view or you could enter it in Design View in the form

CertificationType: IIF(Left(TableName.[Certification],4)="CI -", "CI", "External")

If you haven't used query designer before then I recommend that you do some playing to become familiar with how it works. Liberal doses of Access Help are advised. This is so fundamental to using Access that Access itself is probably the best teaching environment rather than some web site that (usually) assumes that you already know the basics.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[work4livinsean]

Alright, I understand now. I think I just had a brain fart or something...just wasn't understanding where I should put it at first. Okay, I put it in the design view for the query (which puts it in the SQL statement under a Where statement) but nothing shows up in the datasheet view. I even tried to change the 'CI -' to something like 'Micr' which stands for Microsoft (and is in the table). I even tried the entire Certification name but still nothing shows up. Do you know why this is happening and what I can do to make it work?

Thanks again for your reply.

 

[Golom]

Switch to SQL View in your query. Copy and paste your SQL here. We are having some confusion because I posted a partial SQL statement showing only a piece of an SQL SELECT clause and you are now discussing a WHERE clause.

We need to see everything in context to provide meaningful answers.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[work4livinsean]

Okay, first off sorry for this confusion. Here is the SQL Statement:

SELECT queCertification.APEX_ID, queCertification.certification
FROM queCertification LEFT JOIN queActiveAPT ON queCertification.APEX_ID = queActiveAPT.APEX_ID
WHERE (((queCertification.certification)=IIf(Left([queCertification].[certification],4)="CI -","CI","External")))
ORDER BY queCertification.APEX_ID, queCertification.certification;


Now, it seems that it should look for "CI -" in the queries that I am comparing and then replace them. It brings up nothing. I know you had the SQL statement a little different above, so I will keep working on it and post anything if I can figure it out. In the meantime, if you see what is wrong please let me know. Thank you for your help, really appreciate it. Thanks

 

[Golom]

Try something like

SELECT C.APEX_ID, C.certification

FROM queCertification C LEFT JOIN queActiveAPT A 
     ON C.APEX_ID = A.APEX_ID

WHERE Left(C.[certification],4)="CI -"

ORDER BY C.APEX_ID, C.certification;

to select those where "Certification starts with "CI -" or

SELECT C.APEX_ID, C.certification

FROM queCertification C LEFT JOIN queActiveAPT A 
     ON C.APEX_ID = A.APEX_ID

WHERE Left(C.[certification],4) <> "CI -"

ORDER BY C.APEX_ID, C.certification;

for the ones where it doesn't.

If you wanted to show ALL the records (both CI and Non-CI) then

SELECT C.APEX_ID, C.certification,
       IIf(Left(C.[certification],4)="CI -","CI","External") As [Certification Type]

FROM queCertification C LEFT JOIN queActiveAPT A 
     ON C.APEX_ID = A.APEX_ID

ORDER BY C.APEX_ID, C.certification;

I just introduced some table aliases to make the reading a bit easier. They are not strictly required.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[work4livinsean]

Alright I got it to work (well you got it). Thank you so much. I understand now too haha. Thanks.