Help ... Need to find "Next to MAX" value

[ZanyJanie]

Hi all … I’m working on a customer billing system and this one has me stumped.

I need to figure out the average amount of each customer’s last two bills. On the surface, this seemed like an easy query; but I’ve run into problems because not all customers have charges each month.

The table I’m using has the following 3 fields (derived from a Union Query): Customer Statement_Date Amt_Billed

I have no problem finding the latest statement for each customer … I can use a totals query to group by Customer, then use the Max function for the Statement date.

My problem is in finding the “NEXT to MAX” record for each customer. Anyone know of an easy way to do this?

Thanks in advance!
Jane

 

[Paul_Bricker]

Not easy, but not hard. Here is the SQL for two queries. You will need to change the table names to match your table name.
In this first query, I add tblBilling to the QBE grid twice. When you add the same table twice, Access automatically adds the _1 to the name so it can differenciate between tables. I did not join any fields. If you can copy and paste this SQL into a new query, all you should have to do is change the Table name to your table name (I used the field names you put in your post).

SELECT tblBilling.Customer, Max(tblBilling.Statement_Date) AS MaxOfStatement_Date1, Max(tblBilling_1.Statement_Date) AS MaxOfStatement_Date
FROM tblBilling, tblBilling AS tblBilling_1
WHERE (((tblBilling_1.Statement_Date)<[tblBilling].[Statement_Date]))
GROUP BY tblBilling.Customer;

I named this query qryBilling.

Then I created a second new query. I added tblBilling twice, and qryBilling once. If you can just copy and paste this SQL into a new query, and change the Table Names, that would be easiest.

SELECT tblBilling.Customer, tblBilling.Statement_Date, tblBilling.Amt_Billed, tblBilling_1.Statement_Date, tblBilling_1.Amt_Billed, ([tblBilling].[Amt_Billed]+[tblBilling_1].[Amt_Billed])/2 AS AverageAmt
FROM (qryBilling INNER JOIN tblBilling ON (qryBilling.Customer = tblBilling.Customer) AND (qryBilling.MaxOfStatement_Date1 = tblBilling.Statement_Date)) INNER JOIN tblBilling AS tblBilling_1 ON (qryBilling.Customer = tblBilling_1.Customer) AND (qryBilling.MaxOfStatement_Date = tblBilling_1.Statement_Date);

If you have problems following this, just post the name of your Table, and I'll rewrite the SQL to include your table name. Then you would just copy and paste the SQL directly into the query.

HTH
Paul

 

[ZanyJanie]

Paul,

THANKS so much for your help!

Your first query gave me the two dates I needed. (I never would have thought to add the same table twice ... Give yourself a gold star for the day.)

For the second query I was able to simplify things a bit by using a totals query. I joined the Customer fields in the query table and my origninal data table. Then, I grouped by Customer, averaged the Amt_Billed, and used a statement to retrieve only the records WHERE the statement_date was BETWEEN the two dates in the query table.

Thanks again,
Jane

 

[Paul_Bricker]

Well done Jane. You'd be surprised at how many people couldn't get past the first query let alone work out something different in the second one.
Paul

 

[Jon K]

Paul,
I was interested in this topic and wanted to see how it could be done.

I copied and pasted the two queries and created a table tblBilling with these records:

Customer - Statement_Date - Amt_Billed
C3 -------- 5/13/02 -------- $123.00
C3 --------- 3/3/02 -------- $220.00
C3 --------- 6/3/02 -------- $320.00
A1 --------- 3/2/02 -------- $400.00
B2 --------- 3/2/02 -------- $100.00
B2 --------- 8/2/02 -------- $200.00
AA1 -------- 4/6/02 -------- $100.00


When I ran qryBilling, I got these results:
Customer - MaxOfStatement_Date1 - MaxOfStatement_Date
AA1 -------- 4/6/02 -------- 3/3/02
B2 --------- 8/2/02 -------- 6/3/02
C3 --------- 6/3/02 ------- 5/13/02

A1 was left out. And it seemed the second dates for AA1 (3/3/02) and B2 (6/3/02) were retrieved from C3.


When the second query was run, I got only one record. It was for C3:
C3 -- 6/3/02 -- $320.00 -- 5/13/02 -- $123.00 -- 221.5

---------
Jon

 

[Jon K]

The first query needs:

tblBilling.Customer = tblBilling_1.Customer

Without it, the dates in the whole table are being compared.

 

[Paul_Bricker]

Thank you Jon. My data had consecutive dates so I didn't notice any descrepancies. Adding the second Where statement does provide a more accurate return where your dates are not consecutive.

Paul