Avg. dollar amounts for last three dates

[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

 

[Brian Camire]

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.

 

[$P$G4....]

----- 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