Inventory transaction table - set up

[SD707]

Hi,
I am a new user of Access 2007. I am creating a database to track inventory
quantities by locations. I have a Products table and a Locations table, and I
am wondering what is the best way to set up a Transactions table. We have
three types of transactions:
1. Move products from one location to another.
2. Introduce new products to a location.
3. Deplete products from locations.
We would like to keep a record of transactions, by transaction ID and date.
But the more important function will be to report on current Product
quantities by Location.

What fields should I include in the Transactions table, and with what
relationship types to the other two tables? Do I need more than one
Transaction table? Should I put Add and Remove quantities in separate columns?

Thanks for your help!

 

[KARL DEWEY]

What fields should I include in the Transactions table,
You can find templates on the web and Northwind should be adaptable for you.
I would have the following fields --
TransActID - autonumber - primary key
ProductID - foreign key
LocationID - foreign key
TransDate - DateTime
Action - (Move, Intro, Delete, and Inventory)
QTY - number
ALTLocationID - foreign key
ALTUpDated - Yes/No

Using Inventory to correct and bookkeeping and losses. Current balance from
last inventory.
One-to-many with referential integerty and cascade update.

Do I need more than one Transaction table?
No, but will need an append query to create a record for ALTLocation on moves.
Yes, without sign, the action determines plus or minus.

 

[Jeanette Cunningham]

With this part of the answer
Yes, without sign, the action determines plus or minus.

That should read
No, the quantities all go in the same column (quantity) without sign, the
action determines plus or minus.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[KARL DEWEY]

Thanks, I was in a hurry.

 

[cue232s]

I just found this thread.

I am looking to do the exact same thing. My question, is how do you incorporate sales?
Do yo handle that on your Transaction Table?
Say, if you sell an item that is in location 1, your transaction record would reflect "sold" for the transaction type field , and "location 1" for the location field? Also do you only use the Alt Location field when ever you are move items from one location to another? If so, do you relate your Key in the Locations table to the Alt Location field on the transactions table as well?

Thanks in advanced!

 

[chukwuebuka]

Hello pc crews, i'm a new member in access, how can i start a new work in my database window.