Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determining earliest date in a query function (Access 2000)

    We have a database table called

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining earliest date in a query function (Access 2000)

    What about using an initial contact date in the Client table. You could then find new clients by querying those records with an initial contact date in whatever quarter you are looking at.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Determining earliest date in a query function (Access 2000)

    Quick Question -

    Is your database setup such that Client Records are kept in one table and contact information such as date contacted is kept in a separate table with a one to many join between them. (i.e., One client can be contacted many times)?

    Or if there is just one table, how are the data fields setup?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Determining earliest date in a query function (Access 2000)

    Assuming all of your data resides in one table, you could do what you want by using several queries.

    Query 1 - Select All Distinct Clients that were contacted earlier then the date wanted. (You dont want to contact them again)
    Query 2 - Select All Distinct Clients that were contacted after the date wanted. (You may want to contact them again)
    Query 3 - Run the Find an Unmatched Query Wizard that uses query 2 as the query containing the results, query 1 contains related records, and link the queries on client. Select Client and/or Client and Date as the output.

    This should get you close to where you want to be.

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining earliest date in a query function (Access 2000)

    This is a good idea for future, but in initial design such a field was not included, so we're having to work without it. However, I will suggest modifying the table so that we include that field. Thank you!...Mary

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining earliest date in a query function (Access 2000)

    Hello, Gary...Yes, in the "Marketing Contacts" table the ClientName field is a lookup field from another table "Clients." I probably didn't make it clear, though. The sales people will continue to contact all clients again and again. In the initial design of the table, it was not thought of to have a separate field "Initial Contact Date" which would make things easier. So for now we have to find out all so-called NEW client contacts by querying just the dates field but like I said we need to know when the earliest date was the client was contacted in order to determine if it is NEW (first contact falls between a quarter's dates, e.g., 7/1/04 thru 9/30/04). I'll look at your solution again and see if I understand. Will be back in touch soon. Thank you...Mary

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining earliest date in a query function (Access 2000)

    Gary, I forgot to mention that the primary field in the table is an auto number "ID" field and yes a client can be contacted many times. There are other fields in the database table such as type of contact (e.g., phone, e-mail, visit, etc.), summary of contact, salesperson making the contact, etc., etc. Hope this helps...Mary

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining earliest date in a query function (Access 2000)

    Hello, Gary...Indeed, this did the trick. Using 3 queries as you suggested. I had never used the "Find an Unmatched Query" wizard before. This will save us alot of time! Many, many thanks for the solution to our problem! Sincerely...Mary

Posting Permissions

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