I am office manager in a service company that installs internet. A customer orders internet from the store, the store places the order and equipment is sent out for that job. There is a cantenna serial number, and IMEI and a router serial number that is unique to each order. There is also an order number. We get a dispatch number for the job. If we could use the equipment on the jobs for which they was intended I wouldn't have an issue. But sometimes we have to use equipment on a job meant for another. I want to be able to enter a cantenna or router serial number and see where it is (stock, job, etc.). To make things more complicated I have 5 locations and only one that I am physically present. I would also like to enter a zone to see a list of orders, jobs or customers; enter a date to find jobs completed; enter an order number to find the received date and zone, enter an order number to auto populate the cantenna, imei, and router and zone in the job record; enter employee id to list all jobs for that id; among other tasks. I have
Customers Table, Orders Table, Jobs Table, Employee Table, Stock Table.
The Customers Table has fields of Dispatch, last name, first name, address, city, state, zip, phone, email, zone.
The Orders Table has fields of OrderID, Cantenna, IMEI, Router, ship date, rec date, zone and a cancel yes/no (if a job is cancelled then the equipment goes into stock).
The Jobs Table has Dispatch, Cantenna, IMEI, Router, serv date, zone, Employee id, and a transaction number (needed for returns).
The Employee Table has ID, last name, first name, address, city, state, zip, ICE.
The Stock Table has OrderID, Cantenna, IMEI,
Router, Dispatch Used, Date Used.
I am confused on how to relate these tables. I find myself entering the same data on several tables. The main purpose is to track the equipment because every month I have to turn in inventory to the company that gives us these jobs. Last month I received a list of missing equipment and it has been a nightmare trying to find the missing equipment because there is no tracking in place. Help please. Also the order number is the dispatch number with 5 added letters in the beginning and the transaction number is the order number less 3 letters.