Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Machine Activty Log Table design issues

    I have a number of checklists for my machinery

    i have attached a couple of check sheets I have attached my table structure and an example of my check list in XLS format

    i need to track which of these checks are being carried out each week..

    I have created 3 tables so far, 1 called tLine and 1 called tMachine and 1 called tActivity

    tLine
    LineID (PK) - Autonumber
    LineDescription - Text

    Not sure if my use of PK is right, should it be the MachineDescription that i make the Key?

    tMachine
    MachineID (PK) - Autonumber
    MachineDescription - Text

    Not sure if my use of PK is right, should it be the MachineDescription that i make the Key?

    tActivity

    ActivityID - (PK) - Autonumber
    Activity - Text
    StdTime - Number

    Not sure if my use of PK is right, should it be the MachineDescription that i make the Key?

    -----

    I am having a few issues i could really do with some help with please

    Line 1 has a Filler and Capper
    Line 2 has a Filler
    Line 3 Has a Filler and a Capper

    if i focus on the filler:

    I have 5 checks to do on this however the Stdtime on activity 5 is different on Line 1 and Line 3 but the activity is the same.

    if i focus on the capper:
    this has 2 differnet activities and 1 activity which has the same description as the filler activity.

    Problem 1:
    Table Setup - not sure i am using the PK correctly etc, have read a few reference guides etc, but am struggling with this..

    Problem 2:
    how do i link the activitys to the machines and the machines to the Lines

    Problem3:
    how do i account for the same activity but different std times.

    Problem 4:
    I need to track these checks but i will try to get the table structure right first

    I really would appreciate your help here please
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Ok, im kinda short on time so i can answer problem 1 and 2 real quick before I head home for the day and hopefully someone else can answer the rest.

    Problem 1: PKs pretty much always should be a number field at the very least, even better, an autonumber field so I think that from what you have listed you have used the correct fields as the PKs.

    Problem 2: You link the activity to the machine by adding a field to the table tActivity that is called machine which looks up from table tMachine. You then link the machine to the line in a similar way adding a field to table tMachine called Line which looks up from table tLine

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Chimp8471
    I have a number of checklists for my machinery

    i have attached a couple of check sheets I have attached my table structure and an example of my check list in XLS format

    i need to track which of these checks are being carried out each week..

    I have created 3 tables so far, 1 called tLine and 1 called tMachine and 1 called tActivity

    tLine
    LineID (PK) - Autonumber
    LineDescription - Text

    Not sure if my use of PK is right, should it be the MachineDescription that i make the Key?

    tMachine
    MachineID (PK) - Autonumber
    MachineDescription - Text

    Not sure if my use of PK is right, should it be the MachineDescription that i make the Key?

    tActivity

    ActivityID - (PK) - Autonumber
    Activity - Text
    StdTime - Number

    Not sure if my use of PK is right, should it be the MachineDescription that i make the Key?

    -----

    I am having a few issues i could really do with some help with please

    Line 1 has a Filler and Capper
    Line 2 has a Filler
    Line 3 Has a Filler and a Capper

    if i focus on the filler:

    I have 5 checks to do on this however the Stdtime on activity 5 is different on Line 1 and Line 3 but the activity is the same.

    if i focus on the capper:
    this has 2 differnet activities and 1 activity which has the same description as the filler activity.

    Problem 1:
    Table Setup - not sure i am using the PK correctly etc, have read a few reference guides etc, but am struggling with this..

    Problem 2:
    how do i link the activitys to the machines and the machines to the Lines

    Problem3:
    how do i account for the same activity but different std times.

    Problem 4:
    I need to track these checks but i will try to get the table structure right first

    I really would appreciate your help here please
    Forgive me for not looking at your db first; I didn't for fear of getting confused. I have about 15 years experience in drawings, parts lists, wire lists, etc., so I might be able to give you some basic pointers.

    The way you presented your situation, you have various lines; each line has various machines; each machine has various activities; each activity has a standard length of time associated with it.

    If the above is true, you need four tables.

    You need tblLines, with a PK for each line. Each line will include a machine list, which will contain (an) entr(y)ies in a table called tblMachineList (more about this later). All machines, of course, will appear in tblMachines, which will have a PK for each machine. Activities, with an activity PK, will appear in tblActivities. So far, this is all "black box" (no detail).

    Let's start with the machine level. You will need

    MachineID (PK), Autonumber
    MachineDesc, Text

    You can also add fields for model number, serial number, ad amnauseum.
    Activities do not need to be mentioned here.

    Now tblActivities. You will need

    MachineID, Number. this is the Foreign Key which is the previously-mentioned PK above
    ActivitySeq, Number (I assume they must be done in order)
    ActivityDesc, Text
    ActivityTime, Number

    I don't think you need an ActivityID as a PK, as sequencing will be by machine and activityseq, not by activity per se. Likewise, reporting will only use those data, so a PK isn't useful.

    tblLines. You will need

    LineID (PK) - Autonumber
    LineDescription - Text

    I just copied your text above.

    tblMachineList. You will need

    LineID, Number. This is the foreign key from tblLines
    MachineID, Number. This is the foreign key from tblMachines.

    By the way, a "foreign key" is simply any field that was used in a related table as that table's primary key.

    Now you want to make some relations. tblLines-LineID to tblMachineList-LineID will be a one-to-many relation. tblMachines-MachineID to tblMachineList-MachineID, likewise, will be one-to-many. tblMachine-MachineID to tblActivities-MachineID will also be one-to-many.

    This is a basic structure for the info as you presented it; I hope it helps,

    Sam

  4. #4
    Join Date
    Mar 2003
    Posts
    225
    many thanks for your help, i am just about to toy around with this and just wanted to check something before i do...

    you said:

    The way you presented your situation, you have various lines; each line has various machines; each machine has various activities; each activity has a standard length of time associated with it.
    this is all true except the part

    each activity has a standard length of time associated with it

    an activity on one line maybe as follows:

    Line 1 -
    filler
    Activity 1 = Clean Machine Std Time 5 mins

    Line 2
    filler
    Activity 10 = Clean Machine Std Time 9 mins

    as you can see they are differnet activity numbers, same description, and different std times...

    this could be the same principle for 30 othe similar situations

    please advise

    Andy

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Chimp8471
    many thanks for your help, i am just about to toy around with this and just wanted to check something before i do...

    you said:



    this is all true except the part

    each activity has a standard length of time associated with it

    an activity on one line maybe as follows:

    Line 1 -
    filler
    Activity 1 = Clean Machine Std Time 5 mins

    Line 2
    filler
    Activity 10 = Clean Machine Std Time 9 mins

    as you can see they are differnet activity numbers, same description, and different std times...

    this could be the same principle for 30 othe similar situations

    please advise

    Andy
    Hi, Andy,

    Maybe I could have been a bit more clear in the line you quoted. I should have said
    Code:
    each MachineID/ActivitySeq/ActivityDesc has a standard length of time associated with it
    What this means is that you can have an ActivityDesc described as "Cleaning", for example, for each machine, but at different points in the sequence they will have different standard times, because different things might be cleaned at various points. Maybe a little more detail in the ActivityDesc is called for, such as "Cleaning the assembled widget", or "Cleaning the overall machine", etc.

    This can get infinitely more detailed, such as station designations, technical grades, etc., but if you don't need it, there's no reason to go there.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •