CODEWITHSUNDEEP
ms-accessvbams-access-2007
Alfred Emsenhuber
Alfred Emsenhuber

Reputation: 45

Summing Columns up with some exceptions

I am working on a database with two different tables.
The first contains every change in the database like a transaction table. It contains the object that was bought/sold, how many of them where bought/sold, when this tranaction happend and in which place. The second table contains the total value of every object that should be available in those places.

Now here is my question:
I want to automaticly sum up every entry with the same object and location inside of table one and save this value inside of table two.
BUT
Sometimes there are special entrys in table one which should not be summed up with the other values. They should overwrite the value.

I have an example of how this summing up should look like:

n = normal value, s = special value   
n:  1  sum:  1  
n:  2  sum:  3  
s:  7  sum:  7  
n:  5  sum: 12  
n:  4  sum: 16  
n:  7  sum: 23  
s: 20  sum: 20

To help you help me I have some additional informations:
There are 4 columns inside of table one

The first one is called object and contains the object number for which this entry takes effect.

The second column contains the amount of that object. Whether it was bought or sold.

The third column tells me on which locations this transaction belongs to. Which also means that every object has different amounts depending on the location.

The fourth column contains an information why this transaction happend. It tells me if this transaction happend because I bought something or because I sold something OR because I counted my stock.
This is the special indicator which should tell my database not to sum up this value but instead overwrite the previous one with this.

The fifth and last column contains the date when this transaction happend. This is very important because the whole table is sorted by the date. And it tells when those special values come in place.

The other table just contains the summed up value for every object in every location.

Upvotes: 0

Views: 45

Answers (1)

JCro
JCro

Reputation: 696

This below will return the sum of each record for a particular Object 'MyObj' starting from the last instance of a 'Special' entry (inclusive).

(Untested)

SELECT Sum(a.Amount) AS TheSum
FROM tblMyTable a
WHERE ID_PK> = nz((
    SELECT max(ID_PK) 
    FROM tblMyTable 
    WHERE Object=a.Object AND IsSpecial=1
),0) 
AND a.Object='MyObj'

Upvotes: 1

Related Questions