aggregate functions

[Sirocco]

Is there a standard way to determine, in a query or set of queries, where
there are 3 fields (customer, date, phone number) what the phone number is
that corresponds to the max date? Using a single aggregate query, setting
the aggregate function to max for date, phone numbers can't be grouped
because then you don't get the max date (since each phone number is
different). Is there a way to do this so I get a single record for each
customer, with the phone humber that corresponds to the max date? I'd
really appreciate any advice.

 

[Rick Brandt]

Is there a standard way to determine, in a query or set of queries,
where there are 3 fields (customer, date, phone number) what the
phone number is that corresponds to the max date? Using a single
aggregate query, setting the aggregate function to max for date,
phone numbers can't be grouped because then you don't get the max
date (since each phone number is different). Is there a way to do
this so I get a single record for each customer, with the phone
humber that corresponds to the max date? I'd really appreciate any
advice.

Easy way is to make one query that gets the max date per customer.

SELECT customer max([date]) as MaxOfDate
FROM TableName
GROUP BY customer

Then use that and the original table as inputs in a second query.

SELECT customer, [date], [phone number]
FROM TableName
INNER JOIN QueryName
ON TableName.customer = QueryName.customer
AND TableName.[date] = QueryName.MaxOfDate

And change the field name of 'date'. That is a reserved word in Access.

 

[Sirocco]

This doesn't work if some phone numbers have a blank ReceivedDate, and when
you relate back in the 2nd query, you get multiple entries (multiple phone
numbers) for these records with a blank ReceivedDate. And I want to keep a
blank ReceivedDate as acceptable, otherwise ongoing maintenance will be
required to make sure it's non blank. Any other solutions?

Thanks.

Is there a standard way to determine, in a query or set of queries,
where there are 3 fields (customer, date, phone number) what the
phone number is that corresponds to the max date? Using a single
aggregate query, setting the aggregate function to max for date,
phone numbers can't be grouped because then you don't get the max
date (since each phone number is different). Is there a way to do
this so I get a single record for each customer, with the phone
humber that corresponds to the max date? I'd really appreciate any
advice.

Easy way is to make one query that gets the max date per customer.

SELECT customer max([date]) as MaxOfDate
FROM TableName
GROUP BY customer

Then use that and the original table as inputs in a second query.

SELECT customer, [date], [phone number]
FROM TableName
INNER JOIN QueryName
ON TableName.customer = QueryName.customer
AND TableName.[date] = QueryName.MaxOfDate

And change the field name of 'date'. That is a reserved word in Access.