• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

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
0
peispud
Asked:
peispud
  • 4
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.>>

 No need for a cross tab.  A simple SUM() on the qty column will do.

 As for in /out's there are a couple of approaches:

1. Always use a positive number.

2. Use a positive number with a transaction indicator, 0 for in, -1 for out.

  Transaction Qty then is always [TransactionType]*[Qty]

  This allows you to do a credit or reverse transaction.

Where these differ is in how the transaction might be formatted.  For example, someone moves 1,000 instead of 100.  

Method #1:

StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200, Quantity = 1000
StockNum  = 5000,  Src_EntityID= 200,  Dest_EntityID= 100,  Quantity = 1000
StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200,  Quantity = 100

Method #2:
StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200,  Type = 0, Quantity = 1000
StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200,  Type = -1, Quantity = 1000
StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200,  Type = 0, Quantity = 100

You can't do that with Option #1.  With #1, you'd have to do:

StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200,  Quantity = 1000
StockNum  = 5000,  Src_EntityID= 200,  Dest_EntityID= 100,  Quantity = 1000
StockNum  = 5000,  Src_EntityID= 100,  Dest_EntityID= 200,  Quantity = 100


I would also add a tran type field ie. Sale, Purchase Order, Mfg Process, Cycle Count, etc.

a "source" field (which program generated the transaction)  and also

EntryDate - Date transaction was entered.
TransactionDate - Date inventory was affected

Jim.
0
 
peispudAuthor Commented:
Yes,  I do have the other fields that you mentioned.

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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
peispudAuthor Commented:
I may have more questions again about this subject,  but feel good about it right now.

Hope you had a good holiday.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
By the way, you may find this old thread interesting:

Algorithm resources for Inventory DB
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22933888.html

and especially comment:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22933888.html#a20200394

Take a look at tblTransactions.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now