Running total in a form using a SQL Statement and tables

[Jinzuku]

Hi,

I'm trying to create a form that has a running total of the amount an organisation has been invoiced. 1 table (Database) consists of everything I've invoice out. The fields this table has is the Organisation code, name and amounts among many others.

I also have another table (Orgs) that is a comprehensive list of organisation codes, name and a blank field (Organisation_Total).

I've created a form whose Record Source is Orgs. My aim is to use a SQL Statement to populate the blank Total field automatically upon opening it, however I seem to be having some difficulty. I've tried multiple ways of doing it and I have the feeling I'm missing something simple.

The code I have so far is:

SELECT Orgs.Org_Code as Org_Code, Orgs.Org_Name as Org_Name, case when Database.Org_Code=Orgs.Org_Code then Sum(Total_Invoice_Amount) AS Org_Total else " "
FROM Database
INNER JOIN Orgs ON Orgs.Org_Code = Database.Org_Code 

Any help you can give me would be greatly appreciated.

Thanks

Sunny

[Jinzuku]

I think maybe the term "Running Total" was the wrong word to use, since that tends to refer to a cumulative total.

What I'm hoping to have is more an auto-refresh on the totals, so when more data is imported into the database the totals for each organisation will go up if they have been sent a new invoice.

[TheSmileyCoder]

I have uploaded a small example showing a way of doing it. If you have any questions please ask.

[Jinzuku]

Excellent, that worked like a charm.

Thanks TheSmileyCoder, you've now made me smile too :D

Sunny

[TheSmileyCoder]

Your welcome. Im glad to hear it made you smile.