Inventory question (one table)
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, been off-line for a few days here over the holiday and just realized you had commented again.
Have you made progress with this or need more help?
Jim.
Have you made progress with this or need more help?
Jim.
ASKER
I may have more questions again about this subject, but feel good about it right now.
Hope you had a good holiday.
Hope you had a good holiday.
<<Hope you had a good holiday. >>
It was good, just too short<g>
<<I may have more questions again about this subject, but feel good about it right now.>>
OK.
Jim.
It was good, just too short<g>
<<I may have more questions again about this subject, but feel good about it right now.>>
OK.
Jim.
By the way, you may find this old thread interesting:
Algorithm resources for Inventory DB
https://www.experts-exchange.com/questions/22933888/Algorithm-resources-for-Inventory-DB.html
and especially comment:
https://www.experts-exchange.com/questions/22933888/Algorithm-resources-for-Inventory-DB.html?anchorAnswerId=20200394#a20200394
Take a look at tblTransactions.
Jim.
Algorithm resources for Inventory DB
https://www.experts-exchange.com/questions/22933888/Algorithm-resources-for-Inventory-DB.html
and especially comment:
https://www.experts-exchange.com/questions/22933888/Algorithm-resources-for-Inventory-DB.html?anchorAnswerId=20200394#a20200394
Take a look at tblTransactions.
Jim.
ASKER
For my question, I am going to give the Entities short names. Let's say that if [Transactor] can only have 0 and -1 as values.
If transactor = -1 then [Src_EntityID] inventory has been reduced and [Dest_EntityID] has increased by the same amount. If Transasactor = 0 then visa versa.
Example Table Entries
[Src_EntityID] [Transactor] [Dest_EntityID] [Quantity] <result for ABC>
ABC -1 XYZ 100 -100 <interim>
QRS -1 Abc 50 -50 <interim>
ABC 0 KLM 25 -25 <Current>
I've been thinking about your reply..
Would this query be at runtime in VBA? If this is a query were to be created in runtime, and if you are familiar with the SQL to get the result above for ABC, then this would be greatly appreciated.