Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: New to Access--Please Help! :)

    First post....be kind :0)

    Okay, I have a simple db with a parent (Renter) table & child (Payment) table along with others to maintain rental income. I am trying to create a query which will return a list of renters who have NOT paid, on a monthly basis. I thought of filtering the payments by excluding those who HAVE paid prior to a certain date, but somewhere in all the tutorials, I overlooked how to "exclude" records.

    PaymentID is the primary key, RenterID is the linking key. The overall problem is that when I run a query ONLY the renters who have paid show up. If a renter hasn't brought in a payment, then I haven't entered any information to be "searched." I know there is a simple solution I have overlooked...but I am stumped.

    Any suggestions appreciated!
    Heather

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: New to Access--Please Help! :)

    Originally posted by chipperhoney
    First post....be kind :0)

    Okay, I have a simple db with a parent (Renter) table & child (Payment) table along with others to maintain rental income. I am trying to create a query which will return a list of renters who have NOT paid, on a monthly basis. I thought of filtering the payments by excluding those who HAVE paid prior to a certain date, but somewhere in all the tutorials, I overlooked how to "exclude" records.

    PaymentID is the primary key, RenterID is the linking key. The overall problem is that when I run a query ONLY the renters who have paid show up. If a renter hasn't brought in a payment, then I haven't entered any information to be "searched." I know there is a simple solution I have overlooked...but I am stumped.

    Any suggestions appreciated!
    You might try Is Null in the criteia of one of the fields that you omit if they haven't paid. You can use the <> (not equal to) operator to do the opposite of the = (equal to) operator. You can also restrict date entries by using the greater than, and less than (>, <) operators in date criteria fields.

    Best of luck.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    Thanks for the advice, but I still have a problem. For example, I ran a query to include RenterID, PaymentDate, and Payment Amount; criteria, PaymentDate<#9/15/03# and PaymentAmount Is Null. It's not showing any records, because I never entered payment records for people who didn't pay. (This is the first month any payments have been entered). I assumed because the Renter Table & Payment ID table were linked by RenterID, they'd be automatically recognized.

    Should I start out by entering a $0 payment for everyone on a fictional date, just to have them show up? I know this is a "noob" question....but I'm just starting out

    Thanks again!
    Heather

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15
    not knowing very much about your database, I'd say that entering $0 records into your payment table might be your best bet.

    for example: assuming that each of your renters has a rental lease for x months, every time you have a new renter (as well as existing renters with new leases) you could enter their payment schedule into the payments table.

    so joe shmoe is a new renter with a 12 month lease,

    enter joe shmoe into your renter table, and at the same time enter 12 records into your payments table, each with the rent due date and an amount of $0. You'd then update the $0 as payments are made.

    this way you can now query the payments table looking for $0 amounts and a delimited date to find the slackers.

  5. #5
    Join Date
    Oct 2003
    Posts
    15
    Thank you for the responses!

    I continued my search, and found this post which actually works perfectly for what I'm trying to accomplish...thought I'd post it here as well, in case anyone else was interested!

    On 14 Jul 2003 21:11:49 -0700, bulentnews@yahoo.com (bulent) wrote:

    >Database A: Has 100 entries of BOM items numbered from 1 to 100.
    >List B: Has 15 entries of items which I will not use in the next run
    >(numbered randomly).
    >Query C: I would like to get the list of remaining 85 items, along
    >with some attributes from Database A.
    >I would appreciate if someone can tell me how to do it either with the
    >query wizard or SQL, or both?

    A "Frustrated Outer Join" query is the ticket for this.

    Table A (*table*, please, not Database - the Database is the .mdb file
    containing all the tables, forms, and other objects) and ListB can be
    added to a Query. Join them by the item unique ID. Right now this will
    show you only those records in TableA that *do* exist in ListB, but
    fear not.

    Select the Join line in the query window and choose option 2 (or 3) -
    "Show all records in TableA and matching records in ListB". You should
    now see 100 records, 15 of which include the linking field from ListB
    and the other 85 of which have all ListB fields NULL.

    Finally put a criterion

    IS NULL

    on the Criteria line under the ListB joining field. This will exclude
    the 15 records which *do* have matches, displaying the 85 that don't.

    John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
    http://go.compuserve.com/msdevapps?loc=us&access=public
    Heather

  6. #6
    Join Date
    Oct 2003
    Posts
    15

    Exclamation

    Now I need to make the results date sensitive; I am trying to create a list of renters who've made payments late. This is an action I need to perform each month.

    So when I do the frustrated outer join with my Payment Received criteria "Is Null" and it works perfectly. But if I try to add date specific criteria (i.e. between #10/01/03# and #10/10/03#), it no longer works. Can anyone help, please? I'm in a time crunch

    Thanks in advance!
    Heather

  7. #7
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Lightbulb try a nested query

    SELECT ContactName,CompanyName, ContactTitle, Phone
    FROM Customers
    WHERE CustomerID IN

    (SELECT CustomerID FROM Orders
    WHERE OrderDate Between #04/1/95# And #07/1/95#)

    or
    Where x AND Y AND Z AND
    (TableX.FieldDate) Between [TextFrom format] And [TextTill format]

    with this you get an input field

Posting Permissions

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