Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Inventory question (one table)

Posted on 2012-12-21
6
Medium Priority
?
413 Views
Last Modified: 2012-12-27
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
Comment
Question by:peispud
  • 4
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38714472
<<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
 

Author Comment

by:peispud
ID: 38714647
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
 
LVL 58
ID: 38724130
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:peispud
ID: 38724435
I may have more questions again about this subject,  but feel good about it right now.

Hope you had a good holiday.
0
 
LVL 58
ID: 38724536
<<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
 
LVL 58
ID: 38724544
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question