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

    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!




    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
    outside the rim
    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
    Saratoga Springs
    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

  4. #4
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    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