How to show only the 'most recent' related record ?

[Will]

tblAccounts is main table with fields such as...
idAcct
AccountName
AccountCity
AccountPhone
etc

tblHistory contains a history of all prior contacts with the Account. There
is one record per contact. Some fields are...

idAcct
idHist
Date Date contact with the account was made
ContactNote Notes about the contact
Contact Type Called, Call-In, Site_Visit, etc
NextDate Next date to contact the account
NextToDo What to do next

We want to create a query that shows the name of each account and only the
most recent history record for that account.

but if we put field from the two tables into a single query we get one query
record for each history record... so accounts with say... 10 prior contacts
recorded... will have tine records returned by the query.

How can we only have the most recent contact record returned along with the
account name?

thanks for any help.

 

[Charles Calvert]

tblAccounts is main table with fields such as...
idAcct
AccountName
AccountCity
AccountPhone
etc

tblHistory contains a history of all prior contacts with the Account. There
is one record per contact. Some fields are...

idAcct
idHist
Date Date contact with the account was made
ContactNote Notes about the contact
Contact Type Called, Call-In, Site_Visit, etc
NextDate Next date to contact the account
NextToDo What to do next

We want to create a query that shows the name of each account and only the
most recent history record for that account.

qryHistoryMostRecentDate: SELECT idAcct, MAX(Date) AS MostRecentDate
FROM tblHistory GROUP BY idAcct

qryHistoryMostRecentRecord: join tblHistory to qryHistoryDate on
tblHistory.idAcct = qryHistoryMostRecentDate.idAcct AND
tblHistory.Date = qryHistoryMostRecentDate.MostRecentDate (make sure
that you use an INNER JOIN, i.e. one record from each datasource)

qryAccountAndHistoryMostRecent: join tblAccounts to
qryHistoryMostRecentRecord (again, use an INNER JOIN)

That should do it. Note that 2 and 3 are pseudo-queries. You should
have no problem assembling them in the design view if you're not
comfortable with the raw SQL.

 

[Will]

THE TASK:

I'm attempting to do this using the grid in Design View

First to find the most recent record in tblHistory

MY TWO ATTEMPTS:

I open the query design window and drag idAcct into the first column of the
Design Grid

Now I want to create a column that will find only the most current Date
(this will be the most recent record)

Tried 2 approaches...

A - drag Date into the 2nd column of the query Design Grid
then create a 'criteria' to only show the most recent (using the Max
function)

I couldn't get this to work so then I tried...

B - Create an expression in the top cell of the 2nd row of the query Design
Grid usint the Max function... like...
Exp1: Max([Date])

that didn't work either.

SO... MY QUESTIONS:

1 - How can I create a query in the query Design Grid to find only the most
recent record in tblHistory?
2 - How to tell Acess to 'Group' on the idAcct Field

Thanks for any help.

 

[Will]

Got it. I had to turn on the "Total" row in the query grid.

Now it is working fine.

Thanks for the help Charles.