Inventory Design

[rts]

I'm creating an inventory tracker for my construction company and I want to make sure I lay it out properly. The most important aspect of the database is the ability to see where materials are. I have a "material transfer" form for Superintendents to input which materials are going to and from their job sites. So far I have a materials table, a material transfers table, a locations table, a suppliers table and an employees table. I am trying to reference Access's template as I create mine, but it's different because theirs is based on items sold, whereas mine is more for items transferred. Is it correct that I do not store the quantity anywhere in the materials table? Let a query handle it? There will always be a job # from where the material is coming and a job # for where the material is heading. Any new purchases will be handled by a different form. What is the best way to use a query to do this math for me? Thanks for any help!

[June7]

What is the structure of the Transfers table?

I can imagine couple approaches to this.

Following relational database convention, Materials table would not have quantities - the on-hand quantity would be determined by query.

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[rts]

The material transfer is designed from an old paper form used. It has "Shipped From", "Shipped To" for the jobsite locations and then lines for material being transferred and qty. The Yard is the main storage area so things move from there to other job sites and often then between job sites. The idea is to have an overall tracker of where our materials are at any given time.

[rts]

I know it should be a query but I can't come up with how the formula would read to add to this location if it's in the shipped to and subtract from the other location if it's in the shipped from.

[June7]

Possibly would involve more than one query. Would really help to work with your structure and some sample data. Do you have anything built yet?

The transfer of inventory between sites is a twist I haven't encountered before.

One approach would be not to show a transfer between sites but to show return to main stock then a dispersal of material to another site.

[orange]

.... I have a materials table, a material transfers table, a locations table, a suppliers table and an employees table.

Please post a jpg of your Tables and relationships.

[rts]

That is the form my "Material Transfers" table is based on. The Employee, Location, Materials, and Suppliers tables have only the basic information you would expect to find in those tables. The "Material Transfers" table looks up records in the other tables to gets its information. My problem is what to do about quantity. I know calculations should be done in queries, but in my case would it be better to do a Quantity field in the Materials Table to account for the total?

[rts]

Or is there a way to say "If the location in the "Shipped From" (I'm using a job number here), subtract from that location's total quantity. If it's in the "Shipped To", Add to that location's total quantity"