Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Location
    China
    Posts
    54

    Unanswered: Query to show only most recent contacts

    I am designing a simple contact relations database.

    I have designed a query (PersonContacts) that compiles 2 tables (Person and Contacts). The Person table lists all the people the firm has contact with. The Contacts table is a list of every contact made with each person.

    Contacts has a date field and the PersonContacts query is sorted by this so that most recent contacts are display first etc.

    Problem is I can't find a way to only show the most recent contact for any one person. I have tried using the SELECT DISTINCT SQL statement, but still get all contacts listed.

    I think maybe I need to use some sort of counter variable in the contacts table (i.e. so I can use a WHERE counter = 1 statement to select the most recent contacts), but am not sure how to do this either.

    All suggestions greatly appreciated.

    Richard.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    You said, your contact Table has a date Field, suppose it is called ContactDate, and you want to find the recent contacts till last week then

    PHP Code:
      SELECT FROM Contact
        WHERE ContactDate 
    >= Date()-

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You could do with an aggregate query by the sound of it. Click on the Totals button in design view. Any data you want to show, enter "Group By" in the Totals row in the design grid, and put Max in the Totals row for date. The more columns you Group By, the more likely you are to end up with more records than you want so start with only a few colums. The SQL, if you prefer, would be similar to the below:

    SELECT contact.name, Max(contact.ContactDate) AS LastContact
    FROM contact
    GROUP BY contact.name;

    You can add more tables and stuff too (I doubt you have the contact name in the contacts table but this just an illustration), so the above is just a starting point.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Quote Originally Posted by pootle flump
    Hi

    You could do with an aggregate query by the sound of it. Click on the Totals button in design view. Any data you want to show, enter "Group By" in the Totals row in the design grid, and put Max in the Totals row for date. The more columns you Group By, the more likely you are to end up with more records than you want so start with only a few colums. The SQL, if you prefer, would be similar to the below:

    SELECT contact.name, Max(contact.ContactDate) AS LastContact
    FROM contact
    GROUP BY contact.name;

    You can add more tables and stuff too (I doubt you have the contact name in the contacts table but this just an illustration), so the above is just a starting point.
    Pootle,
    this looks like what I may have been searching for. I have used Min & Max before with success, but couldn't get it to work this time. I will give your SQL ago.
    Cheers,
    Richard.

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    You make a form for your Persons table and you make a form for your Contacts table. You might also add a field to your Persons table to show last date.

    You now make a simple little macro and this assumes you have some sort of ID number to relate a record in Persons table to the records for his contacts in the Contacts table.

    Let's call the form based on the Persons table PersonsForm and the form based on the Contacts table ContactsForm. We will call the date field in the Contacts table DateContacts. The new field we added to the Persons table we will call LastDate.

    You place the macro on your PersonsForm, stick it on a label for On Click or a command buttom.

    You now make the macro to do the following:

    Open the ContactsForm where the records match the record that is on display and then go to the control DateContacts and sort the DateContacts field in descending order...then do a SetValue action in the macro to set the value of LastDate in Persons table to be the same as DateContacts in the Contacts table.

    You could then make the macro run through each record with a run macro action.

    Everytime you run the macro you will have the last date of contact in the Persons table's LastDate field.

    You now go one step further and call up all the records in the Persons table where LastDate is null and they will be the people that have not been contacted.

    Mike

  6. #6
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Quote Originally Posted by Trumpet
    Pootle,
    this looks like what I may have been searching for. I have used Min & Max before with success, but couldn't get it to work this time. I will give your SQL ago.
    Cheers,
    Richard.
    Pootle,

    Your syntax worked well.

    I used it in the PersonContacts query and found that I could GROUP BY any field from the person (ie. parent) table (such as PersonID, PersonName etc.), but had to MAX all fields in the contact (ie. child) table.

    I now have to work out a way to display the Note field from the child table - it is a Memo type so the MAX syntax won't work with it. I will probably create a further query that includes this one and the Contact table again.

    Cheers,

    Richard.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Richard

    The result sounds about right. The nature of agregate queries such as this is that they will show as few records (distinct rows) as possible, and this is dependant on the particular column selected and the number of distinct values within each one (and the agreagte used). A word of warning with what you have done though - you have joined two tables that have a one to many relationship. Bringing in columns from the many (child) table (defaulting to a Group By in the totals column) increased the number of rows returned, yes? Entering MAX for all such columns may make the number of rows returned seem right, but the way MAX (and MIN) works is to return the maximum value from all the linked recods. As such the return from one MAX column could be from a different record than the MAX of another column, making for misleading reading. What you really want to do (I suspect) is return the full record for the contact where the Contact Date = MAX([contact date]), which is very different. This would mean taking the query I provided, and using it as a sub query linked to contact where Contact Date = MAX([contact date]) and PersonID = PersonID.

    If this makes no sense let me know and I'll provide an example. Agregate queries aren't very intuitive but once you get your head round them they are easy to use (and very useful), if a little difficult to explain. BTW, the above would solve your Memo issue. You would end up with the Memo entry from the last contact with the person.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Pootle,

    Thanks for that info. I had made several MAX fields in the 1st query so, although I hadn't picked it up yet, I am guessing incorrect results would be returned. Actually, I had used MAX/MIN on something else I was working on a while back (for similar reasons) and did notice some dodgy results at that time (never picked up the exact cause for it though).

    I think I can do what you said and simplify the 1st query so that only the Date field is MAXed, then make the second query (based on the first) to contain all the important other fields.

    Cheers, Richard.

    p.s. Does dbforums have a points system? ie. can I "accept" your answer to give you points?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Richard. I worked out what I was trying to say - If you have multiple MAX totals in a query, you need to bare in mind that each MAX is calculated independently from the others, so the values for each MAX column will probably be drawn from a different record from the others, so although everything looks fine it is actually meaningless. I think you were about there anyway. And your plan sounds spot on, BTW.
    ...and I have no idea about the last question.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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