Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Unanswered: Access/SQL Assistance

    I think I have a rather simple problem, that probably has a very simple answer... I just cannot seem to find it :-(.

    Problem: I have one column, I need to create a second column with values that will be dependent on the values of the first column.

    I have been going down the line of using immediate if statements, however, Iam not sure if this is the best, or even viable solution.

    Below is an example column.

    field1
    ------------
    PIPIP024
    EDdlm41001
    EDlms31001
    EDlms31001
    PIPIP026
    null
    VTDPJ005
    QCDM100
    QCQT630
    QCQPE550
    null
    ZNUDPS007

    --------------------
    A second column will be created based on the content of the first.

    if PIP* then Project
    if ED* then task
    if VT* then group
    if QC* then appt
    if null then NotSpec
    if anything else then NotSpec

    ----------------------

    The result would look like this:

    field1 - field2
    ---------- | ----------
    PIPIP024 | Project
    EDdlm41001 | Task
    EDlms31001 | Task
    EDlms31001 | Task
    PIPIP026 | Project
    null | NotSpec
    VTDPJ005 | group
    QCDM100 | appt
    QCQT630 | appt
    QCQPE550 | appt
    null | notspec
    ZNUDPS007 | notspec


    Again, I cannot thank you enough for your willingness to help.

    Jerrold

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what are the possibilities for "anything else"?

    provided that "anything else" does not include "PI", "ED", "VT", "QC" you could be very close to a Left$(blah, 2) + lookup table + join and ...done?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2006
    Posts
    4
    izyrider, you are correct, 'anything else' is a value that is not "PI", "ED", "VT", "QC". So hypothetically, if a value was "OLI", the resulting field would be "notspec".

    I wish I could understand your solution, however, I am not certain I follow. :-(

    Jerrold

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    Jerrold

    You could use this rather unwieldy expression.

    IIf([field1] Like "PIP*","Project",IIf([field1] Like "ED*","Task",IIf([field1] Like "VT*","Group",IIf([field1] Like "VT*","Appt","NonSpec"))))

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Jerrold,

    attached is an outline of what i was thinking of
    - run qry2

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and if you don't like the two-step query, try:

    SELECT tblBlah.idBlah, tblBlah.strBlah, tblLookup.strWhatever
    FROM tblBlah
    LEFT JOIN tblLookup ON left$(tblBlah.strBlah, 2) = tblLookup.idLookup;

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    forgot the nulls: paste into query-SQL-view
    ...or replace " with ' if doing SQL in code

    SELECT tblBlah.idBlah, tblBlah.strBlah, nz(tblLookup.strWhatever, "notSpec")
    FROM tblBlah
    LEFT JOIN tblLookup ON left$(tblBlah.strBlah, 2) = tblLookup.idLookup;

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •