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.

and with what relationship types to the other two tables?
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.

Should I put Add and Remove quantities in separate columns?
Yes, without sign, the action determines plus or minus.

--
Build a little, test a little.


"SD707" wrote:

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!