Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    23

    Unanswered: need a query on looking up for strings ended with "W"

    Hi the Access experts!

    Hope to have a query to solve this old pain in my neck..

    I am looking up from Table1 to Table2 to match the OrderQty for the PartNumbers, using Join Properties. I insert another Field and name it as [W-PartNumberOrderQty]. Can I have the query such that Table1

    PartNumber will look for OrderQty in Table2 for only those PartNumbers that end with a "W" and output the values in this inserted Field name as [W-PartNumberOrderQty] ?

    Thanks a lot!

    Table1

    PartNumber
    0220-34567
    2442-12345
    1120-44234
    5587211


    Table2

    PartNumber OrderQty
    0220-34567 4
    0220-34567W 5
    2442-12345W 10
    1120-44234W 12
    5587211 8
    5587211W 3


    Output Table

    PartNumber OrderQty W-PartNumberOrderQty
    0220-34567 4 5
    2442-12345 0 10
    1120-44234 0 12
    5587211 8 3

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You can add a field in your query grid that uses the Right$ function. Set the seacrch criteria ="W"

    Check the help file for details an this function and adding fields and functions to a query.

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    One solution would be to use the LIKE operator to look for a specific string.

    For example:

    SELECT PartNumber as [W_PartNumberOrderQty]
    FROM Table2
    WHERE PartNumber LIKE ("*W")

    The * is a wildcard.
    Jon Jaussi, OCP DBA
    D4L Data Managent Solutions
    D4L_solutions@comcast.net

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select Replace(partNumber, 'W', ''),
    SUM(CASE WHEN partNumber Like '%W' Then orderQty Else 0 END) As "W-PartNumberOrderQty",
    SUM(CASE WHEN NOT partNumber Like '%W' Then orderQty Else 0 END) As "OrderQty"
    from table2
    group by Replace(partNumber, 'W', '')
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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