Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35

    Lightbulb Using SQL switch function MS Access

    I need to know how to use SQL switch fuction in MS Acces. Simple SELECT query is like this but it is not working,

    SELECT Switch(Habitat=T,"Terrestrial",Habitat=A,"Aquatic" ,Habitat=E,"Epiphytic")


    FROM MainCharacteristics;

    I need to display "Terrestrial" insted of "T" like wise,

    Pls help,

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    Habitat="T"

    Though it would probably be more flexible to have the values in a table and join to that table in your query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Oh, Thanx !
    Finally I got it,

    SELECT
    Switch(MainCharacteristics.[Habitat]='T','Terrestrial',MainCharacteristics.[Habitat]='A','Aquatic',MainCharacteristics.[Habitat]='E','Epiphytes', True,'Error') AS Ecosystem
    FROM MainCharacteristics;

    I didnt understand what u mean by "to have the values in a table and join to that table in your query". Is it more easy than my method?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Typically I would have a table that had two fields, one holding the "T" and the other holding "Terrestrial". It would contain a record for each of your items. It becomes easier via any number of methods to get the description associated with a letter code. If you got a new Habitat you'd simply add a record to that table. With your method, you have to find the various places in the application where you may have hard-coded that relationship, as you have here.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Oh, That seems v. effective. Thaxalot!
    This will be v. good when I divide habitats into sub groups (Shrubs, Herbs, Trees, etc.)

    So do I have to make a separate table to do that? I'm not an expert in VB.
    I'm only using MS Access. How 2 write the Query.
    Your method is very valuable, Really appreciate your kind help.

  6. #6
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Pls can u help me to solve this problem completely, Already it is solved, but need a more effective method.
    Please tell me How to do switch functions using separate tables. It will make my work easy, becasue I have 100's of abbrivates and numbers are increasing...

    I gave a try but not working ps help!
    I have 2 tables in my database (Main, Decode)
    In decode table there are 2 col's 1 contain Abbriviate (code) and other contains Orginal text.

    Code Orginal
    A Tree
    B Shrub
    C Herb
    D Climber
    E Ground cover

    Main table is the table I add data in Abbriviated (short form) format.
    basically it is like this (over 30 columns> ID species Genus Habitate Ecosystem Flowercolor)

    ID Habitat
    01 B
    02 C
    03 A
    04 E
    05 A
    06 A
    07 E

    I try to use below query to retrive orginal (Tree, Shrub,...) words insted of Abbriviates (A, B, C, ) As a column called "List" (As List)

    SELECT Sinhala1 AS Sinhala, English1 AS English, Switch(Main.Habitat='Decode.code','Decode.Orginal' ) AS List
    FROM Main
    WHERE Main.Habitat = Decode.Code

    It's not working , Pls help
    Last edited by sandlucky; 03-29-2011 at 12:42 AM. Reason: Updating info

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I was in Las Vegas all day yesterday (work, not play). Try

    SELECT Main.ID, Decode.Original
    FROM Main LEFT JOIN Original ON Main.Habitat = Decode.Code
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Dont need 2 say sorry, Really appreciate your kind help.
    I'm new 2 MS Access, SQL thing.
    Thanxalot! TC

  9. #9
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Oh, I got it ri8 with a slite change, It's v. effective.

    SELECT Main.ID, Decode.Orginal
    FROM Main LEFT JOIN
    Decode ON Main.Habitat = Decode.Code

    Cool, now I can Hard code all my Abbriviates. Great......Proble solved 100%
    Thanxalot!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oops; looks like I had a copy/paste error. Glad you sorted it out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    It's ok,
    Now I'm trying to add multiple columns (and multiple tables). Can u giv me a lil hint for that. I hav Abbriviates 4 FlowerColor, LeafColor, Climatezone, Canopy shape, etc.
    Thanx!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The way to learn is to switch your working query into design view, and see how the tables and fields look there. Then add one of your new tables and relate it in the same way, and add its field below.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35

    Thumbs up

    Thanx 4 reply

  14. #14
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Pls kindly help me to solve this query problem

    As give above I add 2 new col's to my Decode Table (Htcode, Htorginal)
    now it looks like;
    Code |Orginal | Htcode | Htorginal
    A ----Tree-----------S-------Short
    B-----Shrub---------M-------Medium
    C-----Herb----------T--------Tall
    D-----Climber

    In my Main Table I add S,M ot T to PlantHeigh column.

    Here code I try; not functioning, May b due to Relationship problem, dont know exactly. ID filed's r the primary keys of both Tables and Hav 1to many relation b/w them.

    SELECT Main.ID, Main.Species, Decode.Orginal As Habitat,
    Decode.Htorginal As Height
    FROM Main LEFT JOIN Decode ON Main.Habitat = Decode.Code
    or Main.PlantHeight = Decode.Htcode;

    pls help,
    Last edited by sandlucky; 03-30-2011 at 01:32 AM. Reason: spellings mistake

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What you're trying to do has been debated for some time, that is having one lookup table with multiple sets of columns in it ("one lookup table to rule them all"). Personally I don't like them, and I'd have a separate table for the 2 new columns. If you want to stay with the one master lookup table, you'll need to alias it for the second join, not use an "OR". In design view you'd add that table again, and join the second instance of it to the PlantHeight field in the main table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Case or Switch in an update statement
    By allenjasonbrown@gmail.com in forum Queries
    Replies: 7
    Last Post: 11-17-2010, 01:49 AM
  2. Replies: 3
    Last Post: 10-13-2010, 09:40 AM
  3. Replies: 6
    Last Post: 04-06-2010, 03:00 PM
  4. Replies: 6
    Last Post: 03-27-2010, 11:18 AM
  5. Switch and Tables
    By UtilityRyan in forum Database Design
    Replies: 0
    Last Post: 06-12-2007, 03:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums