Doing calculations between recurring instances of the same item.

J

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
 
K

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top