SQL for Financial Summary from Transactions

[carlmack]

I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.

I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.

I do not want to use record by record VB because it is slow and I have a million+ records.

Can anybody help ?

Transactions Table (input)

Account Period Amount
101 1 100
101 5 100


Account Summary (desired output)

Account Period Opening Movement Closing
101 1 0 100 100
101 2 100 0 100
101 3 100 0 100
101 4 100 0 100
101 5 100 100 200

PS I need to run on Access and SQL Server

 

[yeatmanj]

Don't know if this will help, but:

You could try a crosstab query wizard to get the basic idea, then switch it to SQL view to copy the SQL statement out of once it is generating the results you want.