Help required selecting specific records from related tables

[pooh_bear]

Hi,

Hope someone can help me out on this one. Searched the forum but can’t find anything to help me out.

I have two tables:
tblCustomers that contains unique customer records (names addresses etc.)
and
tblComments that contains comment data for the customers

Tables are joined one to many tblCustomers -> tblComments on account_ref

tblComments can contain several records per customer, all are datestamped.

I want just the latest datestamped comment record per customer, just can’t seem to work the logic on this one. Must be so simple but can’t fathom it.

Ideally, I’m producing a report of Customer data (Name, address, contacts etc) + the last comment record for each customer. Have started with a simple query, but have hit an impasse. Have tried sorting, group by, top 1, distinct etc, but don’t seem to be getting anywhere.

If someone can point me in the right direction, and give me a starter for 10 I’d be much appreciated.

 

[georgedwilkinson]

select customerstuff, commentstuff, Max(dateinquestion)
from blah blah blah
group by customerstuff, commentstuff;

If you need to get rid of the Max field, just select from the above:
select customerstuff, commentstuff from
(select "text of above query").