Solved

Inventory question (one table)

Posted on 2012-12-21
6
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 57
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 57
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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