View Single Post
  #2  
Old March 25th, 2010, 08:03 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Database to track inventory in company cars

To begin, you need a table to identify each material:
TblMaterial
MaterialID
fields that identify a specific material item

Then you need a table to identify each Van:
TblVan
VanID
fields that identify a specific Van such as make, model, year, vehicleID#

Since each Van carries the same materials and your post implies each Van has
the same reorder point for each item of material, you need a material
reorder point table:
TblMaterialReorderPoint
MaterialReorderPointID
MaterialID
MaterialReorderPoint

Now you need to record the inventory of each item of material in each Van:
TblMaterialInVanInventory
MaterialInVanInventoryID
VanID
MaterialID
MaterialInVanInventory

To generate a form or report that shows what items of material need ordered
for eac Van, you eed a query that includes all the above tables. In the
query, you identify the item of material from TblMaterial, you identify the
Van from TblVan. In the query you need a calculated field,
MaterialInVanInventory
(TblMaterialInVanInventory) - MaterialReorderPoint
(TblMaterialReorderPoint). Set the criteria of the calculated field to 0.
In the query you can sort by an appropriate Van field to put the Vans in
ascending order and you can sort by an appropriate Maerial field to put the
Materials to reorder for each Van in ascending order.

Steve


"Ginge" wrote in message
...
I have built a database for my company (a small electrical contracting
firm)
using various access 2007 templates. At the moment it contains customer
contact details, employee details, a tendering/job pipeline tracking leads
and jobs won, a warehouse register tracking our tools in/outs and a
vehicle
details which tracks the expenses on our company cars. I want to add to
the
vehicles portion a way to track the materials in each car so that we know
when to reorder stock. Each van contains the same stock/amounts but I want
to
be able to generate orders for each van when stock reaches a min. level
for
that particular car. I was thinking a table of inventory but im not sure
how
to allocate it to each car with their own min/max system (maybe a stock
location table??). Not sure what transaction tables/queries would be
needed
to perform this task.

Any help would be greatly appreciated