Big question! If databases are new for you, the first and most important
step will be to get the right table structure.
Consider which of these aspects your database needs to handle:
- parts (Part table)
- suppliers (Supplier table)
- purchase orders to suppliers (PO table)
- the details of parts in an order (PODetail table)
- deliveries received from suppliers (Acquisition table)
- the details of parts in a delivery (AcqDetail table)
- vehicles/machines (Machine table)
- services conducted on machines (Service table)
- parts used in a service (ServiceDetail table)
With those 9 tables in place, you can now calculate your stock level as the
sum of parts received (AcqDetail table) less the sum of parts used
(ServiceDetail table.)
In practice, parts get mislaid or broken, so you probably do periodic
stocktakes as well, so the calculation has a known starting value at a known
date (the stocktake date), and the calculations are conducted on
transactions since that date.
For more details of how this would work, see:
Inventory Control: Quantity on Hand
at:
http://allenbrowne.com/AppInventory.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<stevenichols@orange.netwrote in message
news:1174807438.686314.7170@l75g2000hse.googlegrou ps.com...
Quote:
Hi first time here so please bear withme,
i am trying to create database of parts and components on stock.it has
usual things like part number, location, description,qty,re-order
level etc. I have got the input side of it working so i can add parts
to database.
i now need to be able to book these parts to a specific machine or
vehicle as required, and then to be able to print a report off with
parts used and keep a system copy for future referance.
>
i am new to all things access and need help please
>
thanks
>
steve44
>
PS Access 2007 but have 2003 if prefered
>
|
