troubleshooting Question

Inventory question (one table)

Avatar of peispud
peispudFlag for Canada asked on
Microsoft Access
6 Comments1 Solution435 ViewsLast Modified:
Hi

I am using Access 2010

I have two tables.

1)   tblEntities   <field>  [EntityID]

2)   tblTransactions <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.

 Example transactions

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.

Thanks

ps..
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.
Crosstab-query.JPG
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros