Re: Stock Table structure

Tech-Archive recommends: Fix windows errors by optimizing your registry

Dear Allen Browne,

Thank for the advide. I was not aware of the trick (for setting
TransactionTypeID to use 1 and -1) until reading your comment below. I think
this is a useful approach.

SF

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:Oo55jRVOHHA.4848@xxxxxxxxxxxxxxxxxxxxxxx
Both will work.

If one table (handling both ins and outs) works fine for your field
structure, there are advantages in using that approach.

If the TransactionTypeID is limited to 2 types (ins and outs), you might
consider using the values 1 and -1 and mark it as a Required field. You
can then calculate the stock balance as:
[TransactionTypeId] * [Qty]
If there are other transaction types, it might still be worth including a
TransactionDirection field that is 1, -1, or 0 (for in, out, and records
that don't affect the balance.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SF" <ssamnang@xxxxxxxxx> wrote in message
news:ulvYcWUOHHA.4604@xxxxxxxxxxxxxxxxxxxxxxx

I want to setup an inventory dabase and I was not sure what structure to
adopt. Below are two options, hope that someone would advice

Option 1
I just add another typTransactionType that will list all type of
transaction eg Sales,Purchase, Return

tblTransaction
TransactionID Long PK
TransactionDate Date/Time
CustomerID Number
TransactionTypeID Number

tblTransactionDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice


Option 2

I need to set up two tables for Purchase, Sales, Return etc...

tblPurchase
TransactionID Long PK
TransactionDate Date/Time
SupplierID Number

tblPurchaseDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice


tblSale
TransactionID Long PK
TransactionDate Date/Time
CustomerID Number
TransactionTypeID Number

tblSaleDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice



.

Relevant Pages

  • Re: Stock Table structure
    ... If one table works fine for your field structure, there are advantages in using that approach. ... If the TransactionTypeID is limited to 2 types, you might consider using the values 1 and -1 and mark it as a Required field. ... TransactionID Foreign key ... ProductID Number ...
    (microsoft.public.access.tablesdbdesign)
  • Stock Table structure
    ... TransactionDate Date/Time ... TransactionID Foreign key ... ProductID Number ...
    (microsoft.public.access.tablesdbdesign)