1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

'not like' query - access 2003

Discussion in 'Business Applications' started by Trieshard, Sep 29, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Trieshard

    Trieshard Thread Starter

    Joined:
    Jun 18, 2008
    Messages:
    8
    I am fairly new to access 2003 and self taught, but i have been asked to write a
    query that has me stumped and i need some help.

    I need to write a query that will return only the details ie name and address,
    of clients who have not paid their renewal fee - the amounts will vary from client to
    client as they are based on sales discounts and loyalty awards etc.

    At first, I thought that this should be quite simple however, any one client will have a number of payments made throughout the year, with different amounts being paid/refunded. this will make it impossible for me to use the 'amount paid' to find all those who have not paid a renewal fee. It seems the only way to see if they have paid their renewal fee is to use the data held in the 'payment description' which will describe everything from their 'initial fee', 'refund of overpayment', 'discount applied' as well as 'renewal fee'

    I only want to see those clients who do not have 'renewal fee' in the payment description fields and i'm finding it difficult to work out how i can do this. I have tried 'not like renewal fee', but this returns all the clients.

    Any Ideas or suggestions??

    the database has two tables, client and payments
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,910
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Trieshard, welcome to the Forum.
    You could try
    <>"renewal fee"
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    It sounds like you really should reorganize your data, with different fields for each type of payment - a different field for each of those types currently listed in 'payment description.'
    Then you would have a field named 'renewal fee' and you could just have a query showing only those where that field Is Null.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    slurpee, a "Table of Payment Description" would be ideal with a Combo for selecting the type.
     
  6. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,910
    :eek: Slurpee - that was what I read the first time, before re-reading more closely :eek:
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Perhaps Trieshard will enlighten us? :D ;)
     
  8. chaostheory

    chaostheory

    Joined:
    May 18, 2007
    Messages:
    114
    for the criteria of the query put

    Not Like "*renewal fee*"

    If that text is not in the comments in any form it should bring it up

    Edit: I do however recommend making that field a combo of choices that a user must choose from and turn on field validation. Otherwise if you mistype renewal fee even once, you will miss records. If the word fee is only in renewal fee, just in case you could also use

    Not Like "*Fee*" just to hopefully catch any possible typos
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    chaostheory, welcome to this Forum.
     
  10. chaostheory

    chaostheory

    Joined:
    May 18, 2007
    Messages:
    114
    Thanks :) Look forward to imparting my wisdom on as many people as possible lol.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Maybe I'm messed up here (not like that would be news!) - OBP, do you mean making a new table listing all the payment types except 'renewal fee', then linking it to the main table in a query to show all matching? Wouldn't you get duplicated listings then? Or maybe not...I should play around with this.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    slurpee, no I meant a table of all the Payment types, selected for the main table using a Combo box and storing the paymentTypeID. i.e good relational database design :)
    That way you could search for the PaymentTypeId or NOT the PaymentTypeId in question.
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Ah, that makes more sense. It's been too long for me, old friend, since I have done any real Access work. :(
     
  14. Trieshard

    Trieshard Thread Starter

    Joined:
    Jun 18, 2008
    Messages:
    8
    thanks for all your suggestions.
    i've tried chaostheory's suggestion of using Not Like "*renewal fee*" as this seemed the most straightforward for me however, i'm getting all the clients details returned even those who have paid their fee. I think this is because the clients have many payment descriptions over the year. I thought using <1 "*Renewal Fee*" might have worked as details of any clients that did not have Renewal fee in their payment description would be returned but for some reason this did not work either.
    I am unable to reorganise the data as the system we are using does not allow for this. I think i may have to declare defeat, it's so frustrating as i do feel it should be something that can be done.
     
  15. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,910
    are you using any joins in the query -
    if you do NOT LIKE Renewal Fee - then if a the same client has a record of say "discount applied" it will pull that record out

    you will need to qualify with some sort of a date - otherwise you will get all historical records
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/953194