Avg. dollar amounts for last three dates

  • Thread starter Thread starter Attila Fust
  • Start date Start date
A

Attila Fust

I want to query the following table:

Client #, Invoice Date, Invoice Amount

For each client I would like to average the invoice
amounts for the last three invoice dates (ie. Max invoice
date + Max - 1 invoice date + Max - 2 invoice date).

Is there a specific function to do this or do I need to do
more than one step?

Thanks in advance.

Attila
 
For the last three invoices regardless of Client #, you might try a query
whose SQL looks something like this:

SELECT
Avg([Invoice Amount]) AS [Average Invoice Amount]
FROM
(SELECT TOP 3
[Your Table].[Invoice Amount]
FROM
[Your Table]
ORDER BY
[Your Table].[Invoice Date] DESC);

For the last three invoices for each Client #, you might try a query whose
SQL looks something like this:

SELECT
[Client #],
Avg([Invoice Amount]) AS [Average Invoice Amount]
FROM
(SELECT
[Your Table].[Client #],
[Your Table].[Invoice Amount]
FROM
[Your Table]
WHERE
[Your Table].[Invoice Date] In
(SELECT TOP 3
[Self].[Invoice Date]
FROM [Your Table] AS [Self]
WHERE [Self].[Client #]=[Your Table].[Client #]
ORDER BY [Self].[Invoice Date] DESC))
GROUP BY [Client #];

If you're using Access 97 or earlier, you'll need to replace the subquery in
the FROM clause into a separate query.
 
----- Attila Fust wrote: ----

I want to query the following table

Client #, Invoice Date, Invoice Amoun

For each client I would like to average the invoice
amounts for the last three invoice dates (ie. Max invoice
date + Max - 1 invoice date + Max - 2 invoice date)

Is there a specific function to do this or do I need to do
more than one step

Thanks in advance

Attil
 
Back
Top