A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Database to track inventory in company cars



 
 
Thread Tools Display Modes
  #1  
Old March 25th, 2010, 04:31 AM posted to microsoft.public.access.tablesdbdesign
ginge
external usenet poster
 
Posts: 3
Default Database to track inventory in company cars

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
Ads
  #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



  #3  
Old March 26th, 2010, 06:39 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Database to track inventory in company cars

Ginge

You might want to read this article on updating quantity on hand by Allen
Browne.

http://allenbrowne.com/AppInventory.html

good luck, you have a big project in front of you.


Let me ask a couple of questions.


1. Are there different minimum levels per part per truck or does the same
part on every truck have the same minimum level?

2. How are you going to receive the amount used information from the drivers?



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.

Steve’s table design is fine. I do disagree on one point. If you have
the same reorder point for all parts on all cars, I would put the reorder
point on the TblMaterial table.

The TblMaterial table should have the following data:

Key – Part Number
Data – Part Description
Reorder Point


TblVanInventory table:
2 part key

Key - Vehicle Number
Part Number
Reorder point (if the reorder point is by part on each car)

TblVanInvTrans table:
Key - Automatic assigned sequent number
Data Transaction (Sold, On Order, Re-Stock)
Data Vehicle Number
Part Number
Transaction Date
Quantity Sold
Quantity Ordered
Quantity Re-stocked
Comment


When you write a sold transaction, the amount used / sold would be in the
Quantity Sold field.

When you write an order transaction, the amount ordered will be placed as a
positive number in the Quantity Ordered field.

When you write a re-stock transaction, the amount re-stocked will be placed
as a negative number in the Quantity Ordered field and as a positive number
in the Quantity Re-stocked field.

The advantage of this is you can run a query and total up the Quantity Sold,
Quantity On Order, and Quantity Re-stocked. The total quantity on order
should be equal to the amount that is currently on order for this part.


I’ve read Allen’s article and he has a point about storing the amount on
hand, amount sold, and amount on order. It is very complicated to make
sure you’ve covered all of the bases.

The advantage of using Allen’s approach is it makes development very easy.
However, if you have a large number of transactions and you are accessing the
data over the network, there is an time impact to re-compute the quantities
on hand, quantities on order, and quantities sold.

At what point that time impact become a problem, I don’t know that is
something that you would have to ask the MVPs.





Good luck,

Dennis

  #4  
Old March 30th, 2010, 01:08 AM posted to microsoft.public.access.tablesdbdesign
ginge
external usenet poster
 
Posts: 3
Default Database to track inventory in company cars

Dennis,
The same part in each van has the same minimum level I just need to be able
to track the items on a van by van basis so I can do orders for each van
individually.

The information is received by a stock card system. When items are used they
are marked on a card for that job and handed into the office. I want to be
able to enter this information almost like a transaction/sale so the amount
comes off the van and I can then generate a report/order.

I then also want to enter the incoming stock we receive from the suppliers
back into the van. (hopefully not one item at a time!)

Thank you for the link I will try to incorperate it into my design!

"Dennis" wrote:

Ginge

You might want to read this article on updating quantity on hand by Allen
Browne.

http://allenbrowne.com/AppInventory.html

good luck, you have a big project in front of you.


Let me ask a couple of questions.


1. Are there different minimum levels per part per truck or does the same
part on every truck have the same minimum level?

2. How are you going to receive the amount used information from the drivers?



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.

Steve’s table design is fine. I do disagree on one point. If you have
the same reorder point for all parts on all cars, I would put the reorder
point on the TblMaterial table.

The TblMaterial table should have the following data:

Key – Part Number
Data – Part Description
Reorder Point


TblVanInventory table:
2 part key

Key - Vehicle Number
Part Number
Reorder point (if the reorder point is by part on each car)

TblVanInvTrans table:
Key - Automatic assigned sequent number
Data Transaction (Sold, On Order, Re-Stock)
Data Vehicle Number
Part Number
Transaction Date
Quantity Sold
Quantity Ordered
Quantity Re-stocked
Comment


When you write a sold transaction, the amount used / sold would be in the
Quantity Sold field.

When you write an order transaction, the amount ordered will be placed as a
positive number in the Quantity Ordered field.

When you write a re-stock transaction, the amount re-stocked will be placed
as a negative number in the Quantity Ordered field and as a positive number
in the Quantity Re-stocked field.

The advantage of this is you can run a query and total up the Quantity Sold,
Quantity On Order, and Quantity Re-stocked. The total quantity on order
should be equal to the amount that is currently on order for this part.


I’ve read Allen’s article and he has a point about storing the amount on
hand, amount sold, and amount on order. It is very complicated to make
sure you’ve covered all of the bases.

The advantage of using Allen’s approach is it makes development very easy.
However, if you have a large number of transactions and you are accessing the
data over the network, there is an time impact to re-compute the quantities
on hand, quantities on order, and quantities sold.

At what point that time impact become a problem, I don’t know that is
something that you would have to ask the MVPs.





Good luck,

Dennis

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:11 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.