Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    3

    Question Unanswered: Access query (Multiple values with comma separated)

    I have problem in querying data when multiple values are stored with comma separated. Here is the illstration,
    T1 has two column with Column1 and Values, Where Column2 contains multiple values with coma separated.

    Table T1 :

    Column1 Column2
    160524 10444.4;10615.0;10611.1;10936.2;10674.1;5088.9;109 33.0
    169367 7098.16
    172441 10444.7;10933.1;10444.9;10444.10;10936.2;7098.16;8 168.1
    173853 7098.16
    173987 7098.16
    176707 7098.16
    178463 7098.16
    178548 7098.16
    178700 10993.1;10859.1;7098.16
    178830 7098.16
    178966 8168.1;7098.16
    179037 7098.16
    180141 10444.7;10933.1;10444.9;10444.10;10936.2;7098.16;8 168.1
    180174 7098.16


    T2:

    T2.Column1

    7098.1
    10444.7
    10444
    10936.2

    T2 is table has one column where it contains set of values

    Now I want to get the all the records from T1 where T2 column values are present.



    I did in MS Acess query as:

    SELECT RecordID,FeatureNum FROM tblMetrics, (select fid from tblFIDs_PacketCore26_0)
    WHERE (InStr([FeatureNum],(tblFIDs_PacketCore26_0.FID))>0)

    But this record fetch the data even like 7098.16 (last record in T1) for value in T2 7098.1


    Could you help me on this .

    Thanks in advance

    With Regards,
    Dhari

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Normalize your schema. Repeating values in a row violate first normal form, whether they are semicolon separate lists in a single column or columns like: c1, c2, c3. etc. Once you fix the schema, you'll be able to query using standard SQL... Using what you've got, you'll have to resort to all kinds of trickery to get a working query.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agreed with Pat - violation of first normal form is no laughing matter.... but on the brighter side, how about some tricky trickster SQL?
    Code:
    SELECT DISTINCT T1.Column1, T1.Column2
    FROM T1, T2
    WHERE ';' + T1.Column2 + ';' LIKE '%;' + T2.Column1 + ';%'
    Untested naturally
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Dhari

    Welcome to the forum

    It's not good practice to cross post.

    Cheers

    moderator edit: threads merged, link to other thread removed
    Last edited by r937; 04-20-06 at 11:11.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moderator comment: seeing as how the query was using a microsoft access function (InStr), i decided to merge the threads into the Access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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