Select most recent related record from related Table

**davecumber** · 07-02-2012, 03:27 AM

I have a DB with customers in one table and then related tables that contain each time we speak to them, each time they order something and each time there is a meeting with them.

I've been asked to setup a query that will show the user's details, from the customers table and then the most recent dated entry in the other 3 tables.
I know that this is asked for a lot and have tried a few of the solutions I have found but I get a variety of errors from each when I try them. I'm not sure if I've been translated the table names incorrectly.

Can anyone help me? I have got so far which gives me a list of all companies, with a date set in the column for those that we have never contacted.

Code:

Select contacts.*,iif(isnull([date chased]),'31/12/9999',[date chased]) AS [Date_Chased] from contacts LEFT JOIN [chases table] on contacts.id = [chases table].company
Thanks

Dave

**recyan** · 07-02-2012, 03:57 AM

You could try using the aggregate select query on the remaining 3 tables, by using the GROUP BY on the company & MAX on the Date field.
Then try joining them on the Contacts table with a LEFT JOIN with the Contacts Table on the left.

Thanks

**davecumber** · 07-02-2012, 04:21 AM

Is there any chance I could ask you to write out a quick example of what you mean, maybe with just one of the joined tables. Every time I think I am getting somewhere with the logic, I get an error. It seems like as soon as I start changing my queries I get errors.

**recyan** · 07-02-2012, 05:10 AM

Just check if below gives some guidelines :

Code:

SELECT 
    tblContacts.contactID, 
    tblContacts.contactName, 
    tblContacts.contactEmail, 
    qrySpokenContactsMaxDate.ContactID_FK, 
    qrySpokenContactsMaxDate.MaxOfSpokenDate, 
    qryMeetingsContactsMaxDate.ContactID_FK, 
    qryMeetingsContactsMaxDate.MaxOfMeetingDate, 
    qryContactsOrdersMaxDate.ContactID_FK, 
    qryContactsOrdersMaxDate.MaxOfOrderDate
FROM 
    (
        (
            tblContacts 
            LEFT JOIN 
            (
                SELECT 
                    tblSpoken.ContactID_FK, 
                    Max(tblSpoken.SpokenDate) AS MaxOfSpokenDate
                FROM 
                    tblSpoken
                GROUP BY 
                    tblSpoken.ContactID_FK
            )
            AS qrySpokenContactsMaxDate 
            ON 
            tblContacts.contactID = qrySpokenContactsMaxDate.ContactID_FK
        ) 
        LEFT JOIN 
        (
            SELECT 
                tblMeetings.ContactID_FK, 
                Max(tblMeetings.MeetingDate) AS MaxOfMeetingDate
            FROM 
                tblMeetings
            GROUP BY 
                tblMeetings.ContactID_FK
        ) 
        AS qryMeetingsContactsMaxDate 
        ON 
        tblContacts.contactID = qryMeetingsContactsMaxDate.ContactID_FK
    ) 
    LEFT JOIN 
    (
        SELECT 
            tblOrders.ContactID_FK, 
            Max(tblOrders.OrderDate) AS MaxOfOrderDate
        FROM 
            tblOrders
        GROUP BY 
            tblOrders.ContactID_FK
    )
    AS qryContactsOrdersMaxDate 
    ON 
    tblContacts.contactID = qryContactsOrdersMaxDate.ContactID_FK;

Thanks

**davecumber** · 07-02-2012, 05:42 AM

Thanks, That has worked perfectly.

**recyan** · 07-02-2012, 06:07 AM

Glad you found it helpful.

Thanks

**davecumber** · 07-02-2012, 07:07 AM

Yes, really helpful, thank you again. Of all the solutions I've seen, this was the first I could put into use. Plus I was able to understand what it is doing by simply reading the query.