Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Inventory question (one table)

Posted on 2012-12-21
6
Medium Priority
?
414 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 59

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 59
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

572 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