I am using Access 2010
I have two tables.
<fields> [StockNum] [Src_EntityID] [Dest_EntityID] [Quantity]
These are all integer values. [SourceEntity] and [DestEntity] must be present in the "tblEntities" to be allowed in the "tblTransactions" table. The number of entries in tblEntities
table may vary.
StockNum = 5000, Src_EntityID= 100, Dest_EntityID= 200, Quantity = 5
StockNum = 5000, Src_EntityID= 300, Dest_EntityID= 100, Quantity = 5
As you can see, EntityID = 100 can transfer-in or transfer-out quantities of goods.
My objective is to be able to get the current inventory of any of the [EntityID] based on the transaction table in an efficient manner.
I've been exploring crosstab queries and see this as a contender. I would like to be advised if a crosstab query is a correct approach. Maybe there is a better way of doing this.
I've included a snapshot of the crosstab query. As I see it, I need to read two rows of data to calculate current inventory.