Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

'not like' query - access 2003


(!)

Trieshard's Avatar
Trieshard Trieshard is offline
Junior Member with 8 posts.
THREAD STARTER
 
Join Date: Jun 2008
Experience: Beginner
29-Sep-2010, 12:12 PM #1
'not like' query - access 2003
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
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,576 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
29-Sep-2010, 12:20 PM #2
Edit - reread - ignore me
have you tried " is Null"
http://www.techonthenet.com/access/f...ced/isnull.php
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
29-Sep-2010, 12:21 PM #3
Trieshard, welcome to the Forum.
You could try
<>"renewal fee"
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
29-Sep-2010, 12:34 PM #4
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
29-Sep-2010, 12:41 PM #5
slurpee, a "Table of Payment Description" would be ideal with a Combo for selecting the type.
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,576 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
29-Sep-2010, 12:43 PM #6
Slurpee - that was what I read the first time, before re-reading more closely
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
29-Sep-2010, 12:49 PM #7
Perhaps Trieshard will enlighten us?
chaostheory's Avatar
chaostheory chaostheory is offline
Computer Specs
Member with 114 posts.
 
Join Date: May 2007
Location: Elmira, NY
Experience: Advanced
29-Sep-2010, 12:57 PM #8
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
29-Sep-2010, 01:00 PM #9
chaostheory, welcome to this Forum.
chaostheory's Avatar
chaostheory chaostheory is offline
Computer Specs
Member with 114 posts.
 
Join Date: May 2007
Location: Elmira, NY
Experience: Advanced
29-Sep-2010, 01:12 PM #10
Thanks Look forward to imparting my wisdom on as many people as possible lol.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
29-Sep-2010, 02:43 PM #11
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
29-Sep-2010, 02:55 PM #12
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.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
29-Sep-2010, 02:57 PM #13
Ah, that makes more sense. It's been too long for me, old friend, since I have done any real Access work.
Trieshard's Avatar
Trieshard Trieshard is offline
Junior Member with 8 posts.
THREAD STARTER
 
Join Date: Jun 2008
Experience: Beginner
29-Sep-2010, 03:40 PM #14
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.
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,576 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
29-Sep-2010, 03:47 PM #15
are you using any joins in the query -
Quote:
i'm getting all the clients details returned even those who have paid their fee.
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
__________________
Wayne
Please let us know what the final solution was to any problem posted
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Solved: Access 2003 Form and Query SStarker Business Applications 16 04-Sep-2010 12:23 PM
Word 2003 Query Access 2003 mcollins4 Business Applications 1 14-Jul-2010 12:40 PM
Solved: MS Access 2003, Update One Field in Most Recent Record YoMamma Business Applications 2 07-Jul-2009 11:36 AM
Access 2003 and VBA MWE Business Applications 4 22-Jul-2008 06:48 PM
Solved: Accessing ActiveX Control response in Access 2003 mrmike65 Business Applications 3 25-Jun-2008 01:30 PM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑