Combining transaction amounts (credit and debit)?

[Anonymous]

Hello,

I'm not quite sure how to phrase this so I've given an example below.

I'm having issues combining amounts for a location where inventory is moved from one section to another and a transaction occurs much like a debit and credit (a positive amount and a negative amount net 0)

I have a user who would like to see a location, or section, and see the transactions associated with it. 

For example, a user would like to pick Loc1 and see the net of the amount moved and each transaction whether the section is a source or destination.  The example below would net 150 as the transactions on 1/21 would cancel each other out and I would be left with the amount on 2/1.  The amounts, however, are always positive and if I simply used a SourceSection or DestSection as a selection criteria, I would be left with wrong data as amounts would not cancel each other out.

Location	TransNo	SourceItem	SourceSection	DestItem	DestSection	Date	Amount
A1	1	Item1	Loc1	Item2	Loc2	1/21/14	150
A1	2	Item2	Loc2	Item1	Loc1	1/21/14	150
A1	3	Item1	Loc1	Item2	Loc2	2/1/14	150

I'm thinking I need a "master" table and then split the table above into a source and destination table, but I'm not quite sure how to go about doing this.

Any help would be greatly appreciated.

Thank you in advance.

[maxgro · Accepted solution]

just un idea, hope it helps

[iktrayanov]

Maybe you should create a table like this

Location,

TransNo,

Item,

Section,

Amount,

Date,

Credit_DebitFlag \\ this should be 1 or -1 and will give you a way to calculate your amounts

[iktrayanov]

this is how you example data will look like.

Location	TransNo	Item	Section	Date	Amount	Credit_DebitFlag
A1	1	Item1	Loc1	1/21/2014	150	-1
A1	1	Item2	Loc2	1/21/2014	150	1
A1	2	Item2	Loc2	1/21/2014	150	-1
A1	2	Item1	Loc1	1/21/2014	150	1
A1	3	Item1	Loc1	2/1/2014	150	-1
A1	3	Item2	Loc2	2/1/2014	150	1

[Anonymous]

Massimo,

This is perfect and exactly what I was looking to emulate.  Thank you so much!