Doing calculations between recurring instances of the same item.

[Jim Normile]

Table with fields as follows:
1. Record ID(PK-Autonumber); 2.Account No(TExt);3. Account
EXec(Text);4.Closing Balance(Amt-Currency);5.Date(Date)

So imagine there are 1000 accounts and we dump in the closing balance data
for each of them.

A week later we do the same thing .....Same accounts, just different closing
balances and a different date..............maybe also some new accounts.

I need to
1.calculate the difference in closing balance for the two instances of the
same account
2.Count and value any additional accounts and maybe attribute them to their
salesperson.
Any advice please??

Jim

 

[KARL DEWEY]

Try this ---
SELECT Jim_Normile.[Account No], Jim_Normile_1.[Closing Balance],
Jim_Normile_1.Date, Sum([Jim_Normile_1].[Closing
Balance]-[Jim_Normile].[Closing Balance]) AS Diff
FROM Jim_Normile INNER JOIN Jim_Normile AS Jim_Normile_1 ON
Jim_Normile.[Account No] = Jim_Normile_1.[Account No]
WHERE (((Jim_Normile_1.[Closing Balance])>[Jim_Normile].[Closing Balance])
AND ((Jim_Normile_1.Date)>[Jim_Normile].[Date]))
GROUP BY Jim_Normile.[Account No], Jim_Normile_1.[Closing Balance],
Jim_Normile_1.Date;