Help with relationships in Microsoft Access 2010

35 pts.
Tags:
Access Tables
Microsoft Access 2010
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.

Software/Hardware used:
Access 2010

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Is changing the database design an option?
    69,160 pointsBadges:
    report
  • AmWireLa
    Yes very much so. I started from scratch.
    35 pointsBadges:
    report
  • carlosdl
    I don't completely understand the difference between jobs and orders. Can more than one job be related to the same order?

    Also, in the orders table you have a 'cancel' field, but in the comments you mention that it refers to a 'job' being cancelled.  Can you explain that?

    You said the the cantenna, IMEI and router serial numbers are unique to each order.  What happens when you end up using equipment that was meant to another order/job? Those orders don't have equipment data because they will use equipment meant for use in another order?

    Is it possible for a user to have more than one order?

    Can, for example, a router and a cantenna that were originally sent together for one order, be used in separate orders?
    69,160 pointsBadges:
    report
  • carlosdl
    'Is it possible for a user to have more than one order?'

    I meant, 'Is it possible for a customer to have more than one order'?
    69,160 pointsBadges:
    report
  • AmWireLa
    I suppose there is not any difference in an order and a job. The order number is what I can use to look up to see what serial numbers are in that kit. I don't think I have the cancel in the right table however if a job is cancelled then that equipment goes to stock. Sometimes when we get a work order the order number when looked up just says use stock. So we use one of the kits that we have from someone cancelling. A customer can have more than one order but they would have a separate dispatch number for each order (as well as an order number). Yes it is possible for a cantenna and router sent together to be used on separate jobs. 
    A customer goes to the store and orders internet service. The store then enters it into the computer and based on location and availability we get assigned the job. Equipment with an order number is sent out to the location of the techs. I get a work order on the computer with a dispatch number (which is the order number less the letters at the beginning and the -1 at the end). My tech then takes the equipment for each job he has that day and does the job. After he does the job I can go in and look and see the serial numbers of the cantenna and router used on that job. Did I mention that there are actually 6 locations but only one that I am physically present? So, for the location where I am present I have been recording the order number of the equipment and the serial numbers inside the kit. When the job is done, I go in and check the serial numbers against the order number and then add the dispatch number to that order number. If they used the kit on a different job, I put that dispatch number with the order number, etc. If the job is cancelled, I add it to stock. So yes, some job data are missing because they cancelled.  So technically, my inventory doesn't include those kits that are assigned to a job until they are no longer assigned. Last month I got an email from the company that gives us these jobs with a list of 50 or more pieces of equipment that weren't accounted for. I am still attempting to locate these items. I more than anything want a way to search by serial number and see where that piece of equipment is (job, stock, location). So no two order numbers are the same, no two dispatch numbers are the same, nor cantenna serial numbers or router serial numbers are the same (we can leave out IMEI for simplicity). However, the order number and dispatch are technically the same. Ex: order# DIDSP999876545-1  dispatch# D999876545. I can search for equipment by serial number online in the system but all it does is bring up the original order and not where the equipment is now if it wasn't used for that job. 
    I hope this makes some sense. I thank you sincerely. Let me know if I can explain anything else.
    35 pointsBadges:
    report
  • carlosdl
    Here are some recommendations:

    -Add a CustomerID field to the customers table and remove the dispatch.
    -Add the customerId field to the orders table.
    -Remove the Cantenna, IMEI and router from the orders table, and create separate tables for each one (a table for routers, a table for cantennas, etc).  Each of these tables should have some identifier, like the serial number, an order number, a statusId (more on that later) and to simplify tracking, a LastJobId.
    -Assuming that someday an order could be related to more than one job (if an installation fails, for example, and another job has to be scheduled to try again) we should keep the jobs table. We will include the order number and will remove the zone from there, as it can be obtained from the related order.
    -You should have a LOCATIONS table with information about your locations, and a reference to the location in the jobs table.
    -We will need a Status table (a catalog) that will only have a StatusId and a description. It will include records for 1-In Stock, 2-Installed and if you think it could be useful, one for 3-In transit or something similar.

    So, to summarize, your tables would be something like this:

    CUSTOMERS:
    CustomerId,
    Name, etc.

    ORDERS:
    OrderId
    CustomerId
    Date, zone, etc. (NO routers, cantennas, etc.)

    ROUTERS:
    RouterSN
    OriginalOrderId (just for informational purposes.  It is the original order which this router was assigned to)
    StatusId
    LastJobId

    CANTENNAS:
    Similar to routers.

    JOBS:
    OrderId
    RouterSN, CantennaSN,
    LocationId
    Dates,employees,etc (NO zone)


    LOCATIONS:
    LocationId,
    Name, and any other pertinent data.

    So, to track where a router is you would go to the ROUTERS table and look it up by the serial number.  If it status is installed you can use the LastJobId to know what job it was used in. From the JOBS table you can relate it to an order and thus to a customer.  If it is ‘in transit’, for example, you can use the LastJobId to know where it is or who has it (employee or location).

    You can query the routers or cantennas tables to see how many or which specific devices are in stock (without having a separate table for stock).

    This is more or less how I would approach it.  Feel free to ask for the reasoning behind anything included here or for clarification.  It is possible that something doesn’t make much sense, which would mean that I misunderstood something.

    To reduce the possibility of errors, the necessary operations (changing equipment statuses, for example) should be done by an application (a form) and not manually.
    69,160 pointsBadges:
    report
  • AmWireLa

    Thank you! The more I thought about I was figuring I needed more tables with less info per table. It does make sense. Now, I should be able to make a form that would auto populate the info into the appropriate table, correct? One question, why remove the dispatch from the customer table. The dispatch is the unique identifier because no two are the same. Even if a past customer needs a repair, there will be a new dispatch number for the job. Wait, I think I just answered my own question. If I assign a customer number instead of the dispatch then the customer could be associated with more than one job (or dispatch number). Wouldn't I need to add a LastJobID to the jobs table so that it is associated with the routers and cantennas since routers and cantennas may be used from an orderID that was not intended for that job?

    35 pointsBadges:
    report
  • carlosdl
    Yes, you answered your own question.  That is exactly the reason for removing the dispatch from the customers table.

    As for the LastJobID, by mistake I didn't include a JobId in the jobs table.  It is definitely needed. That is the field you would use to relate the routers and cantennas to one specific job.
    69,160 pointsBadges:
    report
  • carlosdl
    And yes, you would need to create a ( or maybe more than one ) form.
    69,160 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following