Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Are Orphaned Tables OK

Discussion in 'Information Technology' started by ED007, Jul 28, 2006.

  1. ED007

    ED007
    Expand Collapse
    Guest

    I have a table set up this way.

    *LOTS*
    LOT - Text Field and Primary key
    GRADE - Text Field
    ....
    ....
    ....

    Our LOT identifiers are unique and are a concatination of the day of the
    year, 1-365), two digit year, single letter signifiying lot order A, B, C
    used for production, two digit code to signify operator, single digit to
    signify shift, and single character to signify machine.

    Originaly I have the table set up as
    *LOTS*
    LOT - Text Field (only day, year part, and order) and Primary key
    OPER - Text Field
    SHIFT - Text Field
    MACHINE - Text Field
    GRADE - Text Field
    ....
    ....
    ....


    I also had tables for OPER, SHIFT and Machine

    *OPER ID*
    OPER - Text Field and primary Key
    First Name - Text
    ....
    ....

    *SHIFT*
    SHIFT - Text Field and primary Key
    Start time - date/time
    end time - date/time
    ....
    ....

    *MACHINE*
    MACH - Text Field primary key
    MACHINE DETAILS - Memo
    ....
    ....


    These tables were linked to limit the data that could be entered on the main
    table to real operators, shifts and machines.
    This worked great UNTIL I realized that the LOT could be repeated if the
    same operator produced material on more that one machine at the same time.
    This is very rare but does happen.

    Therfore I concated all the fileds together into the new LOT. To validate
    the LOT data entry some code is used to pull the LOT apart and compare each
    part with the corisponding table of allowable entries.

    When I run reports or querries that use the information in these orphaned
    tables I also must write code to pull apart the LOT and get the correct part.

    My question is was their a better way to solve my original issue? I thought
    about setting a new primary key in the original table that was an auto
    number. HOWEVER, the users af the data base were already grumbling about
    having to enter each part ofhte LOT seperatly. So combining it made some
    sense make them happy. But I fear I may have set myself up for bigger issues
    down the road.

    Any other ideas as to what i should have done?
     
  2. Loading...


  3. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    Your original idea was correct. You should have an autonumber primary key
    for that table. In addition, you should not store the concatenated value
    for LOT. Instead, you should recreate it on the fly in queries, reports,
    and forms as needed. Then create a Unique Index on the Date, OPER, SHIFT,
    and MACHINE. (You ARE capturing the Date, aren't you?)

    I don't know what to tell you about the grumbling. If the operators are
    themselves entering data, perhaps you could have a login screen which would
    accept OPER, SHIFT, and MACHINE and set them as default values.


    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com
    Want answers to your Access questions in your Email?
    Free subscription:
    http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




    "ED007" <ED007@discussions.microsoft.com> wrote in message
    news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > I have a table set up this way.
    >
    > *LOTS*
    > LOT - Text Field and Primary key
    > GRADE - Text Field
    > ...
    > ...
    > ...
    >
    > Our LOT identifiers are unique and are a concatination of the day of the
    > year, 1-365), two digit year, single letter signifiying lot order A, B, C
    > used for production, two digit code to signify operator, single digit to
    > signify shift, and single character to signify machine.
    >
    > Originaly I have the table set up as
    > *LOTS*
    > LOT - Text Field (only day, year part, and order) and Primary key
    > OPER - Text Field
    > SHIFT - Text Field
    > MACHINE - Text Field
    > GRADE - Text Field
    > ...
    > ...
    > ...
    >
    >
    > I also had tables for OPER, SHIFT and Machine
    >
    > *OPER ID*
    > OPER - Text Field and primary Key
    > First Name - Text
    > ...
    > ...
    >
    > *SHIFT*
    > SHIFT - Text Field and primary Key
    > Start time - date/time
    > end time - date/time
    > ...
    > ...
    >
    > *MACHINE*
    > MACH - Text Field primary key
    > MACHINE DETAILS - Memo
    > ...
    > ...
    >
    >
    > These tables were linked to limit the data that could be entered on the

    main
    > table to real operators, shifts and machines.
    > This worked great UNTIL I realized that the LOT could be repeated if the
    > same operator produced material on more that one machine at the same time.
    > This is very rare but does happen.
    >
    > Therfore I concated all the fileds together into the new LOT. To validate
    > the LOT data entry some code is used to pull the LOT apart and compare

    each
    > part with the corisponding table of allowable entries.
    >
    > When I run reports or querries that use the information in these orphaned
    > tables I also must write code to pull apart the LOT and get the correct

    part.
    >
    > My question is was their a better way to solve my original issue? I

    thought
    > about setting a new primary key in the original table that was an auto
    > number. HOWEVER, the users af the data base were already grumbling about
    > having to enter each part ofhte LOT seperatly. So combining it made some
    > sense make them happy. But I fear I may have set myself up for bigger

    issues
    > down the road.
    >
    > Any other ideas as to what i should have done?
    >
     
  4. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    Regardless of the complaints, storing the lot as a mushed field violates the
    first three normal forms and is not recommended. A string of cascading
    combos can be used to minimize typing or if you wish, populate the RowSource
    of the combo with a concatenated field that mushes all the fields together.
    This works because you have a table that defines valid lots. This combo
    will make the users more comfortable. They think they are working with
    their familiar lot number but the real data is properly stored.

    "This worked great UNTIL I realized that the LOT could be repeated if the
    same operator produced material on more that one machine at the same time.
    This is very rare but does happen."
    Earlier you said that machine is part of the lot identifier so how could
    this cause a duplicate?

    I would use an autonumber as the primary key because pk's with this many
    pieces tend to be awkward to work with. The user never needs to see the
    autonumber. You will only use it for joining to other tables. You can then
    define a unique index that includes all the fields that are required to
    uniquely identify a lot. Access/Jet supports up to 10 fields in a
    multi-field primary key or index.

    "ED007" <ED007@discussions.microsoft.com> wrote in message
    news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    >I have a table set up this way.
    >
    > *LOTS*
    > LOT - Text Field and Primary key
    > GRADE - Text Field
    > ...
    > ...
    > ...
    >
    > Our LOT identifiers are unique and are a concatination of the day of the
    > year, 1-365), two digit year, single letter signifiying lot order A, B, C
    > used for production, two digit code to signify operator, single digit to
    > signify shift, and single character to signify machine.
    >
    > Originaly I have the table set up as
    > *LOTS*
    > LOT - Text Field (only day, year part, and order) and Primary key
    > OPER - Text Field
    > SHIFT - Text Field
    > MACHINE - Text Field
    > GRADE - Text Field
    > ...
    > ...
    > ...
    >
    >
    > I also had tables for OPER, SHIFT and Machine
    >
    > *OPER ID*
    > OPER - Text Field and primary Key
    > First Name - Text
    > ...
    > ...
    >
    > *SHIFT*
    > SHIFT - Text Field and primary Key
    > Start time - date/time
    > end time - date/time
    > ...
    > ...
    >
    > *MACHINE*
    > MACH - Text Field primary key
    > MACHINE DETAILS - Memo
    > ...
    > ...
    >
    >
    > These tables were linked to limit the data that could be entered on the
    > main
    > table to real operators, shifts and machines.
    > This worked great UNTIL I realized that the LOT could be repeated if the
    > same operator produced material on more that one machine at the same time.
    > This is very rare but does happen.
    >
    > Therfore I concated all the fileds together into the new LOT. To validate
    > the LOT data entry some code is used to pull the LOT apart and compare
    > each
    > part with the corisponding table of allowable entries.
    >
    > When I run reports or querries that use the information in these orphaned
    > tables I also must write code to pull apart the LOT and get the correct
    > part.
    >
    > My question is was their a better way to solve my original issue? I
    > thought
    > about setting a new primary key in the original table that was an auto
    > number. HOWEVER, the users af the data base were already grumbling about
    > having to enter each part ofhte LOT seperatly. So combining it made some
    > sense make them happy. But I fear I may have set myself up for bigger
    > issues
    > down the road.
    >
    > Any other ideas as to what i should have done?
    >
     
  5. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Roger:

    Any particular reason why an autonumber primary key is so important. There
    is a unique combination of Date, OPER, SHIFT and MACHINE. Let that be the
    PK.

    I have built multiple databases without autonumber type identities. They
    are almost always unnecessary. In a database the scale which is possible
    for Access Jet, this has never been a problem.

    Tom Elliosn


    "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > Your original idea was correct. You should have an autonumber primary key
    > for that table. In addition, you should not store the concatenated value
    > for LOT. Instead, you should recreate it on the fly in queries, reports,
    > and forms as needed. Then create a Unique Index on the Date, OPER, SHIFT,
    > and MACHINE. (You ARE capturing the Date, aren't you?)
    >
    > I don't know what to tell you about the grumbling. If the operators are
    > themselves entering data, perhaps you could have a login screen which
    > would
    > accept OPER, SHIFT, and MACHINE and set them as default values.
    >
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    >
    >
    > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    >> I have a table set up this way.
    >>
    >> *LOTS*
    >> LOT - Text Field and Primary key
    >> GRADE - Text Field
    >> ...
    >> ...
    >> ...
    >>
    >> Our LOT identifiers are unique and are a concatination of the day of the
    >> year, 1-365), two digit year, single letter signifiying lot order A, B, C
    >> used for production, two digit code to signify operator, single digit to
    >> signify shift, and single character to signify machine.
    >>
    >> Originaly I have the table set up as
    >> *LOTS*
    >> LOT - Text Field (only day, year part, and order) and Primary key
    >> OPER - Text Field
    >> SHIFT - Text Field
    >> MACHINE - Text Field
    >> GRADE - Text Field
    >> ...
    >> ...
    >> ...
    >>
    >>
    >> I also had tables for OPER, SHIFT and Machine
    >>
    >> *OPER ID*
    >> OPER - Text Field and primary Key
    >> First Name - Text
    >> ...
    >> ...
    >>
    >> *SHIFT*
    >> SHIFT - Text Field and primary Key
    >> Start time - date/time
    >> end time - date/time
    >> ...
    >> ...
    >>
    >> *MACHINE*
    >> MACH - Text Field primary key
    >> MACHINE DETAILS - Memo
    >> ...
    >> ...
    >>
    >>
    >> These tables were linked to limit the data that could be entered on the

    > main
    >> table to real operators, shifts and machines.
    >> This worked great UNTIL I realized that the LOT could be repeated if the
    >> same operator produced material on more that one machine at the same
    >> time.
    >> This is very rare but does happen.
    >>
    >> Therfore I concated all the fileds together into the new LOT. To
    >> validate
    >> the LOT data entry some code is used to pull the LOT apart and compare

    > each
    >> part with the corisponding table of allowable entries.
    >>
    >> When I run reports or querries that use the information in these orphaned
    >> tables I also must write code to pull apart the LOT and get the correct

    > part.
    >>
    >> My question is was their a better way to solve my original issue? I

    > thought
    >> about setting a new primary key in the original table that was an auto
    >> number. HOWEVER, the users af the data base were already grumbling about
    >> having to enter each part ofhte LOT seperatly. So combining it made some
    >> sense make them happy. But I fear I may have set myself up for bigger

    > issues
    >> down the road.
    >>
    >> Any other ideas as to what i should have done?
    >>

    >
    >
     
  6. ED007

    ED007
    Expand Collapse
    Guest

    Unfortunatly the operators are not entering the information. So a log on
    screen would not be ideal. I am not capturing the date perse but I do have
    it in the first 5 digits ofhte lot. I use the DateSerial function in
    querries and or reports were I want to report the production date for a lot.

    What I am thinking now is that I should have added the autonumber primary
    key. And to give the daa entry people a break had an unbound field in the
    data entry from to accept the "full" lot id and code in the on update or on
    exit section to yank the text apart and put the appropriate part in the
    relavent field. This would have preserved the table relationships, and made
    writting subsequent reports and querries easier. However, I have everything
    working now so I will leave it alone. Just trying to learn from my mistakes
    for the next time.



    "Roger Carlson" wrote:

    > Your original idea was correct. You should have an autonumber primary key
    > for that table. In addition, you should not store the concatenated value
    > for LOT. Instead, you should recreate it on the fly in queries, reports,
    > and forms as needed. Then create a Unique Index on the Date, OPER, SHIFT,
    > and MACHINE. (You ARE capturing the Date, aren't you?)
    >
    > I don't know what to tell you about the grumbling. If the operators are
    > themselves entering data, perhaps you could have a login screen which would
    > accept OPER, SHIFT, and MACHINE and set them as default values.
    >
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    >
    >
    > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > > I have a table set up this way.
    > >
    > > *LOTS*
    > > LOT - Text Field and Primary key
    > > GRADE - Text Field
    > > ...
    > > ...
    > > ...
    > >
    > > Our LOT identifiers are unique and are a concatination of the day of the
    > > year, 1-365), two digit year, single letter signifiying lot order A, B, C
    > > used for production, two digit code to signify operator, single digit to
    > > signify shift, and single character to signify machine.
    > >
    > > Originaly I have the table set up as
    > > *LOTS*
    > > LOT - Text Field (only day, year part, and order) and Primary key
    > > OPER - Text Field
    > > SHIFT - Text Field
    > > MACHINE - Text Field
    > > GRADE - Text Field
    > > ...
    > > ...
    > > ...
    > >
    > >
    > > I also had tables for OPER, SHIFT and Machine
    > >
    > > *OPER ID*
    > > OPER - Text Field and primary Key
    > > First Name - Text
    > > ...
    > > ...
    > >
    > > *SHIFT*
    > > SHIFT - Text Field and primary Key
    > > Start time - date/time
    > > end time - date/time
    > > ...
    > > ...
    > >
    > > *MACHINE*
    > > MACH - Text Field primary key
    > > MACHINE DETAILS - Memo
    > > ...
    > > ...
    > >
    > >
    > > These tables were linked to limit the data that could be entered on the

    > main
    > > table to real operators, shifts and machines.
    > > This worked great UNTIL I realized that the LOT could be repeated if the
    > > same operator produced material on more that one machine at the same time.
    > > This is very rare but does happen.
    > >
    > > Therfore I concated all the fileds together into the new LOT. To validate
    > > the LOT data entry some code is used to pull the LOT apart and compare

    > each
    > > part with the corisponding table of allowable entries.
    > >
    > > When I run reports or querries that use the information in these orphaned
    > > tables I also must write code to pull apart the LOT and get the correct

    > part.
    > >
    > > My question is was their a better way to solve my original issue? I

    > thought
    > > about setting a new primary key in the original table that was an auto
    > > number. HOWEVER, the users af the data base were already grumbling about
    > > having to enter each part ofhte LOT seperatly. So combining it made some
    > > sense make them happy. But I fear I may have set myself up for bigger

    > issues
    > > down the road.
    > >
    > > Any other ideas as to what i should have done?
    > >

    >
    >
    >
     
  7. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    In this case, it may well be unnecessary.

    The only time it would be necessary (to my mind) is if the table were
    someday linked with to other table at some time. My experience is that no
    matter what people say at the time, things change. To be stuck with a
    multi-field primary key and have to join on multiple fields is, I believe,
    well worth the time to set up an autonumber primary key ahead of time. If
    it's never used, it's not really hurting anything. If it is needed in the
    future, you'll already have it.

    Natural vs Surrogate keys is a religious issue in some circles, and I won't
    get into that here. I'll just say that nearly EVERY table I create (aside
    from "linking" tables) has an autonumber primary key. I think most of the
    MVPs would agree. If you don't want to, I'm fine with that.

    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com
    Want answers to your Access questions in your Email?
    Free subscription:
    http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > Dear Roger:
    >
    > Any particular reason why an autonumber primary key is so important.

    There
    > is a unique combination of Date, OPER, SHIFT and MACHINE. Let that be the
    > PK.
    >
    > I have built multiple databases without autonumber type identities. They
    > are almost always unnecessary. In a database the scale which is possible
    > for Access Jet, this has never been a problem.
    >
    > Tom Elliosn
    >
    >
    > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > > Your original idea was correct. You should have an autonumber primary

    key
    > > for that table. In addition, you should not store the concatenated

    value
    > > for LOT. Instead, you should recreate it on the fly in queries,

    reports,
    > > and forms as needed. Then create a Unique Index on the Date, OPER,

    SHIFT,
    > > and MACHINE. (You ARE capturing the Date, aren't you?)
    > >
    > > I don't know what to tell you about the grumbling. If the operators are
    > > themselves entering data, perhaps you could have a login screen which
    > > would
    > > accept OPER, SHIFT, and MACHINE and set them as default values.
    > >
    > >
    > > --
    > > --Roger Carlson
    > > MS Access MVP
    > > Access Database Samples: www.rogersaccesslibrary.com
    > > Want answers to your Access questions in your Email?
    > > Free subscription:
    > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >
    > >
    > >
    > >
    > > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > >> I have a table set up this way.
    > >>
    > >> *LOTS*
    > >> LOT - Text Field and Primary key
    > >> GRADE - Text Field
    > >> ...
    > >> ...
    > >> ...
    > >>
    > >> Our LOT identifiers are unique and are a concatination of the day of

    the
    > >> year, 1-365), two digit year, single letter signifiying lot order A, B,

    C
    > >> used for production, two digit code to signify operator, single digit

    to
    > >> signify shift, and single character to signify machine.
    > >>
    > >> Originaly I have the table set up as
    > >> *LOTS*
    > >> LOT - Text Field (only day, year part, and order) and Primary key
    > >> OPER - Text Field
    > >> SHIFT - Text Field
    > >> MACHINE - Text Field
    > >> GRADE - Text Field
    > >> ...
    > >> ...
    > >> ...
    > >>
    > >>
    > >> I also had tables for OPER, SHIFT and Machine
    > >>
    > >> *OPER ID*
    > >> OPER - Text Field and primary Key
    > >> First Name - Text
    > >> ...
    > >> ...
    > >>
    > >> *SHIFT*
    > >> SHIFT - Text Field and primary Key
    > >> Start time - date/time
    > >> end time - date/time
    > >> ...
    > >> ...
    > >>
    > >> *MACHINE*
    > >> MACH - Text Field primary key
    > >> MACHINE DETAILS - Memo
    > >> ...
    > >> ...
    > >>
    > >>
    > >> These tables were linked to limit the data that could be entered on the

    > > main
    > >> table to real operators, shifts and machines.
    > >> This worked great UNTIL I realized that the LOT could be repeated if

    the
    > >> same operator produced material on more that one machine at the same
    > >> time.
    > >> This is very rare but does happen.
    > >>
    > >> Therfore I concated all the fileds together into the new LOT. To
    > >> validate
    > >> the LOT data entry some code is used to pull the LOT apart and compare

    > > each
    > >> part with the corisponding table of allowable entries.
    > >>
    > >> When I run reports or querries that use the information in these

    orphaned
    > >> tables I also must write code to pull apart the LOT and get the correct

    > > part.
    > >>
    > >> My question is was their a better way to solve my original issue? I

    > > thought
    > >> about setting a new primary key in the original table that was an auto
    > >> number. HOWEVER, the users af the data base were already grumbling

    about
    > >> having to enter each part ofhte LOT seperatly. So combining it made

    some
    > >> sense make them happy. But I fear I may have set myself up for bigger

    > > issues
    > >> down the road.
    > >>
    > >> Any other ideas as to what i should have done?
    > >>

    > >
    > >

    >
    >
     
  8. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    I would have them enter them into separate controls and store the values in
    separate field and put them together when you need them. Ripping it apart
    is far harder.

    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com
    Want answers to your Access questions in your Email?
    Free subscription:
    http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


    "ED007" <ED007@discussions.microsoft.com> wrote in message
    news:2EA668BE-48C4-4774-AEA7-87F1095143FF@microsoft.com...
    > Unfortunatly the operators are not entering the information. So a log on
    > screen would not be ideal. I am not capturing the date perse but I do

    have
    > it in the first 5 digits ofhte lot. I use the DateSerial function in
    > querries and or reports were I want to report the production date for a

    lot.
    >
    > What I am thinking now is that I should have added the autonumber primary
    > key. And to give the daa entry people a break had an unbound field in the
    > data entry from to accept the "full" lot id and code in the on update or

    on
    > exit section to yank the text apart and put the appropriate part in the
    > relavent field. This would have preserved the table relationships, and

    made
    > writting subsequent reports and querries easier. However, I have

    everything
    > working now so I will leave it alone. Just trying to learn from my

    mistakes
    > for the next time.
    >
    >
    >
    > "Roger Carlson" wrote:
    >
    > > Your original idea was correct. You should have an autonumber primary

    key
    > > for that table. In addition, you should not store the concatenated

    value
    > > for LOT. Instead, you should recreate it on the fly in queries,

    reports,
    > > and forms as needed. Then create a Unique Index on the Date, OPER,

    SHIFT,
    > > and MACHINE. (You ARE capturing the Date, aren't you?)
    > >
    > > I don't know what to tell you about the grumbling. If the operators are
    > > themselves entering data, perhaps you could have a login screen which

    would
    > > accept OPER, SHIFT, and MACHINE and set them as default values.
    > >
    > >
    > > --
    > > --Roger Carlson
    > > MS Access MVP
    > > Access Database Samples: www.rogersaccesslibrary.com
    > > Want answers to your Access questions in your Email?
    > > Free subscription:
    > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >
    > >
    > >
    > >
    > > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > > > I have a table set up this way.
    > > >
    > > > *LOTS*
    > > > LOT - Text Field and Primary key
    > > > GRADE - Text Field
    > > > ...
    > > > ...
    > > > ...
    > > >
    > > > Our LOT identifiers are unique and are a concatination of the day of

    the
    > > > year, 1-365), two digit year, single letter signifiying lot order A,

    B, C
    > > > used for production, two digit code to signify operator, single digit

    to
    > > > signify shift, and single character to signify machine.
    > > >
    > > > Originaly I have the table set up as
    > > > *LOTS*
    > > > LOT - Text Field (only day, year part, and order) and Primary key
    > > > OPER - Text Field
    > > > SHIFT - Text Field
    > > > MACHINE - Text Field
    > > > GRADE - Text Field
    > > > ...
    > > > ...
    > > > ...
    > > >
    > > >
    > > > I also had tables for OPER, SHIFT and Machine
    > > >
    > > > *OPER ID*
    > > > OPER - Text Field and primary Key
    > > > First Name - Text
    > > > ...
    > > > ...
    > > >
    > > > *SHIFT*
    > > > SHIFT - Text Field and primary Key
    > > > Start time - date/time
    > > > end time - date/time
    > > > ...
    > > > ...
    > > >
    > > > *MACHINE*
    > > > MACH - Text Field primary key
    > > > MACHINE DETAILS - Memo
    > > > ...
    > > > ...
    > > >
    > > >
    > > > These tables were linked to limit the data that could be entered on

    the
    > > main
    > > > table to real operators, shifts and machines.
    > > > This worked great UNTIL I realized that the LOT could be repeated if

    the
    > > > same operator produced material on more that one machine at the same

    time.
    > > > This is very rare but does happen.
    > > >
    > > > Therfore I concated all the fileds together into the new LOT. To

    validate
    > > > the LOT data entry some code is used to pull the LOT apart and compare

    > > each
    > > > part with the corisponding table of allowable entries.
    > > >
    > > > When I run reports or querries that use the information in these

    orphaned
    > > > tables I also must write code to pull apart the LOT and get the

    correct
    > > part.
    > > >
    > > > My question is was their a better way to solve my original issue? I

    > > thought
    > > > about setting a new primary key in the original table that was an auto
    > > > number. HOWEVER, the users af the data base were already grumbling

    about
    > > > having to enter each part ofhte LOT seperatly. So combining it made

    some
    > > > sense make them happy. But I fear I may have set myself up for bigger

    > > issues
    > > > down the road.
    > > >
    > > > Any other ideas as to what i should have done?
    > > >

    > >
    > >
    > >
     
  9. ED007

    ED007
    Expand Collapse
    Guest

    Earlier you said that machine is part of the lot identifier so how could
    > this cause a duplicate?


    In the original manefestation LOT was only the first 6 characters of the
    mashed up LOT number. 3 digits for the day of the year, 2 digits for the
    year and 1 to indicate if it was batch A, B, C etc for that day on that
    machine. If I had left the table this way it is possible that on day 001 of
    year 06, batch A is produced (LOT ID is 00106A) and on the same day the
    second machine is also used (LOT ID is also 00106A) In this case the two LOT
    numbers are not distinct even if differnt machines were used to produce them.
    This is a result of our buisness practice however when I mash the fields
    together these two lots become 00106A...A on the first machine and 00106A...B
    on the second machine. This is why I needed to mash the field together in
    retropect however adding an autonumber filed as the primary key. TO prevent
    entering duplicate date in all 4 fields, shich would be an error I would have
    needed to write some verification code. BUT at least it would have only been
    done once. Instead the way I solved the problem created DLookup statments
    every time I wanted use the OPERATOR/SHIFT/MACHINE data tables. Talk about a
    pain.

    "Pat Hartman(MVP)" wrote:

    > Regardless of the complaints, storing the lot as a mushed field violates the
    > first three normal forms and is not recommended. A string of cascading
    > combos can be used to minimize typing or if you wish, populate the RowSource
    > of the combo with a concatenated field that mushes all the fields together.
    > This works because you have a table that defines valid lots. This combo
    > will make the users more comfortable. They think they are working with
    > their familiar lot number but the real data is properly stored.
    >
    > "This worked great UNTIL I realized that the LOT could be repeated if the
    > same operator produced material on more that one machine at the same time.
    > This is very rare but does happen."
    > Earlier you said that machine is part of the lot identifier so how could
    > this cause a duplicate?
    >
    > I would use an autonumber as the primary key because pk's with this many
    > pieces tend to be awkward to work with. The user never needs to see the
    > autonumber. You will only use it for joining to other tables. You can then
    > define a unique index that includes all the fields that are required to
    > uniquely identify a lot. Access/Jet supports up to 10 fields in a
    > multi-field primary key or index.
    >
    > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > >I have a table set up this way.
    > >
    > > *LOTS*
    > > LOT - Text Field and Primary key
    > > GRADE - Text Field
    > > ...
    > > ...
    > > ...
    > >
    > > Our LOT identifiers are unique and are a concatination of the day of the
    > > year, 1-365), two digit year, single letter signifiying lot order A, B, C
    > > used for production, two digit code to signify operator, single digit to
    > > signify shift, and single character to signify machine.
    > >
    > > Originaly I have the table set up as
    > > *LOTS*
    > > LOT - Text Field (only day, year part, and order) and Primary key
    > > OPER - Text Field
    > > SHIFT - Text Field
    > > MACHINE - Text Field
    > > GRADE - Text Field
    > > ...
    > > ...
    > > ...
    > >
    > >
    > > I also had tables for OPER, SHIFT and Machine
    > >
    > > *OPER ID*
    > > OPER - Text Field and primary Key
    > > First Name - Text
    > > ...
    > > ...
    > >
    > > *SHIFT*
    > > SHIFT - Text Field and primary Key
    > > Start time - date/time
    > > end time - date/time
    > > ...
    > > ...
    > >
    > > *MACHINE*
    > > MACH - Text Field primary key
    > > MACHINE DETAILS - Memo
    > > ...
    > > ...
    > >
    > >
    > > These tables were linked to limit the data that could be entered on the
    > > main
    > > table to real operators, shifts and machines.
    > > This worked great UNTIL I realized that the LOT could be repeated if the
    > > same operator produced material on more that one machine at the same time.
    > > This is very rare but does happen.
    > >
    > > Therfore I concated all the fileds together into the new LOT. To validate
    > > the LOT data entry some code is used to pull the LOT apart and compare
    > > each
    > > part with the corisponding table of allowable entries.
    > >
    > > When I run reports or querries that use the information in these orphaned
    > > tables I also must write code to pull apart the LOT and get the correct
    > > part.
    > >
    > > My question is was their a better way to solve my original issue? I
    > > thought
    > > about setting a new primary key in the original table that was an auto
    > > number. HOWEVER, the users af the data base were already grumbling about
    > > having to enter each part ofhte LOT seperatly. So combining it made some
    > > sense make them happy. But I fear I may have set myself up for bigger
    > > issues
    > > down the road.
    > >
    > > Any other ideas as to what i should have done?
    > >

    >
    >
    >
     
  10. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Roger:

    OK.

    But having a multi-field primary key is not "stuck". On the contrary, you
    are "stuck" with an additional column of data AND an index on it. What it
    hurts is insert performance and disk space. This is a trivial difference
    for a small number or rows in the table.

    Almost none of the tables I create have autonumber columns. I used to
    program that way, but after extensive testing, I do not find them useful.
    I've heard of and tested the differences extensively. For a database of no
    more the 2 gigabytes, they serve no real purpose.

    The statement, "You should have an autonumber primary key for that table" is
    the problem. Why?

    I know most of the MVPs from previous summits except for the "new crop" and
    I'm looking forward to meeting them, too. Till then.

    Tom Ellison


    "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > In this case, it may well be unnecessary.
    >
    > The only time it would be necessary (to my mind) is if the table were
    > someday linked with to other table at some time. My experience is that no
    > matter what people say at the time, things change. To be stuck with a
    > multi-field primary key and have to join on multiple fields is, I believe,
    > well worth the time to set up an autonumber primary key ahead of time. If
    > it's never used, it's not really hurting anything. If it is needed in the
    > future, you'll already have it.
    >
    > Natural vs Surrogate keys is a religious issue in some circles, and I
    > won't
    > get into that here. I'll just say that nearly EVERY table I create (aside
    > from "linking" tables) has an autonumber primary key. I think most of the
    > MVPs would agree. If you don't want to, I'm fine with that.
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    >> Dear Roger:
    >>
    >> Any particular reason why an autonumber primary key is so important.

    > There
    >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let that be
    >> the
    >> PK.
    >>
    >> I have built multiple databases without autonumber type identities. They
    >> are almost always unnecessary. In a database the scale which is possible
    >> for Access Jet, this has never been a problem.
    >>
    >> Tom Elliosn
    >>
    >>
    >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    >> > Your original idea was correct. You should have an autonumber primary

    > key
    >> > for that table. In addition, you should not store the concatenated

    > value
    >> > for LOT. Instead, you should recreate it on the fly in queries,

    > reports,
    >> > and forms as needed. Then create a Unique Index on the Date, OPER,

    > SHIFT,
    >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    >> >
    >> > I don't know what to tell you about the grumbling. If the operators
    >> > are
    >> > themselves entering data, perhaps you could have a login screen which
    >> > would
    >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    >> >
    >> >
    >> > --
    >> > --Roger Carlson
    >> > MS Access MVP
    >> > Access Database Samples: www.rogersaccesslibrary.com
    >> > Want answers to your Access questions in your Email?
    >> > Free subscription:
    >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >
    >> >
    >> >
    >> >
    >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    >> >> I have a table set up this way.
    >> >>
    >> >> *LOTS*
    >> >> LOT - Text Field and Primary key
    >> >> GRADE - Text Field
    >> >> ...
    >> >> ...
    >> >> ...
    >> >>
    >> >> Our LOT identifiers are unique and are a concatination of the day of

    > the
    >> >> year, 1-365), two digit year, single letter signifiying lot order A,
    >> >> B,

    > C
    >> >> used for production, two digit code to signify operator, single digit

    > to
    >> >> signify shift, and single character to signify machine.
    >> >>
    >> >> Originaly I have the table set up as
    >> >> *LOTS*
    >> >> LOT - Text Field (only day, year part, and order) and Primary key
    >> >> OPER - Text Field
    >> >> SHIFT - Text Field
    >> >> MACHINE - Text Field
    >> >> GRADE - Text Field
    >> >> ...
    >> >> ...
    >> >> ...
    >> >>
    >> >>
    >> >> I also had tables for OPER, SHIFT and Machine
    >> >>
    >> >> *OPER ID*
    >> >> OPER - Text Field and primary Key
    >> >> First Name - Text
    >> >> ...
    >> >> ...
    >> >>
    >> >> *SHIFT*
    >> >> SHIFT - Text Field and primary Key
    >> >> Start time - date/time
    >> >> end time - date/time
    >> >> ...
    >> >> ...
    >> >>
    >> >> *MACHINE*
    >> >> MACH - Text Field primary key
    >> >> MACHINE DETAILS - Memo
    >> >> ...
    >> >> ...
    >> >>
    >> >>
    >> >> These tables were linked to limit the data that could be entered on
    >> >> the
    >> > main
    >> >> table to real operators, shifts and machines.
    >> >> This worked great UNTIL I realized that the LOT could be repeated if

    > the
    >> >> same operator produced material on more that one machine at the same
    >> >> time.
    >> >> This is very rare but does happen.
    >> >>
    >> >> Therfore I concated all the fileds together into the new LOT. To
    >> >> validate
    >> >> the LOT data entry some code is used to pull the LOT apart and compare
    >> > each
    >> >> part with the corisponding table of allowable entries.
    >> >>
    >> >> When I run reports or querries that use the information in these

    > orphaned
    >> >> tables I also must write code to pull apart the LOT and get the
    >> >> correct
    >> > part.
    >> >>
    >> >> My question is was their a better way to solve my original issue? I
    >> > thought
    >> >> about setting a new primary key in the original table that was an auto
    >> >> number. HOWEVER, the users af the data base were already grumbling

    > about
    >> >> having to enter each part ofhte LOT seperatly. So combining it made

    > some
    >> >> sense make them happy. But I fear I may have set myself up for bigger
    >> > issues
    >> >> down the road.
    >> >>
    >> >> Any other ideas as to what i should have done?
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  11. ED007

    ED007
    Expand Collapse
    Guest

    You have confused me. I thought that a primary key could only be ONE field
    in a table. However a little testing in Access showed me that I could set up
    four fields (lot, oper, shift, machine) and define them all as the primary
    key (joined primary key??). Three of them share a relationship with the
    tables that describe operator shift and machine and therefore the values are
    limited. AND access will not let me duplicate a lot operator shift machine
    so the whole lot number is not repeated. So buisness practice is enforced.

    But then I run into trouble. How do I use this joined primary key as the
    forien key in another table. Do I need to set up all four fileds in the
    forgien table as well? Or is their a way to use a field that is a
    concatination of the four fields in the main table?

    "Tom Ellison" wrote:

    > Dear Roger:
    >
    > OK.
    >
    > But having a multi-field primary key is not "stuck". On the contrary, you
    > are "stuck" with an additional column of data AND an index on it. What it
    > hurts is insert performance and disk space. This is a trivial difference
    > for a small number or rows in the table.
    >
    > Almost none of the tables I create have autonumber columns. I used to
    > program that way, but after extensive testing, I do not find them useful.
    > I've heard of and tested the differences extensively. For a database of no
    > more the 2 gigabytes, they serve no real purpose.
    >
    > The statement, "You should have an autonumber primary key for that table" is
    > the problem. Why?
    >
    > I know most of the MVPs from previous summits except for the "new crop" and
    > I'm looking forward to meeting them, too. Till then.
    >
    > Tom Ellison
    >
    >
    > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > > In this case, it may well be unnecessary.
    > >
    > > The only time it would be necessary (to my mind) is if the table were
    > > someday linked with to other table at some time. My experience is that no
    > > matter what people say at the time, things change. To be stuck with a
    > > multi-field primary key and have to join on multiple fields is, I believe,
    > > well worth the time to set up an autonumber primary key ahead of time. If
    > > it's never used, it's not really hurting anything. If it is needed in the
    > > future, you'll already have it.
    > >
    > > Natural vs Surrogate keys is a religious issue in some circles, and I
    > > won't
    > > get into that here. I'll just say that nearly EVERY table I create (aside
    > > from "linking" tables) has an autonumber primary key. I think most of the
    > > MVPs would agree. If you don't want to, I'm fine with that.
    > >
    > > --
    > > --Roger Carlson
    > > MS Access MVP
    > > Access Database Samples: www.rogersaccesslibrary.com
    > > Want answers to your Access questions in your Email?
    > > Free subscription:
    > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >
    > >
    > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > >> Dear Roger:
    > >>
    > >> Any particular reason why an autonumber primary key is so important.

    > > There
    > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let that be
    > >> the
    > >> PK.
    > >>
    > >> I have built multiple databases without autonumber type identities. They
    > >> are almost always unnecessary. In a database the scale which is possible
    > >> for Access Jet, this has never been a problem.
    > >>
    > >> Tom Elliosn
    > >>
    > >>
    > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > >> > Your original idea was correct. You should have an autonumber primary

    > > key
    > >> > for that table. In addition, you should not store the concatenated

    > > value
    > >> > for LOT. Instead, you should recreate it on the fly in queries,

    > > reports,
    > >> > and forms as needed. Then create a Unique Index on the Date, OPER,

    > > SHIFT,
    > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    > >> >
    > >> > I don't know what to tell you about the grumbling. If the operators
    > >> > are
    > >> > themselves entering data, perhaps you could have a login screen which
    > >> > would
    > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    > >> >
    > >> >
    > >> > --
    > >> > --Roger Carlson
    > >> > MS Access MVP
    > >> > Access Database Samples: www.rogersaccesslibrary.com
    > >> > Want answers to your Access questions in your Email?
    > >> > Free subscription:
    > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> >
    > >> >
    > >> >
    > >> >
    > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > >> >> I have a table set up this way.
    > >> >>
    > >> >> *LOTS*
    > >> >> LOT - Text Field and Primary key
    > >> >> GRADE - Text Field
    > >> >> ...
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >> Our LOT identifiers are unique and are a concatination of the day of

    > > the
    > >> >> year, 1-365), two digit year, single letter signifiying lot order A,
    > >> >> B,

    > > C
    > >> >> used for production, two digit code to signify operator, single digit

    > > to
    > >> >> signify shift, and single character to signify machine.
    > >> >>
    > >> >> Originaly I have the table set up as
    > >> >> *LOTS*
    > >> >> LOT - Text Field (only day, year part, and order) and Primary key
    > >> >> OPER - Text Field
    > >> >> SHIFT - Text Field
    > >> >> MACHINE - Text Field
    > >> >> GRADE - Text Field
    > >> >> ...
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >>
    > >> >> I also had tables for OPER, SHIFT and Machine
    > >> >>
    > >> >> *OPER ID*
    > >> >> OPER - Text Field and primary Key
    > >> >> First Name - Text
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >> *SHIFT*
    > >> >> SHIFT - Text Field and primary Key
    > >> >> Start time - date/time
    > >> >> end time - date/time
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >> *MACHINE*
    > >> >> MACH - Text Field primary key
    > >> >> MACHINE DETAILS - Memo
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >>
    > >> >> These tables were linked to limit the data that could be entered on
    > >> >> the
    > >> > main
    > >> >> table to real operators, shifts and machines.
    > >> >> This worked great UNTIL I realized that the LOT could be repeated if

    > > the
    > >> >> same operator produced material on more that one machine at the same
    > >> >> time.
    > >> >> This is very rare but does happen.
    > >> >>
    > >> >> Therfore I concated all the fileds together into the new LOT. To
    > >> >> validate
    > >> >> the LOT data entry some code is used to pull the LOT apart and compare
    > >> > each
    > >> >> part with the corisponding table of allowable entries.
    > >> >>
    > >> >> When I run reports or querries that use the information in these

    > > orphaned
    > >> >> tables I also must write code to pull apart the LOT and get the
    > >> >> correct
    > >> > part.
    > >> >>
    > >> >> My question is was their a better way to solve my original issue? I
    > >> > thought
    > >> >> about setting a new primary key in the original table that was an auto
    > >> >> number. HOWEVER, the users af the data base were already grumbling

    > > about
    > >> >> having to enter each part ofhte LOT seperatly. So combining it made

    > > some
    > >> >> sense make them happy. But I fear I may have set myself up for bigger
    > >> > issues
    > >> >> down the road.
    > >> >>
    > >> >> Any other ideas as to what i should have done?
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
    >
     
  12. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    That's why an autonumber field is preferable. If this table has to
    participate in a relationship with another table, with a multi-field primary
    key, you have to put ALL the fields as a foriegn key in the related table.
    Considering that the fields are all text and an autonumber field is a long
    integer, the advantages of an autonumber primary key in terms of performance
    and ease-of-use are overwhelming.

    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com
    Want answers to your Access questions in your Email?
    Free subscription:
    http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


    "ED007" <ED007@discussions.microsoft.com> wrote in message
    news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    > You have confused me. I thought that a primary key could only be ONE

    field
    > in a table. However a little testing in Access showed me that I could set

    up
    > four fields (lot, oper, shift, machine) and define them all as the primary
    > key (joined primary key??). Three of them share a relationship with the
    > tables that describe operator shift and machine and therefore the values

    are
    > limited. AND access will not let me duplicate a lot operator shift

    machine
    > so the whole lot number is not repeated. So buisness practice is

    enforced.
    >
    > But then I run into trouble. How do I use this joined primary key as the
    > forien key in another table. Do I need to set up all four fileds in the
    > forgien table as well? Or is their a way to use a field that is a
    > concatination of the four fields in the main table?
    >
    > "Tom Ellison" wrote:
    >
    > > Dear Roger:
    > >
    > > OK.
    > >
    > > But having a multi-field primary key is not "stuck". On the contrary,

    you
    > > are "stuck" with an additional column of data AND an index on it. What

    it
    > > hurts is insert performance and disk space. This is a trivial

    difference
    > > for a small number or rows in the table.
    > >
    > > Almost none of the tables I create have autonumber columns. I used to
    > > program that way, but after extensive testing, I do not find them

    useful.
    > > I've heard of and tested the differences extensively. For a database of

    no
    > > more the 2 gigabytes, they serve no real purpose.
    > >
    > > The statement, "You should have an autonumber primary key for that

    table" is
    > > the problem. Why?
    > >
    > > I know most of the MVPs from previous summits except for the "new crop"

    and
    > > I'm looking forward to meeting them, too. Till then.
    > >
    > > Tom Ellison
    > >
    > >
    > > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > > > In this case, it may well be unnecessary.
    > > >
    > > > The only time it would be necessary (to my mind) is if the table were
    > > > someday linked with to other table at some time. My experience is

    that no
    > > > matter what people say at the time, things change. To be stuck with a
    > > > multi-field primary key and have to join on multiple fields is, I

    believe,
    > > > well worth the time to set up an autonumber primary key ahead of time.

    If
    > > > it's never used, it's not really hurting anything. If it is needed in

    the
    > > > future, you'll already have it.
    > > >
    > > > Natural vs Surrogate keys is a religious issue in some circles, and I
    > > > won't
    > > > get into that here. I'll just say that nearly EVERY table I create

    (aside
    > > > from "linking" tables) has an autonumber primary key. I think most of

    the
    > > > MVPs would agree. If you don't want to, I'm fine with that.
    > > >
    > > > --
    > > > --Roger Carlson
    > > > MS Access MVP
    > > > Access Database Samples: www.rogersaccesslibrary.com
    > > > Want answers to your Access questions in your Email?
    > > > Free subscription:
    > > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > > >
    > > >
    > > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > > >> Dear Roger:
    > > >>
    > > >> Any particular reason why an autonumber primary key is so important.
    > > > There
    > > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let that

    be
    > > >> the
    > > >> PK.
    > > >>
    > > >> I have built multiple databases without autonumber type identities.

    They
    > > >> are almost always unnecessary. In a database the scale which is

    possible
    > > >> for Access Jet, this has never been a problem.
    > > >>
    > > >> Tom Elliosn
    > > >>
    > > >>
    > > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > > >> > Your original idea was correct. You should have an autonumber

    primary
    > > > key
    > > >> > for that table. In addition, you should not store the concatenated
    > > > value
    > > >> > for LOT. Instead, you should recreate it on the fly in queries,
    > > > reports,
    > > >> > and forms as needed. Then create a Unique Index on the Date, OPER,
    > > > SHIFT,
    > > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    > > >> >
    > > >> > I don't know what to tell you about the grumbling. If the

    operators
    > > >> > are
    > > >> > themselves entering data, perhaps you could have a login screen

    which
    > > >> > would
    > > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    > > >> >
    > > >> >
    > > >> > --
    > > >> > --Roger Carlson
    > > >> > MS Access MVP
    > > >> > Access Database Samples: www.rogersaccesslibrary.com
    > > >> > Want answers to your Access questions in your Email?
    > > >> > Free subscription:
    > > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > > >> >
    > > >> >
    > > >> >
    > > >> >
    > > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > > >> >> I have a table set up this way.
    > > >> >>
    > > >> >> *LOTS*
    > > >> >> LOT - Text Field and Primary key
    > > >> >> GRADE - Text Field
    > > >> >> ...
    > > >> >> ...
    > > >> >> ...
    > > >> >>
    > > >> >> Our LOT identifiers are unique and are a concatination of the day

    of
    > > > the
    > > >> >> year, 1-365), two digit year, single letter signifiying lot order

    A,
    > > >> >> B,
    > > > C
    > > >> >> used for production, two digit code to signify operator, single

    digit
    > > > to
    > > >> >> signify shift, and single character to signify machine.
    > > >> >>
    > > >> >> Originaly I have the table set up as
    > > >> >> *LOTS*
    > > >> >> LOT - Text Field (only day, year part, and order) and Primary key
    > > >> >> OPER - Text Field
    > > >> >> SHIFT - Text Field
    > > >> >> MACHINE - Text Field
    > > >> >> GRADE - Text Field
    > > >> >> ...
    > > >> >> ...
    > > >> >> ...
    > > >> >>
    > > >> >>
    > > >> >> I also had tables for OPER, SHIFT and Machine
    > > >> >>
    > > >> >> *OPER ID*
    > > >> >> OPER - Text Field and primary Key
    > > >> >> First Name - Text
    > > >> >> ...
    > > >> >> ...
    > > >> >>
    > > >> >> *SHIFT*
    > > >> >> SHIFT - Text Field and primary Key
    > > >> >> Start time - date/time
    > > >> >> end time - date/time
    > > >> >> ...
    > > >> >> ...
    > > >> >>
    > > >> >> *MACHINE*
    > > >> >> MACH - Text Field primary key
    > > >> >> MACHINE DETAILS - Memo
    > > >> >> ...
    > > >> >> ...
    > > >> >>
    > > >> >>
    > > >> >> These tables were linked to limit the data that could be entered

    on
    > > >> >> the
    > > >> > main
    > > >> >> table to real operators, shifts and machines.
    > > >> >> This worked great UNTIL I realized that the LOT could be repeated

    if
    > > > the
    > > >> >> same operator produced material on more that one machine at the

    same
    > > >> >> time.
    > > >> >> This is very rare but does happen.
    > > >> >>
    > > >> >> Therfore I concated all the fileds together into the new LOT. To
    > > >> >> validate
    > > >> >> the LOT data entry some code is used to pull the LOT apart and

    compare
    > > >> > each
    > > >> >> part with the corisponding table of allowable entries.
    > > >> >>
    > > >> >> When I run reports or querries that use the information in these
    > > > orphaned
    > > >> >> tables I also must write code to pull apart the LOT and get the
    > > >> >> correct
    > > >> > part.
    > > >> >>
    > > >> >> My question is was their a better way to solve my original issue?

    I
    > > >> > thought
    > > >> >> about setting a new primary key in the original table that was an

    auto
    > > >> >> number. HOWEVER, the users af the data base were already

    grumbling
    > > > about
    > > >> >> having to enter each part ofhte LOT seperatly. So combining it

    made
    > > > some
    > > >> >> sense make them happy. But I fear I may have set myself up for

    bigger
    > > >> > issues
    > > >> >> down the road.
    > > >> >>
    > > >> >> Any other ideas as to what i should have done?
    > > >> >>
    > > >> >
    > > >> >
    > > >>
    > > >>
    > > >
    > > >

    > >
    > >
    > >
     
  13. ED007

    ED007
    Expand Collapse
    Guest

    Thanks for taking the time to explain this to me. I only started this
    database thing about 3 weeks ago and have learned a lot. But I still have a
    lot to learn. I will know better next time.



    "Roger Carlson" wrote:

    > That's why an autonumber field is preferable. If this table has to
    > participate in a relationship with another table, with a multi-field primary
    > key, you have to put ALL the fields as a foriegn key in the related table.
    > Considering that the fields are all text and an autonumber field is a long
    > integer, the advantages of an autonumber primary key in terms of performance
    > and ease-of-use are overwhelming.
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    > > You have confused me. I thought that a primary key could only be ONE

    > field
    > > in a table. However a little testing in Access showed me that I could set

    > up
    > > four fields (lot, oper, shift, machine) and define them all as the primary
    > > key (joined primary key??). Three of them share a relationship with the
    > > tables that describe operator shift and machine and therefore the values

    > are
    > > limited. AND access will not let me duplicate a lot operator shift

    > machine
    > > so the whole lot number is not repeated. So buisness practice is

    > enforced.
    > >
    > > But then I run into trouble. How do I use this joined primary key as the
    > > forien key in another table. Do I need to set up all four fileds in the
    > > forgien table as well? Or is their a way to use a field that is a
    > > concatination of the four fields in the main table?
    > >
    > > "Tom Ellison" wrote:
    > >
    > > > Dear Roger:
    > > >
    > > > OK.
    > > >
    > > > But having a multi-field primary key is not "stuck". On the contrary,

    > you
    > > > are "stuck" with an additional column of data AND an index on it. What

    > it
    > > > hurts is insert performance and disk space. This is a trivial

    > difference
    > > > for a small number or rows in the table.
    > > >
    > > > Almost none of the tables I create have autonumber columns. I used to
    > > > program that way, but after extensive testing, I do not find them

    > useful.
    > > > I've heard of and tested the differences extensively. For a database of

    > no
    > > > more the 2 gigabytes, they serve no real purpose.
    > > >
    > > > The statement, "You should have an autonumber primary key for that

    > table" is
    > > > the problem. Why?
    > > >
    > > > I know most of the MVPs from previous summits except for the "new crop"

    > and
    > > > I'm looking forward to meeting them, too. Till then.
    > > >
    > > > Tom Ellison
    > > >
    > > >
    > > > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > > > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > > > > In this case, it may well be unnecessary.
    > > > >
    > > > > The only time it would be necessary (to my mind) is if the table were
    > > > > someday linked with to other table at some time. My experience is

    > that no
    > > > > matter what people say at the time, things change. To be stuck with a
    > > > > multi-field primary key and have to join on multiple fields is, I

    > believe,
    > > > > well worth the time to set up an autonumber primary key ahead of time.

    > If
    > > > > it's never used, it's not really hurting anything. If it is needed in

    > the
    > > > > future, you'll already have it.
    > > > >
    > > > > Natural vs Surrogate keys is a religious issue in some circles, and I
    > > > > won't
    > > > > get into that here. I'll just say that nearly EVERY table I create

    > (aside
    > > > > from "linking" tables) has an autonumber primary key. I think most of

    > the
    > > > > MVPs would agree. If you don't want to, I'm fine with that.
    > > > >
    > > > > --
    > > > > --Roger Carlson
    > > > > MS Access MVP
    > > > > Access Database Samples: www.rogersaccesslibrary.com
    > > > > Want answers to your Access questions in your Email?
    > > > > Free subscription:
    > > > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > > > >
    > > > >
    > > > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > > > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > > > >> Dear Roger:
    > > > >>
    > > > >> Any particular reason why an autonumber primary key is so important.
    > > > > There
    > > > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let that

    > be
    > > > >> the
    > > > >> PK.
    > > > >>
    > > > >> I have built multiple databases without autonumber type identities.

    > They
    > > > >> are almost always unnecessary. In a database the scale which is

    > possible
    > > > >> for Access Jet, this has never been a problem.
    > > > >>
    > > > >> Tom Elliosn
    > > > >>
    > > > >>
    > > > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > > > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > > > >> > Your original idea was correct. You should have an autonumber

    > primary
    > > > > key
    > > > >> > for that table. In addition, you should not store the concatenated
    > > > > value
    > > > >> > for LOT. Instead, you should recreate it on the fly in queries,
    > > > > reports,
    > > > >> > and forms as needed. Then create a Unique Index on the Date, OPER,
    > > > > SHIFT,
    > > > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    > > > >> >
    > > > >> > I don't know what to tell you about the grumbling. If the

    > operators
    > > > >> > are
    > > > >> > themselves entering data, perhaps you could have a login screen

    > which
    > > > >> > would
    > > > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    > > > >> >
    > > > >> >
    > > > >> > --
    > > > >> > --Roger Carlson
    > > > >> > MS Access MVP
    > > > >> > Access Database Samples: www.rogersaccesslibrary.com
    > > > >> > Want answers to your Access questions in your Email?
    > > > >> > Free subscription:
    > > > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > > > >> >
    > > > >> >
    > > > >> >
    > > > >> >
    > > > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > > > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > > > >> >> I have a table set up this way.
    > > > >> >>
    > > > >> >> *LOTS*
    > > > >> >> LOT - Text Field and Primary key
    > > > >> >> GRADE - Text Field
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >>
    > > > >> >> Our LOT identifiers are unique and are a concatination of the day

    > of
    > > > > the
    > > > >> >> year, 1-365), two digit year, single letter signifiying lot order

    > A,
    > > > >> >> B,
    > > > > C
    > > > >> >> used for production, two digit code to signify operator, single

    > digit
    > > > > to
    > > > >> >> signify shift, and single character to signify machine.
    > > > >> >>
    > > > >> >> Originaly I have the table set up as
    > > > >> >> *LOTS*
    > > > >> >> LOT - Text Field (only day, year part, and order) and Primary key
    > > > >> >> OPER - Text Field
    > > > >> >> SHIFT - Text Field
    > > > >> >> MACHINE - Text Field
    > > > >> >> GRADE - Text Field
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >>
    > > > >> >>
    > > > >> >> I also had tables for OPER, SHIFT and Machine
    > > > >> >>
    > > > >> >> *OPER ID*
    > > > >> >> OPER - Text Field and primary Key
    > > > >> >> First Name - Text
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >>
    > > > >> >> *SHIFT*
    > > > >> >> SHIFT - Text Field and primary Key
    > > > >> >> Start time - date/time
    > > > >> >> end time - date/time
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >>
    > > > >> >> *MACHINE*
    > > > >> >> MACH - Text Field primary key
    > > > >> >> MACHINE DETAILS - Memo
    > > > >> >> ...
    > > > >> >> ...
    > > > >> >>
    > > > >> >>
    > > > >> >> These tables were linked to limit the data that could be entered

    > on
    > > > >> >> the
    > > > >> > main
    > > > >> >> table to real operators, shifts and machines.
    > > > >> >> This worked great UNTIL I realized that the LOT could be repeated

    > if
    > > > > the
    > > > >> >> same operator produced material on more that one machine at the

    > same
    > > > >> >> time.
    > > > >> >> This is very rare but does happen.
    > > > >> >>
    > > > >> >> Therfore I concated all the fileds together into the new LOT. To
    > > > >> >> validate
    > > > >> >> the LOT data entry some code is used to pull the LOT apart and

    > compare
    > > > >> > each
    > > > >> >> part with the corisponding table of allowable entries.
    > > > >> >>
    > > > >> >> When I run reports or querries that use the information in these
    > > > > orphaned
    > > > >> >> tables I also must write code to pull apart the LOT and get the
    > > > >> >> correct
    > > > >> > part.
    > > > >> >>
    > > > >> >> My question is was their a better way to solve my original issue?

    > I
    > > > >> > thought
    > > > >> >> about setting a new primary key in the original table that was an

    > auto
    > > > >> >> number. HOWEVER, the users af the data base were already

    > grumbling
    > > > > about
    > > > >> >> having to enter each part ofhte LOT seperatly. So combining it

    > made
    > > > > some
    > > > >> >> sense make them happy. But I fear I may have set myself up for

    > bigger
    > > > >> > issues
    > > > >> >> down the road.
    > > > >> >>
    > > > >> >> Any other ideas as to what i should have done?
    > > > >> >>
    > > > >> >
    > > > >> >
    > > > >>
    > > > >>
    > > > >
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  14. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

    Hi Tom,

    As Roger observed, people tend to embrace their own points of view and
    the surrogate key, natural key issue tends to be joined with religious
    fervor. I discovered that back in the days of Access 1.x .Someone had
    a question about the issue so I innocently proposed the Autonumber
    point of view. There was a s..tstorm that went on for days. The
    sides were about even at that time.

    I happen to come down on the other side of the issue from you but that
    isn't really important. We're all free to do as we please. What is
    important is that we are able to create and deliver "bullet proof"
    applications to our clients.

    It is also important that when you render judgements in support of
    your point of view you should take care that the rationale is valid.

    What follows is a few of my observations:

    The greatest single value the Autonumber surrogate key is that is has
    no meaning whatever in the context of any application in which it
    appears.

    Every professional Access developer has been deceived by well intended
    lies. We've got scars. Unfortunately when the lie is revealed the
    developer has to re-do some work and the client (who lied) rarely
    takes responsibility and often places blame on the developer. The lie
    is "this will NEVER CHANGE". By the time I'd been developing with
    Access for a year or so I went to Autonumber primary keys with a will.
    I've never had to go back to deal with a changed Autonumber. So,
    IMHO, the development process is more predictable, more efficient and
    less risky using the surrogate key vice the natural key. Those are
    very compelling reasons.

    I go even further and insist that the Autonumber/surrogate key never
    be revealed to the end users. If they see the number it will often
    suck them in. They want to play with it. They want to assign meaning
    to it. Many micromanaging anal types want to dictate that you treat
    it according to their tastes. It's a waste of time and energy. I
    know that MS proudly displays Autonumbers in their sample apps. IMHO
    they're wrong to do so.

    The "additional column of data AND an index on it" doesn't ring true
    to me. An index is an index whether on the natural key or the
    surrogate key. Parity.

    I believe that the disk space issue usually falls in favor of the
    Autonumber point of view. It may favor the natural key point of view
    if the ratio of the One-to-Many is very low. The "extra column"
    comparison at the One side of a relation is true. On the Many side,
    the Foreign Key is a long Integer; Each and every natural key will
    take many times more disk space than a Long Integer. Now throw in
    another Long Integer for the Autonumber primary key of the Many side
    table and you'll still require less disk space. That natural key as
    the Foreign Key uses more turf to do the same thing. When you
    consider that for each record on the One side there may be thousands
    of records on the Many side the difference can be considerable. A
    very low ratio favors the natural key. A very high ratio favors the
    surrogate side. The ratio where parity falls will be different for
    each application and relation and the storage required for the natural
    key.

    That being said, I rarely consider the disk space issues regarding
    Access. Our time and our costs and our delivering on schedule are
    much more important to our clients. Storage and clock speeds keep
    growing and getting cheaper. Only once in a while do I get concerned
    with speed. Then I get really concerned until things get resolved.

    I confess that I've never dug into the innards of Jet to the level of
    knowing all of the nitty gritty details of how it gets things done.

    I catch some of your posts. Like many of the MVPs, you are much more
    patient with people than I am.

    Regards,
    --
    -Larry-
    --

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:uPs8$dAdGHA.4148@TK2MSFTNGP05.phx.gbl...
    > Dear Roger:
    >
    > OK.
    >
    > But having a multi-field primary key is not "stuck". On the

    contrary, you
    > are "stuck" with an additional column of data AND an index on it.

    What it
    > hurts is insert performance and disk space. This is a trivial

    difference
    > for a small number or rows in the table.
    >
    > Almost none of the tables I create have autonumber columns. I used

    to
    > program that way, but after extensive testing, I do not find them

    useful.
    > I've heard of and tested the differences extensively. For a

    database of no
    > more the 2 gigabytes, they serve no real purpose.
    >
    > The statement, "You should have an autonumber primary key for that

    table" is
    > the problem. Why?
    >
    > I know most of the MVPs from previous summits except for the "new

    crop" and
    > I'm looking forward to meeting them, too. Till then.
    >
    > Tom Ellison
    >
    >
    > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > > In this case, it may well be unnecessary.
    > >
    > > The only time it would be necessary (to my mind) is if the table

    were
    > > someday linked with to other table at some time. My experience is

    that no
    > > matter what people say at the time, things change. To be stuck

    with a
    > > multi-field primary key and have to join on multiple fields is, I

    believe,
    > > well worth the time to set up an autonumber primary key ahead of

    time. If
    > > it's never used, it's not really hurting anything. If it is

    needed in the
    > > future, you'll already have it.
    > >
    > > Natural vs Surrogate keys is a religious issue in some circles,

    and I
    > > won't
    > > get into that here. I'll just say that nearly EVERY table I

    create (aside
    > > from "linking" tables) has an autonumber primary key. I think

    most of the
    > > MVPs would agree. If you don't want to, I'm fine with that.
    > >
    > > --
    > > --Roger Carlson
    > > MS Access MVP
    > > Access Database Samples: www.rogersaccesslibrary.com
    > > Want answers to your Access questions in your Email?
    > > Free subscription:
    > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >
    > >
    > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > >> Dear Roger:
    > >>
    > >> Any particular reason why an autonumber primary key is so

    important.
    > > There
    > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let

    that be
    > >> the
    > >> PK.
    > >>
    > >> I have built multiple databases without autonumber type

    identities. They
    > >> are almost always unnecessary. In a database the scale which is

    possible
    > >> for Access Jet, this has never been a problem.
    > >>
    > >> Tom Elliosn
    > >>
    > >>
    > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in

    message
    > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > >> > Your original idea was correct. You should have an autonumber

    primary
    > > key
    > >> > for that table. In addition, you should not store the

    concatenated
    > > value
    > >> > for LOT. Instead, you should recreate it on the fly in

    queries,
    > > reports,
    > >> > and forms as needed. Then create a Unique Index on the Date,

    OPER,
    > > SHIFT,
    > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    > >> >
    > >> > I don't know what to tell you about the grumbling. If the

    operators
    > >> > are
    > >> > themselves entering data, perhaps you could have a login screen

    which
    > >> > would
    > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    > >> >
    > >> >
    > >> > --
    > >> > --Roger Carlson
    > >> > MS Access MVP
    > >> > Access Database Samples: www.rogersaccesslibrary.com
    > >> > Want answers to your Access questions in your Email?
    > >> > Free subscription:
    > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> >
    > >> >
    > >> >
    > >> >
    > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > >> >> I have a table set up this way.
    > >> >>
    > >> >> *LOTS*
    > >> >> LOT - Text Field and Primary key
    > >> >> GRADE - Text Field
    > >> >> ...
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >> Our LOT identifiers are unique and are a concatination of the

    day of
    > > the
    > >> >> year, 1-365), two digit year, single letter signifiying lot

    order A,
    > >> >> B,

    > > C
    > >> >> used for production, two digit code to signify operator,

    single digit
    > > to
    > >> >> signify shift, and single character to signify machine.
    > >> >>
    > >> >> Originaly I have the table set up as
    > >> >> *LOTS*
    > >> >> LOT - Text Field (only day, year part, and order) and Primary

    key
    > >> >> OPER - Text Field
    > >> >> SHIFT - Text Field
    > >> >> MACHINE - Text Field
    > >> >> GRADE - Text Field
    > >> >> ...
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >>
    > >> >> I also had tables for OPER, SHIFT and Machine
    > >> >>
    > >> >> *OPER ID*
    > >> >> OPER - Text Field and primary Key
    > >> >> First Name - Text
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >> *SHIFT*
    > >> >> SHIFT - Text Field and primary Key
    > >> >> Start time - date/time
    > >> >> end time - date/time
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >> *MACHINE*
    > >> >> MACH - Text Field primary key
    > >> >> MACHINE DETAILS - Memo
    > >> >> ...
    > >> >> ...
    > >> >>
    > >> >>
    > >> >> These tables were linked to limit the data that could be

    entered on
    > >> >> the
    > >> > main
    > >> >> table to real operators, shifts and machines.
    > >> >> This worked great UNTIL I realized that the LOT could be

    repeated if
    > > the
    > >> >> same operator produced material on more that one machine at

    the same
    > >> >> time.
    > >> >> This is very rare but does happen.
    > >> >>
    > >> >> Therfore I concated all the fileds together into the new LOT.

    To
    > >> >> validate
    > >> >> the LOT data entry some code is used to pull the LOT apart and

    compare
    > >> > each
    > >> >> part with the corisponding table of allowable entries.
    > >> >>
    > >> >> When I run reports or querries that use the information in

    these
    > > orphaned
    > >> >> tables I also must write code to pull apart the LOT and get

    the
    > >> >> correct
    > >> > part.
    > >> >>
    > >> >> My question is was their a better way to solve my original

    issue? I
    > >> > thought
    > >> >> about setting a new primary key in the original table that was

    an auto
    > >> >> number. HOWEVER, the users af the data base were already

    grumbling
    > > about
    > >> >> having to enter each part ofhte LOT seperatly. So combining

    it made
    > > some
    > >> >> sense make them happy. But I fear I may have set myself up

    for bigger
    > >> > issues
    > >> >> down the road.
    > >> >>
    > >> >> Any other ideas as to what i should have done?
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  15. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Roger:

    An autonumber column is not perferable. And, if you do use one, please,
    please, do not make it the primary key. Make it a separate, unique key, but
    not the primary key.

    The primary key is used to put the physical rows of the table in order when
    you do a compact and repair. Now, which has more advantage: to put the
    rows of the table in the order of a unique natural key, or to put the rows
    in the order of the autonumber?

    I propose there is NO advantage in putting the rows of the table in
    autonumber order. This order is almost never wanted.

    When you perform a report or form, you will usually be looking at the rows
    of the table in the natural key order. If the rows in the table sre in this
    physical order, you will experience increased performance by making the
    primary key the natural key. You could still have an autonumber column in
    the table and make a unique key of it. If you must, build relationships on
    this. There's no disadvantage to having the autonumber column be a unique
    index but not the primary key, but there are definite performance advantages
    in having the natural key be the primary index.

    Please, think about it. Autonumber surrogate keys are not appropriate as a
    primary key. If you're going to use this technique, do it right. You're
    going to need a unique natural key almost every time anyway, and performance
    will benefit from this being the primary key.

    Tom Ellison


    "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    news:eOUSebDdGHA.1272@TK2MSFTNGP03.phx.gbl...
    > That's why an autonumber field is preferable. If this table has to
    > participate in a relationship with another table, with a multi-field
    > primary
    > key, you have to put ALL the fields as a foriegn key in the related table.
    > Considering that the fields are all text and an autonumber field is a long
    > integer, the advantages of an autonumber primary key in terms of
    > performance
    > and ease-of-use are overwhelming.
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    >> You have confused me. I thought that a primary key could only be ONE

    > field
    >> in a table. However a little testing in Access showed me that I could
    >> set

    > up
    >> four fields (lot, oper, shift, machine) and define them all as the
    >> primary
    >> key (joined primary key??). Three of them share a relationship with the
    >> tables that describe operator shift and machine and therefore the values

    > are
    >> limited. AND access will not let me duplicate a lot operator shift

    > machine
    >> so the whole lot number is not repeated. So buisness practice is

    > enforced.
    >>
    >> But then I run into trouble. How do I use this joined primary key as the
    >> forien key in another table. Do I need to set up all four fileds in the
    >> forgien table as well? Or is their a way to use a field that is a
    >> concatination of the four fields in the main table?
    >>
    >> "Tom Ellison" wrote:
    >>
    >> > Dear Roger:
    >> >
    >> > OK.
    >> >
    >> > But having a multi-field primary key is not "stuck". On the contrary,

    > you
    >> > are "stuck" with an additional column of data AND an index on it. What

    > it
    >> > hurts is insert performance and disk space. This is a trivial

    > difference
    >> > for a small number or rows in the table.
    >> >
    >> > Almost none of the tables I create have autonumber columns. I used to
    >> > program that way, but after extensive testing, I do not find them

    > useful.
    >> > I've heard of and tested the differences extensively. For a database
    >> > of

    > no
    >> > more the 2 gigabytes, they serve no real purpose.
    >> >
    >> > The statement, "You should have an autonumber primary key for that

    > table" is
    >> > the problem. Why?
    >> >
    >> > I know most of the MVPs from previous summits except for the "new crop"

    > and
    >> > I'm looking forward to meeting them, too. Till then.
    >> >
    >> > Tom Ellison
    >> >
    >> >
    >> > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    >> > > In this case, it may well be unnecessary.
    >> > >
    >> > > The only time it would be necessary (to my mind) is if the table were
    >> > > someday linked with to other table at some time. My experience is

    > that no
    >> > > matter what people say at the time, things change. To be stuck with
    >> > > a
    >> > > multi-field primary key and have to join on multiple fields is, I

    > believe,
    >> > > well worth the time to set up an autonumber primary key ahead of
    >> > > time.

    > If
    >> > > it's never used, it's not really hurting anything. If it is needed
    >> > > in

    > the
    >> > > future, you'll already have it.
    >> > >
    >> > > Natural vs Surrogate keys is a religious issue in some circles, and I
    >> > > won't
    >> > > get into that here. I'll just say that nearly EVERY table I create

    > (aside
    >> > > from "linking" tables) has an autonumber primary key. I think most
    >> > > of

    > the
    >> > > MVPs would agree. If you don't want to, I'm fine with that.
    >> > >
    >> > > --
    >> > > --Roger Carlson
    >> > > MS Access MVP
    >> > > Access Database Samples: www.rogersaccesslibrary.com
    >> > > Want answers to your Access questions in your Email?
    >> > > Free subscription:
    >> > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> > >
    >> > >
    >> > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >> > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    >> > >> Dear Roger:
    >> > >>
    >> > >> Any particular reason why an autonumber primary key is so important.
    >> > > There
    >> > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let that

    > be
    >> > >> the
    >> > >> PK.
    >> > >>
    >> > >> I have built multiple databases without autonumber type identities.

    > They
    >> > >> are almost always unnecessary. In a database the scale which is

    > possible
    >> > >> for Access Jet, this has never been a problem.
    >> > >>
    >> > >> Tom Elliosn
    >> > >>
    >> > >>
    >> > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    >> > >> > Your original idea was correct. You should have an autonumber

    > primary
    >> > > key
    >> > >> > for that table. In addition, you should not store the
    >> > >> > concatenated
    >> > > value
    >> > >> > for LOT. Instead, you should recreate it on the fly in queries,
    >> > > reports,
    >> > >> > and forms as needed. Then create a Unique Index on the Date,
    >> > >> > OPER,
    >> > > SHIFT,
    >> > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    >> > >> >
    >> > >> > I don't know what to tell you about the grumbling. If the

    > operators
    >> > >> > are
    >> > >> > themselves entering data, perhaps you could have a login screen

    > which
    >> > >> > would
    >> > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    >> > >> >
    >> > >> >
    >> > >> > --
    >> > >> > --Roger Carlson
    >> > >> > MS Access MVP
    >> > >> > Access Database Samples: www.rogersaccesslibrary.com
    >> > >> > Want answers to your Access questions in your Email?
    >> > >> > Free subscription:
    >> > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> > >> >
    >> > >> >
    >> > >> >
    >> > >> >
    >> > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    >> > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    >> > >> >> I have a table set up this way.
    >> > >> >>
    >> > >> >> *LOTS*
    >> > >> >> LOT - Text Field and Primary key
    >> > >> >> GRADE - Text Field
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >>
    >> > >> >> Our LOT identifiers are unique and are a concatination of the day

    > of
    >> > > the
    >> > >> >> year, 1-365), two digit year, single letter signifiying lot order

    > A,
    >> > >> >> B,
    >> > > C
    >> > >> >> used for production, two digit code to signify operator, single

    > digit
    >> > > to
    >> > >> >> signify shift, and single character to signify machine.
    >> > >> >>
    >> > >> >> Originaly I have the table set up as
    >> > >> >> *LOTS*
    >> > >> >> LOT - Text Field (only day, year part, and order) and Primary key
    >> > >> >> OPER - Text Field
    >> > >> >> SHIFT - Text Field
    >> > >> >> MACHINE - Text Field
    >> > >> >> GRADE - Text Field
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >>
    >> > >> >>
    >> > >> >> I also had tables for OPER, SHIFT and Machine
    >> > >> >>
    >> > >> >> *OPER ID*
    >> > >> >> OPER - Text Field and primary Key
    >> > >> >> First Name - Text
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >>
    >> > >> >> *SHIFT*
    >> > >> >> SHIFT - Text Field and primary Key
    >> > >> >> Start time - date/time
    >> > >> >> end time - date/time
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >>
    >> > >> >> *MACHINE*
    >> > >> >> MACH - Text Field primary key
    >> > >> >> MACHINE DETAILS - Memo
    >> > >> >> ...
    >> > >> >> ...
    >> > >> >>
    >> > >> >>
    >> > >> >> These tables were linked to limit the data that could be entered

    > on
    >> > >> >> the
    >> > >> > main
    >> > >> >> table to real operators, shifts and machines.
    >> > >> >> This worked great UNTIL I realized that the LOT could be repeated

    > if
    >> > > the
    >> > >> >> same operator produced material on more that one machine at the

    > same
    >> > >> >> time.
    >> > >> >> This is very rare but does happen.
    >> > >> >>
    >> > >> >> Therfore I concated all the fileds together into the new LOT. To
    >> > >> >> validate
    >> > >> >> the LOT data entry some code is used to pull the LOT apart and

    > compare
    >> > >> > each
    >> > >> >> part with the corisponding table of allowable entries.
    >> > >> >>
    >> > >> >> When I run reports or querries that use the information in these
    >> > > orphaned
    >> > >> >> tables I also must write code to pull apart the LOT and get the
    >> > >> >> correct
    >> > >> > part.
    >> > >> >>
    >> > >> >> My question is was their a better way to solve my original issue?

    > I
    >> > >> > thought
    >> > >> >> about setting a new primary key in the original table that was an

    > auto
    >> > >> >> number. HOWEVER, the users af the data base were already

    > grumbling
    >> > > about
    >> > >> >> having to enter each part ofhte LOT seperatly. So combining it

    > made
    >> > > some
    >> > >> >> sense make them happy. But I fear I may have set myself up for

    > bigger
    >> > >> > issues
    >> > >> >> down the road.
    >> > >> >>
    >> > >> >> Any other ideas as to what i should have done?
    >> > >> >>
    >> > >> >
    >> > >> >
    >> > >>
    >> > >>
    >> > >
    >> > >
    >> >
    >> >
    >> >

    >
    >
     
  16. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    As I said, I'm not interested in debating this here.

    I understand that Access stores the primary key as a clustered index. I
    also understand that relationships do not have to be created on a Primary
    Key, a Unique Index will do. I have, in fact, created tables exactly as you
    describe. However, it is not my preferred method and it is not "wrong".

    I have no problem with you creating natural primary keys, if that is your
    preference, but it IS a preference. Please do not confuse your preference
    with gospel.

    --
    --Roger Carlson
    MS Access MVP
    www.rogersaccesslibrary.com

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:eq%23sh7JdGHA.4148@TK2MSFTNGP05.phx.gbl...
    > Dear Roger:
    >
    > An autonumber column is not perferable. And, if you do use one, please,
    > please, do not make it the primary key. Make it a separate, unique key,

    but
    > not the primary key.
    >
    > The primary key is used to put the physical rows of the table in order

    when
    > you do a compact and repair. Now, which has more advantage: to put the
    > rows of the table in the order of a unique natural key, or to put the rows
    > in the order of the autonumber?
    >
    > I propose there is NO advantage in putting the rows of the table in
    > autonumber order. This order is almost never wanted.
    >
    > When you perform a report or form, you will usually be looking at the rows
    > of the table in the natural key order. If the rows in the table sre in

    this
    > physical order, you will experience increased performance by making the
    > primary key the natural key. You could still have an autonumber column in
    > the table and make a unique key of it. If you must, build relationships

    on
    > this. There's no disadvantage to having the autonumber column be a unique
    > index but not the primary key, but there are definite performance

    advantages
    > in having the natural key be the primary index.
    >
    > Please, think about it. Autonumber surrogate keys are not appropriate as

    a
    > primary key. If you're going to use this technique, do it right. You're
    > going to need a unique natural key almost every time anyway, and

    performance
    > will benefit from this being the primary key.
    >
    > Tom Ellison
    >
    >
    > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > news:eOUSebDdGHA.1272@TK2MSFTNGP03.phx.gbl...
    > > That's why an autonumber field is preferable. If this table has to
    > > participate in a relationship with another table, with a multi-field
    > > primary
    > > key, you have to put ALL the fields as a foriegn key in the related

    table.
    > > Considering that the fields are all text and an autonumber field is a

    long
    > > integer, the advantages of an autonumber primary key in terms of
    > > performance
    > > and ease-of-use are overwhelming.
    > >
    > > --
    > > --Roger Carlson
    > > MS Access MVP
    > > Access Database Samples: www.rogersaccesslibrary.com
    > > Want answers to your Access questions in your Email?
    > > Free subscription:
    > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >
    > >
    > > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > > news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    > >> You have confused me. I thought that a primary key could only be ONE

    > > field
    > >> in a table. However a little testing in Access showed me that I could
    > >> set

    > > up
    > >> four fields (lot, oper, shift, machine) and define them all as the
    > >> primary
    > >> key (joined primary key??). Three of them share a relationship with

    the
    > >> tables that describe operator shift and machine and therefore the

    values
    > > are
    > >> limited. AND access will not let me duplicate a lot operator shift

    > > machine
    > >> so the whole lot number is not repeated. So buisness practice is

    > > enforced.
    > >>
    > >> But then I run into trouble. How do I use this joined primary key as

    the
    > >> forien key in another table. Do I need to set up all four fileds in

    the
    > >> forgien table as well? Or is their a way to use a field that is a
    > >> concatination of the four fields in the main table?
    > >>
    > >> "Tom Ellison" wrote:
    > >>
    > >> > Dear Roger:
    > >> >
    > >> > OK.
    > >> >
    > >> > But having a multi-field primary key is not "stuck". On the

    contrary,
    > > you
    > >> > are "stuck" with an additional column of data AND an index on it.

    What
    > > it
    > >> > hurts is insert performance and disk space. This is a trivial

    > > difference
    > >> > for a small number or rows in the table.
    > >> >
    > >> > Almost none of the tables I create have autonumber columns. I used

    to
    > >> > program that way, but after extensive testing, I do not find them

    > > useful.
    > >> > I've heard of and tested the differences extensively. For a database
    > >> > of

    > > no
    > >> > more the 2 gigabytes, they serve no real purpose.
    > >> >
    > >> > The statement, "You should have an autonumber primary key for that

    > > table" is
    > >> > the problem. Why?
    > >> >
    > >> > I know most of the MVPs from previous summits except for the "new

    crop"
    > > and
    > >> > I'm looking forward to meeting them, too. Till then.
    > >> >
    > >> > Tom Ellison
    > >> >
    > >> >
    > >> > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > >> > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > >> > > In this case, it may well be unnecessary.
    > >> > >
    > >> > > The only time it would be necessary (to my mind) is if the table

    were
    > >> > > someday linked with to other table at some time. My experience is

    > > that no
    > >> > > matter what people say at the time, things change. To be stuck

    with
    > >> > > a
    > >> > > multi-field primary key and have to join on multiple fields is, I

    > > believe,
    > >> > > well worth the time to set up an autonumber primary key ahead of
    > >> > > time.

    > > If
    > >> > > it's never used, it's not really hurting anything. If it is needed
    > >> > > in

    > > the
    > >> > > future, you'll already have it.
    > >> > >
    > >> > > Natural vs Surrogate keys is a religious issue in some circles, and

    I
    > >> > > won't
    > >> > > get into that here. I'll just say that nearly EVERY table I create

    > > (aside
    > >> > > from "linking" tables) has an autonumber primary key. I think most
    > >> > > of

    > > the
    > >> > > MVPs would agree. If you don't want to, I'm fine with that.
    > >> > >
    > >> > > --
    > >> > > --Roger Carlson
    > >> > > MS Access MVP
    > >> > > Access Database Samples: www.rogersaccesslibrary.com
    > >> > > Want answers to your Access questions in your Email?
    > >> > > Free subscription:
    > >> > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> > >
    > >> > >
    > >> > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > >> > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > >> > >> Dear Roger:
    > >> > >>
    > >> > >> Any particular reason why an autonumber primary key is so

    important.
    > >> > > There
    > >> > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let

    that
    > > be
    > >> > >> the
    > >> > >> PK.
    > >> > >>
    > >> > >> I have built multiple databases without autonumber type

    identities.
    > > They
    > >> > >> are almost always unnecessary. In a database the scale which is

    > > possible
    > >> > >> for Access Jet, this has never been a problem.
    > >> > >>
    > >> > >> Tom Elliosn
    > >> > >>
    > >> > >>
    > >> > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in

    message
    > >> > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > >> > >> > Your original idea was correct. You should have an autonumber

    > > primary
    > >> > > key
    > >> > >> > for that table. In addition, you should not store the
    > >> > >> > concatenated
    > >> > > value
    > >> > >> > for LOT. Instead, you should recreate it on the fly in queries,
    > >> > > reports,
    > >> > >> > and forms as needed. Then create a Unique Index on the Date,
    > >> > >> > OPER,
    > >> > > SHIFT,
    > >> > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    > >> > >> >
    > >> > >> > I don't know what to tell you about the grumbling. If the

    > > operators
    > >> > >> > are
    > >> > >> > themselves entering data, perhaps you could have a login screen

    > > which
    > >> > >> > would
    > >> > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    > >> > >> >
    > >> > >> >
    > >> > >> > --
    > >> > >> > --Roger Carlson
    > >> > >> > MS Access MVP
    > >> > >> > Access Database Samples: www.rogersaccesslibrary.com
    > >> > >> > Want answers to your Access questions in your Email?
    > >> > >> > Free subscription:
    > >> > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> > >> >
    > >> > >> >
    > >> > >> >
    > >> > >> >
    > >> > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > >> > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > >> > >> >> I have a table set up this way.
    > >> > >> >>
    > >> > >> >> *LOTS*
    > >> > >> >> LOT - Text Field and Primary key
    > >> > >> >> GRADE - Text Field
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >>
    > >> > >> >> Our LOT identifiers are unique and are a concatination of the

    day
    > > of
    > >> > > the
    > >> > >> >> year, 1-365), two digit year, single letter signifiying lot

    order
    > > A,
    > >> > >> >> B,
    > >> > > C
    > >> > >> >> used for production, two digit code to signify operator, single

    > > digit
    > >> > > to
    > >> > >> >> signify shift, and single character to signify machine.
    > >> > >> >>
    > >> > >> >> Originaly I have the table set up as
    > >> > >> >> *LOTS*
    > >> > >> >> LOT - Text Field (only day, year part, and order) and Primary

    key
    > >> > >> >> OPER - Text Field
    > >> > >> >> SHIFT - Text Field
    > >> > >> >> MACHINE - Text Field
    > >> > >> >> GRADE - Text Field
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >>
    > >> > >> >>
    > >> > >> >> I also had tables for OPER, SHIFT and Machine
    > >> > >> >>
    > >> > >> >> *OPER ID*
    > >> > >> >> OPER - Text Field and primary Key
    > >> > >> >> First Name - Text
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >>
    > >> > >> >> *SHIFT*
    > >> > >> >> SHIFT - Text Field and primary Key
    > >> > >> >> Start time - date/time
    > >> > >> >> end time - date/time
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >>
    > >> > >> >> *MACHINE*
    > >> > >> >> MACH - Text Field primary key
    > >> > >> >> MACHINE DETAILS - Memo
    > >> > >> >> ...
    > >> > >> >> ...
    > >> > >> >>
    > >> > >> >>
    > >> > >> >> These tables were linked to limit the data that could be

    entered
    > > on
    > >> > >> >> the
    > >> > >> > main
    > >> > >> >> table to real operators, shifts and machines.
    > >> > >> >> This worked great UNTIL I realized that the LOT could be

    repeated
    > > if
    > >> > > the
    > >> > >> >> same operator produced material on more that one machine at the

    > > same
    > >> > >> >> time.
    > >> > >> >> This is very rare but does happen.
    > >> > >> >>
    > >> > >> >> Therfore I concated all the fileds together into the new LOT.

    To
    > >> > >> >> validate
    > >> > >> >> the LOT data entry some code is used to pull the LOT apart and

    > > compare
    > >> > >> > each
    > >> > >> >> part with the corisponding table of allowable entries.
    > >> > >> >>
    > >> > >> >> When I run reports or querries that use the information in

    these
    > >> > > orphaned
    > >> > >> >> tables I also must write code to pull apart the LOT and get the
    > >> > >> >> correct
    > >> > >> > part.
    > >> > >> >>
    > >> > >> >> My question is was their a better way to solve my original

    issue?
    > > I
    > >> > >> > thought
    > >> > >> >> about setting a new primary key in the original table that was

    an
    > > auto
    > >> > >> >> number. HOWEVER, the users af the data base were already

    > > grumbling
    > >> > > about
    > >> > >> >> having to enter each part ofhte LOT seperatly. So combining it

    > > made
    > >> > > some
    > >> > >> >> sense make them happy. But I fear I may have set myself up for

    > > bigger
    > >> > >> > issues
    > >> > >> >> down the road.
    > >> > >> >>
    > >> > >> >> Any other ideas as to what i should have done?
    > >> > >> >>
    > >> > >> >
    > >> > >> >
    > >> > >>
    > >> > >>
    > >> > >
    > >> > >
    > >> >
    > >> >
    > >> >

    > >
    > >

    >
    >
     
  17. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Roger:

    It was your statement:

    "You should have an autonumber primary key for that table."

    This is your preferred method, but you present it as a rule that "should" be
    performed at all times. It is not I who have confused his preference with
    "gospel." And that, from the beginning, was my objection.

    The point is, you're permitted only one primary key or clustered index. You
    should not waste it on a surrogate key, which is virtually never the order
    you would want to show the rows in the table. That is something that is
    simply true almost all the time. Your statement that the autonumber
    "should" be the primary key is simply wrong in most cases.

    Tom Ellison


    "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    news:%23ZOaa5PdGHA.4720@TK2MSFTNGP03.phx.gbl...
    > As I said, I'm not interested in debating this here.
    >
    > I understand that Access stores the primary key as a clustered index. I
    > also understand that relationships do not have to be created on a Primary
    > Key, a Unique Index will do. I have, in fact, created tables exactly as
    > you
    > describe. However, it is not my preferred method and it is not "wrong".
    >
    > I have no problem with you creating natural primary keys, if that is your
    > preference, but it IS a preference. Please do not confuse your preference
    > with gospel.
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > www.rogersaccesslibrary.com
    >
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:eq%23sh7JdGHA.4148@TK2MSFTNGP05.phx.gbl...
    >> Dear Roger:
    >>
    >> An autonumber column is not perferable. And, if you do use one, please,
    >> please, do not make it the primary key. Make it a separate, unique key,

    > but
    >> not the primary key.
    >>
    >> The primary key is used to put the physical rows of the table in order

    > when
    >> you do a compact and repair. Now, which has more advantage: to put the
    >> rows of the table in the order of a unique natural key, or to put the
    >> rows
    >> in the order of the autonumber?
    >>
    >> I propose there is NO advantage in putting the rows of the table in
    >> autonumber order. This order is almost never wanted.
    >>
    >> When you perform a report or form, you will usually be looking at the
    >> rows
    >> of the table in the natural key order. If the rows in the table sre in

    > this
    >> physical order, you will experience increased performance by making the
    >> primary key the natural key. You could still have an autonumber column
    >> in
    >> the table and make a unique key of it. If you must, build relationships

    > on
    >> this. There's no disadvantage to having the autonumber column be a
    >> unique
    >> index but not the primary key, but there are definite performance

    > advantages
    >> in having the natural key be the primary index.
    >>
    >> Please, think about it. Autonumber surrogate keys are not appropriate as

    > a
    >> primary key. If you're going to use this technique, do it right. You're
    >> going to need a unique natural key almost every time anyway, and

    > performance
    >> will benefit from this being the primary key.
    >>
    >> Tom Ellison
    >>
    >>
    >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> news:eOUSebDdGHA.1272@TK2MSFTNGP03.phx.gbl...
    >> > That's why an autonumber field is preferable. If this table has to
    >> > participate in a relationship with another table, with a multi-field
    >> > primary
    >> > key, you have to put ALL the fields as a foriegn key in the related

    > table.
    >> > Considering that the fields are all text and an autonumber field is a

    > long
    >> > integer, the advantages of an autonumber primary key in terms of
    >> > performance
    >> > and ease-of-use are overwhelming.
    >> >
    >> > --
    >> > --Roger Carlson
    >> > MS Access MVP
    >> > Access Database Samples: www.rogersaccesslibrary.com
    >> > Want answers to your Access questions in your Email?
    >> > Free subscription:
    >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >
    >> >
    >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    >> > news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    >> >> You have confused me. I thought that a primary key could only be ONE
    >> > field
    >> >> in a table. However a little testing in Access showed me that I could
    >> >> set
    >> > up
    >> >> four fields (lot, oper, shift, machine) and define them all as the
    >> >> primary
    >> >> key (joined primary key??). Three of them share a relationship with

    > the
    >> >> tables that describe operator shift and machine and therefore the

    > values
    >> > are
    >> >> limited. AND access will not let me duplicate a lot operator shift
    >> > machine
    >> >> so the whole lot number is not repeated. So buisness practice is
    >> > enforced.
    >> >>
    >> >> But then I run into trouble. How do I use this joined primary key as

    > the
    >> >> forien key in another table. Do I need to set up all four fileds in

    > the
    >> >> forgien table as well? Or is their a way to use a field that is a
    >> >> concatination of the four fields in the main table?
    >> >>
    >> >> "Tom Ellison" wrote:
    >> >>
    >> >> > Dear Roger:
    >> >> >
    >> >> > OK.
    >> >> >
    >> >> > But having a multi-field primary key is not "stuck". On the

    > contrary,
    >> > you
    >> >> > are "stuck" with an additional column of data AND an index on it.

    > What
    >> > it
    >> >> > hurts is insert performance and disk space. This is a trivial
    >> > difference
    >> >> > for a small number or rows in the table.
    >> >> >
    >> >> > Almost none of the tables I create have autonumber columns. I used

    > to
    >> >> > program that way, but after extensive testing, I do not find them
    >> > useful.
    >> >> > I've heard of and tested the differences extensively. For a
    >> >> > database
    >> >> > of
    >> > no
    >> >> > more the 2 gigabytes, they serve no real purpose.
    >> >> >
    >> >> > The statement, "You should have an autonumber primary key for that
    >> > table" is
    >> >> > the problem. Why?
    >> >> >
    >> >> > I know most of the MVPs from previous summits except for the "new

    > crop"
    >> > and
    >> >> > I'm looking forward to meeting them, too. Till then.
    >> >> >
    >> >> > Tom Ellison
    >> >> >
    >> >> >
    >> >> > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> >> > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    >> >> > > In this case, it may well be unnecessary.
    >> >> > >
    >> >> > > The only time it would be necessary (to my mind) is if the table

    > were
    >> >> > > someday linked with to other table at some time. My experience is
    >> > that no
    >> >> > > matter what people say at the time, things change. To be stuck

    > with
    >> >> > > a
    >> >> > > multi-field primary key and have to join on multiple fields is, I
    >> > believe,
    >> >> > > well worth the time to set up an autonumber primary key ahead of
    >> >> > > time.
    >> > If
    >> >> > > it's never used, it's not really hurting anything. If it is
    >> >> > > needed
    >> >> > > in
    >> > the
    >> >> > > future, you'll already have it.
    >> >> > >
    >> >> > > Natural vs Surrogate keys is a religious issue in some circles,
    >> >> > > and

    > I
    >> >> > > won't
    >> >> > > get into that here. I'll just say that nearly EVERY table I
    >> >> > > create
    >> > (aside
    >> >> > > from "linking" tables) has an autonumber primary key. I think
    >> >> > > most
    >> >> > > of
    >> > the
    >> >> > > MVPs would agree. If you don't want to, I'm fine with that.
    >> >> > >
    >> >> > > --
    >> >> > > --Roger Carlson
    >> >> > > MS Access MVP
    >> >> > > Access Database Samples: www.rogersaccesslibrary.com
    >> >> > > Want answers to your Access questions in your Email?
    >> >> > > Free subscription:
    >> >> > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >> > >
    >> >> > >
    >> >> > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >> >> > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    >> >> > >> Dear Roger:
    >> >> > >>
    >> >> > >> Any particular reason why an autonumber primary key is so

    > important.
    >> >> > > There
    >> >> > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let

    > that
    >> > be
    >> >> > >> the
    >> >> > >> PK.
    >> >> > >>
    >> >> > >> I have built multiple databases without autonumber type

    > identities.
    >> > They
    >> >> > >> are almost always unnecessary. In a database the scale which is
    >> > possible
    >> >> > >> for Access Jet, this has never been a problem.
    >> >> > >>
    >> >> > >> Tom Elliosn
    >> >> > >>
    >> >> > >>
    >> >> > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in

    > message
    >> >> > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    >> >> > >> > Your original idea was correct. You should have an autonumber
    >> > primary
    >> >> > > key
    >> >> > >> > for that table. In addition, you should not store the
    >> >> > >> > concatenated
    >> >> > > value
    >> >> > >> > for LOT. Instead, you should recreate it on the fly in
    >> >> > >> > queries,
    >> >> > > reports,
    >> >> > >> > and forms as needed. Then create a Unique Index on the Date,
    >> >> > >> > OPER,
    >> >> > > SHIFT,
    >> >> > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    >> >> > >> >
    >> >> > >> > I don't know what to tell you about the grumbling. If the
    >> > operators
    >> >> > >> > are
    >> >> > >> > themselves entering data, perhaps you could have a login screen
    >> > which
    >> >> > >> > would
    >> >> > >> > accept OPER, SHIFT, and MACHINE and set them as default values.
    >> >> > >> >
    >> >> > >> >
    >> >> > >> > --
    >> >> > >> > --Roger Carlson
    >> >> > >> > MS Access MVP
    >> >> > >> > Access Database Samples: www.rogersaccesslibrary.com
    >> >> > >> > Want answers to your Access questions in your Email?
    >> >> > >> > Free subscription:
    >> >> > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >> > >> >
    >> >> > >> >
    >> >> > >> >
    >> >> > >> >
    >> >> > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    >> >> > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    >> >> > >> >> I have a table set up this way.
    >> >> > >> >>
    >> >> > >> >> *LOTS*
    >> >> > >> >> LOT - Text Field and Primary key
    >> >> > >> >> GRADE - Text Field
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >>
    >> >> > >> >> Our LOT identifiers are unique and are a concatination of the

    > day
    >> > of
    >> >> > > the
    >> >> > >> >> year, 1-365), two digit year, single letter signifiying lot

    > order
    >> > A,
    >> >> > >> >> B,
    >> >> > > C
    >> >> > >> >> used for production, two digit code to signify operator,
    >> >> > >> >> single
    >> > digit
    >> >> > > to
    >> >> > >> >> signify shift, and single character to signify machine.
    >> >> > >> >>
    >> >> > >> >> Originaly I have the table set up as
    >> >> > >> >> *LOTS*
    >> >> > >> >> LOT - Text Field (only day, year part, and order) and Primary

    > key
    >> >> > >> >> OPER - Text Field
    >> >> > >> >> SHIFT - Text Field
    >> >> > >> >> MACHINE - Text Field
    >> >> > >> >> GRADE - Text Field
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >>
    >> >> > >> >>
    >> >> > >> >> I also had tables for OPER, SHIFT and Machine
    >> >> > >> >>
    >> >> > >> >> *OPER ID*
    >> >> > >> >> OPER - Text Field and primary Key
    >> >> > >> >> First Name - Text
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >>
    >> >> > >> >> *SHIFT*
    >> >> > >> >> SHIFT - Text Field and primary Key
    >> >> > >> >> Start time - date/time
    >> >> > >> >> end time - date/time
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >>
    >> >> > >> >> *MACHINE*
    >> >> > >> >> MACH - Text Field primary key
    >> >> > >> >> MACHINE DETAILS - Memo
    >> >> > >> >> ...
    >> >> > >> >> ...
    >> >> > >> >>
    >> >> > >> >>
    >> >> > >> >> These tables were linked to limit the data that could be

    > entered
    >> > on
    >> >> > >> >> the
    >> >> > >> > main
    >> >> > >> >> table to real operators, shifts and machines.
    >> >> > >> >> This worked great UNTIL I realized that the LOT could be

    > repeated
    >> > if
    >> >> > > the
    >> >> > >> >> same operator produced material on more that one machine at
    >> >> > >> >> the
    >> > same
    >> >> > >> >> time.
    >> >> > >> >> This is very rare but does happen.
    >> >> > >> >>
    >> >> > >> >> Therfore I concated all the fileds together into the new LOT.

    > To
    >> >> > >> >> validate
    >> >> > >> >> the LOT data entry some code is used to pull the LOT apart and
    >> > compare
    >> >> > >> > each
    >> >> > >> >> part with the corisponding table of allowable entries.
    >> >> > >> >>
    >> >> > >> >> When I run reports or querries that use the information in

    > these
    >> >> > > orphaned
    >> >> > >> >> tables I also must write code to pull apart the LOT and get
    >> >> > >> >> the
    >> >> > >> >> correct
    >> >> > >> > part.
    >> >> > >> >>
    >> >> > >> >> My question is was their a better way to solve my original

    > issue?
    >> > I
    >> >> > >> > thought
    >> >> > >> >> about setting a new primary key in the original table that was

    > an
    >> > auto
    >> >> > >> >> number. HOWEVER, the users af the data base were already
    >> > grumbling
    >> >> > > about
    >> >> > >> >> having to enter each part ofhte LOT seperatly. So combining
    >> >> > >> >> it
    >> > made
    >> >> > > some
    >> >> > >> >> sense make them happy. But I fear I may have set myself up
    >> >> > >> >> for
    >> > bigger
    >> >> > >> > issues
    >> >> > >> >> down the road.
    >> >> > >> >>
    >> >> > >> >> Any other ideas as to what i should have done?
    >> >> > >> >>
    >> >> > >> >
    >> >> > >> >
    >> >> > >>
    >> >> > >>
    >> >> > >
    >> >> > >
    >> >> >
    >> >> >
    >> >> >
    >> >
    >> >

    >>
    >>

    >
    >
     
  18. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    I'm sorry, but your assertion that it is *almost always* preferable ("simply
    true almost all the time") to store records in the order of their natural
    key is simply wrong. As with most things in database design, how you use
    the data determines the correct method. That's why SQL Server gives you a
    choice on which index to create as clustered.

    It is true that if you only want to retrieve records from a single table,
    the yes, storing them in natural key order is more efficient. However, if
    your table is related to another table, and you typically Join these table
    to retrieve records, then natural key order is *not* more efficient. In
    this case, it is more efficient to store the records in the order of the
    surrogate key, that is, the field on which the Join will be made.

    Most of my databases are heavily normalized and most tables participate in
    relationships. Joins are much more processor intensive than retieval of
    records from single tables, so I choose, in most cases, to make the
    autonumber field the primary key and create a unique index on the natural
    key. In these cases, it is the most efficient design.

    As I said, however, I *do* at times create compound primary keys. That is
    in the case of a intersection or "linking" table that is used to resolve a
    Many-to-Many relationship into two One-to-Many relationships. In this case,
    it *does* make sense to store the values in the natural key order, because
    these will be the fields on which the Joins will be created. If this
    intersection table will participate in a join of its own, I will add an
    autonumber field. Sometimes I make it the Primary Key, sometimes I simply
    make it a unique index. It all depends on which Join I see as more
    important, that is, which will be used more.

    By the way, you misinterpreted my statement. As you correctly quoted, I
    said:
    > "You should have an autonumber primary key for that table."


    Notice I said "that" table. I did not:
    > "present it as a rule that 'should' be performed at all times"


    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:OuOtzITdGHA.1272@TK2MSFTNGP03.phx.gbl...
    > Dear Roger:
    >
    > It was your statement:
    >
    > "You should have an autonumber primary key for that table."
    >
    > This is your preferred method, but you present it as a rule that "should"

    be
    > performed at all times. It is not I who have confused his preference with
    > "gospel." And that, from the beginning, was my objection.
    >
    > The point is, you're permitted only one primary key or clustered index.

    You
    > should not waste it on a surrogate key, which is virtually never the order
    > you would want to show the rows in the table. That is something that is
    > simply true almost all the time. Your statement that the autonumber
    > "should" be the primary key is simply wrong in most cases.
    >
    > Tom Ellison
    >
    >
    > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > news:%23ZOaa5PdGHA.4720@TK2MSFTNGP03.phx.gbl...
    > > As I said, I'm not interested in debating this here.
    > >
    > > I understand that Access stores the primary key as a clustered index. I
    > > also understand that relationships do not have to be created on a

    Primary
    > > Key, a Unique Index will do. I have, in fact, created tables exactly as
    > > you
    > > describe. However, it is not my preferred method and it is not "wrong".
    > >
    > > I have no problem with you creating natural primary keys, if that is

    your
    > > preference, but it IS a preference. Please do not confuse your

    preference
    > > with gospel.
    > >
    > > --
    > > --Roger Carlson
    > > MS Access MVP
    > > www.rogersaccesslibrary.com
    > >
    > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > > news:eq%23sh7JdGHA.4148@TK2MSFTNGP05.phx.gbl...
    > >> Dear Roger:
    > >>
    > >> An autonumber column is not perferable. And, if you do use one,

    please,
    > >> please, do not make it the primary key. Make it a separate, unique

    key,
    > > but
    > >> not the primary key.
    > >>
    > >> The primary key is used to put the physical rows of the table in order

    > > when
    > >> you do a compact and repair. Now, which has more advantage: to put

    the
    > >> rows of the table in the order of a unique natural key, or to put the
    > >> rows
    > >> in the order of the autonumber?
    > >>
    > >> I propose there is NO advantage in putting the rows of the table in
    > >> autonumber order. This order is almost never wanted.
    > >>
    > >> When you perform a report or form, you will usually be looking at the
    > >> rows
    > >> of the table in the natural key order. If the rows in the table sre in

    > > this
    > >> physical order, you will experience increased performance by making the
    > >> primary key the natural key. You could still have an autonumber column
    > >> in
    > >> the table and make a unique key of it. If you must, build

    relationships
    > > on
    > >> this. There's no disadvantage to having the autonumber column be a
    > >> unique
    > >> index but not the primary key, but there are definite performance

    > > advantages
    > >> in having the natural key be the primary index.
    > >>
    > >> Please, think about it. Autonumber surrogate keys are not appropriate

    as
    > > a
    > >> primary key. If you're going to use this technique, do it right.

    You're
    > >> going to need a unique natural key almost every time anyway, and

    > > performance
    > >> will benefit from this being the primary key.
    > >>
    > >> Tom Ellison
    > >>
    > >>
    > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    > >> news:eOUSebDdGHA.1272@TK2MSFTNGP03.phx.gbl...
    > >> > That's why an autonumber field is preferable. If this table has to
    > >> > participate in a relationship with another table, with a multi-field
    > >> > primary
    > >> > key, you have to put ALL the fields as a foriegn key in the related

    > > table.
    > >> > Considering that the fields are all text and an autonumber field is a

    > > long
    > >> > integer, the advantages of an autonumber primary key in terms of
    > >> > performance
    > >> > and ease-of-use are overwhelming.
    > >> >
    > >> > --
    > >> > --Roger Carlson
    > >> > MS Access MVP
    > >> > Access Database Samples: www.rogersaccesslibrary.com
    > >> > Want answers to your Access questions in your Email?
    > >> > Free subscription:
    > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> >
    > >> >
    > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > >> > news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    > >> >> You have confused me. I thought that a primary key could only be

    ONE
    > >> > field
    > >> >> in a table. However a little testing in Access showed me that I

    could
    > >> >> set
    > >> > up
    > >> >> four fields (lot, oper, shift, machine) and define them all as the
    > >> >> primary
    > >> >> key (joined primary key??). Three of them share a relationship with

    > > the
    > >> >> tables that describe operator shift and machine and therefore the

    > > values
    > >> > are
    > >> >> limited. AND access will not let me duplicate a lot operator shift
    > >> > machine
    > >> >> so the whole lot number is not repeated. So buisness practice is
    > >> > enforced.
    > >> >>
    > >> >> But then I run into trouble. How do I use this joined primary key

    as
    > > the
    > >> >> forien key in another table. Do I need to set up all four fileds in

    > > the
    > >> >> forgien table as well? Or is their a way to use a field that is a
    > >> >> concatination of the four fields in the main table?
    > >> >>
    > >> >> "Tom Ellison" wrote:
    > >> >>
    > >> >> > Dear Roger:
    > >> >> >
    > >> >> > OK.
    > >> >> >
    > >> >> > But having a multi-field primary key is not "stuck". On the

    > > contrary,
    > >> > you
    > >> >> > are "stuck" with an additional column of data AND an index on it.

    > > What
    > >> > it
    > >> >> > hurts is insert performance and disk space. This is a trivial
    > >> > difference
    > >> >> > for a small number or rows in the table.
    > >> >> >
    > >> >> > Almost none of the tables I create have autonumber columns. I

    used
    > > to
    > >> >> > program that way, but after extensive testing, I do not find them
    > >> > useful.
    > >> >> > I've heard of and tested the differences extensively. For a
    > >> >> > database
    > >> >> > of
    > >> > no
    > >> >> > more the 2 gigabytes, they serve no real purpose.
    > >> >> >
    > >> >> > The statement, "You should have an autonumber primary key for that
    > >> > table" is
    > >> >> > the problem. Why?
    > >> >> >
    > >> >> > I know most of the MVPs from previous summits except for the "new

    > > crop"
    > >> > and
    > >> >> > I'm looking forward to meeting them, too. Till then.
    > >> >> >
    > >> >> > Tom Ellison
    > >> >> >
    > >> >> >
    > >> >> > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in

    message
    > >> >> > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    > >> >> > > In this case, it may well be unnecessary.
    > >> >> > >
    > >> >> > > The only time it would be necessary (to my mind) is if the table

    > > were
    > >> >> > > someday linked with to other table at some time. My experience

    is
    > >> > that no
    > >> >> > > matter what people say at the time, things change. To be stuck

    > > with
    > >> >> > > a
    > >> >> > > multi-field primary key and have to join on multiple fields is,

    I
    > >> > believe,
    > >> >> > > well worth the time to set up an autonumber primary key ahead of
    > >> >> > > time.
    > >> > If
    > >> >> > > it's never used, it's not really hurting anything. If it is
    > >> >> > > needed
    > >> >> > > in
    > >> > the
    > >> >> > > future, you'll already have it.
    > >> >> > >
    > >> >> > > Natural vs Surrogate keys is a religious issue in some circles,
    > >> >> > > and

    > > I
    > >> >> > > won't
    > >> >> > > get into that here. I'll just say that nearly EVERY table I
    > >> >> > > create
    > >> > (aside
    > >> >> > > from "linking" tables) has an autonumber primary key. I think
    > >> >> > > most
    > >> >> > > of
    > >> > the
    > >> >> > > MVPs would agree. If you don't want to, I'm fine with that.
    > >> >> > >
    > >> >> > > --
    > >> >> > > --Roger Carlson
    > >> >> > > MS Access MVP
    > >> >> > > Access Database Samples: www.rogersaccesslibrary.com
    > >> >> > > Want answers to your Access questions in your Email?
    > >> >> > > Free subscription:
    > >> >> > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> >> > >
    > >> >> > >
    > >> >> > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > >> >> > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    > >> >> > >> Dear Roger:
    > >> >> > >>
    > >> >> > >> Any particular reason why an autonumber primary key is so

    > > important.
    > >> >> > > There
    > >> >> > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let

    > > that
    > >> > be
    > >> >> > >> the
    > >> >> > >> PK.
    > >> >> > >>
    > >> >> > >> I have built multiple databases without autonumber type

    > > identities.
    > >> > They
    > >> >> > >> are almost always unnecessary. In a database the scale which

    is
    > >> > possible
    > >> >> > >> for Access Jet, this has never been a problem.
    > >> >> > >>
    > >> >> > >> Tom Elliosn
    > >> >> > >>
    > >> >> > >>
    > >> >> > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in

    > > message
    > >> >> > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    > >> >> > >> > Your original idea was correct. You should have an

    autonumber
    > >> > primary
    > >> >> > > key
    > >> >> > >> > for that table. In addition, you should not store the
    > >> >> > >> > concatenated
    > >> >> > > value
    > >> >> > >> > for LOT. Instead, you should recreate it on the fly in
    > >> >> > >> > queries,
    > >> >> > > reports,
    > >> >> > >> > and forms as needed. Then create a Unique Index on the Date,
    > >> >> > >> > OPER,
    > >> >> > > SHIFT,
    > >> >> > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    > >> >> > >> >
    > >> >> > >> > I don't know what to tell you about the grumbling. If the
    > >> > operators
    > >> >> > >> > are
    > >> >> > >> > themselves entering data, perhaps you could have a login

    screen
    > >> > which
    > >> >> > >> > would
    > >> >> > >> > accept OPER, SHIFT, and MACHINE and set them as default

    values.
    > >> >> > >> >
    > >> >> > >> >
    > >> >> > >> > --
    > >> >> > >> > --Roger Carlson
    > >> >> > >> > MS Access MVP
    > >> >> > >> > Access Database Samples: www.rogersaccesslibrary.com
    > >> >> > >> > Want answers to your Access questions in your Email?
    > >> >> > >> > Free subscription:
    > >> >> > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    > >> >> > >> >
    > >> >> > >> >
    > >> >> > >> >
    > >> >> > >> >
    > >> >> > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    > >> >> > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    > >> >> > >> >> I have a table set up this way.
    > >> >> > >> >>
    > >> >> > >> >> *LOTS*
    > >> >> > >> >> LOT - Text Field and Primary key
    > >> >> > >> >> GRADE - Text Field
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >>
    > >> >> > >> >> Our LOT identifiers are unique and are a concatination of

    the
    > > day
    > >> > of
    > >> >> > > the
    > >> >> > >> >> year, 1-365), two digit year, single letter signifiying lot

    > > order
    > >> > A,
    > >> >> > >> >> B,
    > >> >> > > C
    > >> >> > >> >> used for production, two digit code to signify operator,
    > >> >> > >> >> single
    > >> > digit
    > >> >> > > to
    > >> >> > >> >> signify shift, and single character to signify machine.
    > >> >> > >> >>
    > >> >> > >> >> Originaly I have the table set up as
    > >> >> > >> >> *LOTS*
    > >> >> > >> >> LOT - Text Field (only day, year part, and order) and

    Primary
    > > key
    > >> >> > >> >> OPER - Text Field
    > >> >> > >> >> SHIFT - Text Field
    > >> >> > >> >> MACHINE - Text Field
    > >> >> > >> >> GRADE - Text Field
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >>
    > >> >> > >> >>
    > >> >> > >> >> I also had tables for OPER, SHIFT and Machine
    > >> >> > >> >>
    > >> >> > >> >> *OPER ID*
    > >> >> > >> >> OPER - Text Field and primary Key
    > >> >> > >> >> First Name - Text
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >>
    > >> >> > >> >> *SHIFT*
    > >> >> > >> >> SHIFT - Text Field and primary Key
    > >> >> > >> >> Start time - date/time
    > >> >> > >> >> end time - date/time
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >>
    > >> >> > >> >> *MACHINE*
    > >> >> > >> >> MACH - Text Field primary key
    > >> >> > >> >> MACHINE DETAILS - Memo
    > >> >> > >> >> ...
    > >> >> > >> >> ...
    > >> >> > >> >>
    > >> >> > >> >>
    > >> >> > >> >> These tables were linked to limit the data that could be

    > > entered
    > >> > on
    > >> >> > >> >> the
    > >> >> > >> > main
    > >> >> > >> >> table to real operators, shifts and machines.
    > >> >> > >> >> This worked great UNTIL I realized that the LOT could be

    > > repeated
    > >> > if
    > >> >> > > the
    > >> >> > >> >> same operator produced material on more that one machine at
    > >> >> > >> >> the
    > >> > same
    > >> >> > >> >> time.
    > >> >> > >> >> This is very rare but does happen.
    > >> >> > >> >>
    > >> >> > >> >> Therfore I concated all the fileds together into the new

    LOT.
    > > To
    > >> >> > >> >> validate
    > >> >> > >> >> the LOT data entry some code is used to pull the LOT apart

    and
    > >> > compare
    > >> >> > >> > each
    > >> >> > >> >> part with the corisponding table of allowable entries.
    > >> >> > >> >>
    > >> >> > >> >> When I run reports or querries that use the information in

    > > these
    > >> >> > > orphaned
    > >> >> > >> >> tables I also must write code to pull apart the LOT and get
    > >> >> > >> >> the
    > >> >> > >> >> correct
    > >> >> > >> > part.
    > >> >> > >> >>
    > >> >> > >> >> My question is was their a better way to solve my original

    > > issue?
    > >> > I
    > >> >> > >> > thought
    > >> >> > >> >> about setting a new primary key in the original table that

    was
    > > an
    > >> > auto
    > >> >> > >> >> number. HOWEVER, the users af the data base were already
    > >> > grumbling
    > >> >> > > about
    > >> >> > >> >> having to enter each part ofhte LOT seperatly. So combining
    > >> >> > >> >> it
    > >> > made
    > >> >> > > some
    > >> >> > >> >> sense make them happy. But I fear I may have set myself up
    > >> >> > >> >> for
    > >> > bigger
    > >> >> > >> > issues
    > >> >> > >> >> down the road.
    > >> >> > >> >>
    > >> >> > >> >> Any other ideas as to what i should have done?
    > >> >> > >> >>
    > >> >> > >> >
    > >> >> > >> >
    > >> >> > >>
    > >> >> > >>
    > >> >> > >
    > >> >> > >
    > >> >> >
    > >> >> >
    > >> >> >
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  19. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Roger:

    Your responses have led me to begin an intensive study and demonstration
    project to prove whether what you say is true. I seriously doubt it is.

    I've given a lot of consideration to the concept of whether a
    clustered/primary index on a surrogate key would be efficient, and why that
    could be. On the one hand, it would keep the rows of the dependent table
    with the same key together. A clustered/PK index on the natural key does
    this also, and more. Having your data in an organized physical sequence is
    obviously advantageous. Consider that the natural key order is considerably
    more organized. For example, for orders, the surrogate key order would keep
    all the detail of each order together. The natural key would (properly
    designed) keep all the detail of all the orders for a given customer
    together, and in date order, ready for the screen or a report.

    Well, I've been looking for a topic to author. This will be it. I'm
    expecting it to take a month or more, but it's already started.

    Tom Ellison


    "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    news:%23CFd4BcdGHA.3484@TK2MSFTNGP04.phx.gbl...
    > I'm sorry, but your assertion that it is *almost always* preferable
    > ("simply
    > true almost all the time") to store records in the order of their natural
    > key is simply wrong. As with most things in database design, how you use
    > the data determines the correct method. That's why SQL Server gives you a
    > choice on which index to create as clustered.
    >
    > It is true that if you only want to retrieve records from a single table,
    > the yes, storing them in natural key order is more efficient. However, if
    > your table is related to another table, and you typically Join these table
    > to retrieve records, then natural key order is *not* more efficient. In
    > this case, it is more efficient to store the records in the order of the
    > surrogate key, that is, the field on which the Join will be made.
    >
    > Most of my databases are heavily normalized and most tables participate in
    > relationships. Joins are much more processor intensive than retieval of
    > records from single tables, so I choose, in most cases, to make the
    > autonumber field the primary key and create a unique index on the natural
    > key. In these cases, it is the most efficient design.
    >
    > As I said, however, I *do* at times create compound primary keys. That is
    > in the case of a intersection or "linking" table that is used to resolve a
    > Many-to-Many relationship into two One-to-Many relationships. In this
    > case,
    > it *does* make sense to store the values in the natural key order, because
    > these will be the fields on which the Joins will be created. If this
    > intersection table will participate in a join of its own, I will add an
    > autonumber field. Sometimes I make it the Primary Key, sometimes I simply
    > make it a unique index. It all depends on which Join I see as more
    > important, that is, which will be used more.
    >
    > By the way, you misinterpreted my statement. As you correctly quoted, I
    > said:
    >> "You should have an autonumber primary key for that table."

    >
    > Notice I said "that" table. I did not:
    >> "present it as a rule that 'should' be performed at all times"

    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    >
    >
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:OuOtzITdGHA.1272@TK2MSFTNGP03.phx.gbl...
    >> Dear Roger:
    >>
    >> It was your statement:
    >>
    >> "You should have an autonumber primary key for that table."
    >>
    >> This is your preferred method, but you present it as a rule that "should"

    > be
    >> performed at all times. It is not I who have confused his preference
    >> with
    >> "gospel." And that, from the beginning, was my objection.
    >>
    >> The point is, you're permitted only one primary key or clustered index.

    > You
    >> should not waste it on a surrogate key, which is virtually never the
    >> order
    >> you would want to show the rows in the table. That is something that is
    >> simply true almost all the time. Your statement that the autonumber
    >> "should" be the primary key is simply wrong in most cases.
    >>
    >> Tom Ellison
    >>
    >>
    >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> news:%23ZOaa5PdGHA.4720@TK2MSFTNGP03.phx.gbl...
    >> > As I said, I'm not interested in debating this here.
    >> >
    >> > I understand that Access stores the primary key as a clustered index.
    >> > I
    >> > also understand that relationships do not have to be created on a

    > Primary
    >> > Key, a Unique Index will do. I have, in fact, created tables exactly
    >> > as
    >> > you
    >> > describe. However, it is not my preferred method and it is not
    >> > "wrong".
    >> >
    >> > I have no problem with you creating natural primary keys, if that is

    > your
    >> > preference, but it IS a preference. Please do not confuse your

    > preference
    >> > with gospel.
    >> >
    >> > --
    >> > --Roger Carlson
    >> > MS Access MVP
    >> > www.rogersaccesslibrary.com
    >> >
    >> > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >> > news:eq%23sh7JdGHA.4148@TK2MSFTNGP05.phx.gbl...
    >> >> Dear Roger:
    >> >>
    >> >> An autonumber column is not perferable. And, if you do use one,

    > please,
    >> >> please, do not make it the primary key. Make it a separate, unique

    > key,
    >> > but
    >> >> not the primary key.
    >> >>
    >> >> The primary key is used to put the physical rows of the table in order
    >> > when
    >> >> you do a compact and repair. Now, which has more advantage: to put

    > the
    >> >> rows of the table in the order of a unique natural key, or to put the
    >> >> rows
    >> >> in the order of the autonumber?
    >> >>
    >> >> I propose there is NO advantage in putting the rows of the table in
    >> >> autonumber order. This order is almost never wanted.
    >> >>
    >> >> When you perform a report or form, you will usually be looking at the
    >> >> rows
    >> >> of the table in the natural key order. If the rows in the table sre
    >> >> in
    >> > this
    >> >> physical order, you will experience increased performance by making
    >> >> the
    >> >> primary key the natural key. You could still have an autonumber
    >> >> column
    >> >> in
    >> >> the table and make a unique key of it. If you must, build

    > relationships
    >> > on
    >> >> this. There's no disadvantage to having the autonumber column be a
    >> >> unique
    >> >> index but not the primary key, but there are definite performance
    >> > advantages
    >> >> in having the natural key be the primary index.
    >> >>
    >> >> Please, think about it. Autonumber surrogate keys are not appropriate

    > as
    >> > a
    >> >> primary key. If you're going to use this technique, do it right.

    > You're
    >> >> going to need a unique natural key almost every time anyway, and
    >> > performance
    >> >> will benefit from this being the primary key.
    >> >>
    >> >> Tom Ellison
    >> >>
    >> >>
    >> >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in message
    >> >> news:eOUSebDdGHA.1272@TK2MSFTNGP03.phx.gbl...
    >> >> > That's why an autonumber field is preferable. If this table has to
    >> >> > participate in a relationship with another table, with a multi-field
    >> >> > primary
    >> >> > key, you have to put ALL the fields as a foriegn key in the related
    >> > table.
    >> >> > Considering that the fields are all text and an autonumber field is
    >> >> > a
    >> > long
    >> >> > integer, the advantages of an autonumber primary key in terms of
    >> >> > performance
    >> >> > and ease-of-use are overwhelming.
    >> >> >
    >> >> > --
    >> >> > --Roger Carlson
    >> >> > MS Access MVP
    >> >> > Access Database Samples: www.rogersaccesslibrary.com
    >> >> > Want answers to your Access questions in your Email?
    >> >> > Free subscription:
    >> >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >> >
    >> >> >
    >> >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    >> >> > news:AA84A7B9-3999-4528-B1B2-30E96EB840B3@microsoft.com...
    >> >> >> You have confused me. I thought that a primary key could only be

    > ONE
    >> >> > field
    >> >> >> in a table. However a little testing in Access showed me that I

    > could
    >> >> >> set
    >> >> > up
    >> >> >> four fields (lot, oper, shift, machine) and define them all as the
    >> >> >> primary
    >> >> >> key (joined primary key??). Three of them share a relationship
    >> >> >> with
    >> > the
    >> >> >> tables that describe operator shift and machine and therefore the
    >> > values
    >> >> > are
    >> >> >> limited. AND access will not let me duplicate a lot operator shift
    >> >> > machine
    >> >> >> so the whole lot number is not repeated. So buisness practice is
    >> >> > enforced.
    >> >> >>
    >> >> >> But then I run into trouble. How do I use this joined primary key

    > as
    >> > the
    >> >> >> forien key in another table. Do I need to set up all four fileds
    >> >> >> in
    >> > the
    >> >> >> forgien table as well? Or is their a way to use a field that is a
    >> >> >> concatination of the four fields in the main table?
    >> >> >>
    >> >> >> "Tom Ellison" wrote:
    >> >> >>
    >> >> >> > Dear Roger:
    >> >> >> >
    >> >> >> > OK.
    >> >> >> >
    >> >> >> > But having a multi-field primary key is not "stuck". On the
    >> > contrary,
    >> >> > you
    >> >> >> > are "stuck" with an additional column of data AND an index on it.
    >> > What
    >> >> > it
    >> >> >> > hurts is insert performance and disk space. This is a trivial
    >> >> > difference
    >> >> >> > for a small number or rows in the table.
    >> >> >> >
    >> >> >> > Almost none of the tables I create have autonumber columns. I

    > used
    >> > to
    >> >> >> > program that way, but after extensive testing, I do not find them
    >> >> > useful.
    >> >> >> > I've heard of and tested the differences extensively. For a
    >> >> >> > database
    >> >> >> > of
    >> >> > no
    >> >> >> > more the 2 gigabytes, they serve no real purpose.
    >> >> >> >
    >> >> >> > The statement, "You should have an autonumber primary key for
    >> >> >> > that
    >> >> > table" is
    >> >> >> > the problem. Why?
    >> >> >> >
    >> >> >> > I know most of the MVPs from previous summits except for the "new
    >> > crop"
    >> >> > and
    >> >> >> > I'm looking forward to meeting them, too. Till then.
    >> >> >> >
    >> >> >> > Tom Ellison
    >> >> >> >
    >> >> >> >
    >> >> >> > "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in

    > message
    >> >> >> > news:uvLKAK6cGHA.3632@TK2MSFTNGP02.phx.gbl...
    >> >> >> > > In this case, it may well be unnecessary.
    >> >> >> > >
    >> >> >> > > The only time it would be necessary (to my mind) is if the
    >> >> >> > > table
    >> > were
    >> >> >> > > someday linked with to other table at some time. My experience

    > is
    >> >> > that no
    >> >> >> > > matter what people say at the time, things change. To be stuck
    >> > with
    >> >> >> > > a
    >> >> >> > > multi-field primary key and have to join on multiple fields is,

    > I
    >> >> > believe,
    >> >> >> > > well worth the time to set up an autonumber primary key ahead
    >> >> >> > > of
    >> >> >> > > time.
    >> >> > If
    >> >> >> > > it's never used, it's not really hurting anything. If it is
    >> >> >> > > needed
    >> >> >> > > in
    >> >> > the
    >> >> >> > > future, you'll already have it.
    >> >> >> > >
    >> >> >> > > Natural vs Surrogate keys is a religious issue in some circles,
    >> >> >> > > and
    >> > I
    >> >> >> > > won't
    >> >> >> > > get into that here. I'll just say that nearly EVERY table I
    >> >> >> > > create
    >> >> > (aside
    >> >> >> > > from "linking" tables) has an autonumber primary key. I think
    >> >> >> > > most
    >> >> >> > > of
    >> >> > the
    >> >> >> > > MVPs would agree. If you don't want to, I'm fine with that.
    >> >> >> > >
    >> >> >> > > --
    >> >> >> > > --Roger Carlson
    >> >> >> > > MS Access MVP
    >> >> >> > > Access Database Samples: www.rogersaccesslibrary.com
    >> >> >> > > Want answers to your Access questions in your Email?
    >> >> >> > > Free subscription:
    >> >> >> > > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >> >> > >
    >> >> >> > >
    >> >> >> > > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >> >> >> > > news:%23dYfNE6cGHA.4912@TK2MSFTNGP05.phx.gbl...
    >> >> >> > >> Dear Roger:
    >> >> >> > >>
    >> >> >> > >> Any particular reason why an autonumber primary key is so
    >> > important.
    >> >> >> > > There
    >> >> >> > >> is a unique combination of Date, OPER, SHIFT and MACHINE. Let
    >> > that
    >> >> > be
    >> >> >> > >> the
    >> >> >> > >> PK.
    >> >> >> > >>
    >> >> >> > >> I have built multiple databases without autonumber type
    >> > identities.
    >> >> > They
    >> >> >> > >> are almost always unnecessary. In a database the scale which

    > is
    >> >> > possible
    >> >> >> > >> for Access Jet, this has never been a problem.
    >> >> >> > >>
    >> >> >> > >> Tom Elliosn
    >> >> >> > >>
    >> >> >> > >>
    >> >> >> > >> "Roger Carlson" <NO-Roger.J.Carlson-SPAM@gmail.com> wrote in
    >> > message
    >> >> >> > >> news:OX0Cg05cGHA.4108@TK2MSFTNGP03.phx.gbl...
    >> >> >> > >> > Your original idea was correct. You should have an

    > autonumber
    >> >> > primary
    >> >> >> > > key
    >> >> >> > >> > for that table. In addition, you should not store the
    >> >> >> > >> > concatenated
    >> >> >> > > value
    >> >> >> > >> > for LOT. Instead, you should recreate it on the fly in
    >> >> >> > >> > queries,
    >> >> >> > > reports,
    >> >> >> > >> > and forms as needed. Then create a Unique Index on the
    >> >> >> > >> > Date,
    >> >> >> > >> > OPER,
    >> >> >> > > SHIFT,
    >> >> >> > >> > and MACHINE. (You ARE capturing the Date, aren't you?)
    >> >> >> > >> >
    >> >> >> > >> > I don't know what to tell you about the grumbling. If the
    >> >> > operators
    >> >> >> > >> > are
    >> >> >> > >> > themselves entering data, perhaps you could have a login

    > screen
    >> >> > which
    >> >> >> > >> > would
    >> >> >> > >> > accept OPER, SHIFT, and MACHINE and set them as default

    > values.
    >> >> >> > >> >
    >> >> >> > >> >
    >> >> >> > >> > --
    >> >> >> > >> > --Roger Carlson
    >> >> >> > >> > MS Access MVP
    >> >> >> > >> > Access Database Samples: www.rogersaccesslibrary.com
    >> >> >> > >> > Want answers to your Access questions in your Email?
    >> >> >> > >> > Free subscription:
    >> >> >> > >> > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >> >> >> > >> >
    >> >> >> > >> >
    >> >> >> > >> >
    >> >> >> > >> >
    >> >> >> > >> > "ED007" <ED007@discussions.microsoft.com> wrote in message
    >> >> >> > >> > news:AA185CAC-3DCB-44B1-9DD3-B8D468DBD8A2@microsoft.com...
    >> >> >> > >> >> I have a table set up this way.
    >> >> >> > >> >>
    >> >> >> > >> >> *LOTS*
    >> >> >> > >> >> LOT - Text Field and Primary key
    >> >> >> > >> >> GRADE - Text Field
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >>
    >> >> >> > >> >> Our LOT identifiers are unique and are a concatination of

    > the
    >> > day
    >> >> > of
    >> >> >> > > the
    >> >> >> > >> >> year, 1-365), two digit year, single letter signifiying lot
    >> > order
    >> >> > A,
    >> >> >> > >> >> B,
    >> >> >> > > C
    >> >> >> > >> >> used for production, two digit code to signify operator,
    >> >> >> > >> >> single
    >> >> > digit
    >> >> >> > > to
    >> >> >> > >> >> signify shift, and single character to signify machine.
    >> >> >> > >> >>
    >> >> >> > >> >> Originaly I have the table set up as
    >> >> >> > >> >> *LOTS*
    >> >> >> > >> >> LOT - Text Field (only day, year part, and order) and

    > Primary
    >> > key
    >> >> >> > >> >> OPER - Text Field
    >> >> >> > >> >> SHIFT - Text Field
    >> >> >> > >> >> MACHINE - Text Field
    >> >> >> > >> >> GRADE - Text Field
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >>
    >> >> >> > >> >>
    >> >> >> > >> >> I also had tables for OPER, SHIFT and Machine
    >> >> >> > >> >>
    >> >> >> > >> >> *OPER ID*
    >> >> >> > >> >> OPER - Text Field and primary Key
    >> >> >> > >> >> First Name - Text
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >>
    >> >> >> > >> >> *SHIFT*
    >> >> >> > >> >> SHIFT - Text Field and primary Key
    >> >> >> > >> >> Start time - date/time
    >> >> >> > >> >> end time - date/time
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >>
    >> >> >> > >> >> *MACHINE*
    >> >> >> > >> >> MACH - Text Field primary key
    >> >> >> > >> >> MACHINE DETAILS - Memo
    >> >> >> > >> >> ...
    >> >> >> > >> >> ...
    >> >> >> > >> >>
    >> >> >> > >> >>
    >> >> >> > >> >> These tables were linked to limit the data that could be
    >> > entered
    >> >> > on
    >> >> >> > >> >> the
    >> >> >> > >> > main
    >> >> >> > >> >> table to real operators, shifts and machines.
    >> >> >> > >> >> This worked great UNTIL I realized that the LOT could be
    >> > repeated
    >> >> > if
    >> >> >> > > the
    >> >> >> > >> >> same operator produced material on more that one machine at
    >> >> >> > >> >> the
    >> >> > same
    >> >> >> > >> >> time.
    >> >> >> > >> >> This is very rare but does happen.
    >> >> >> > >> >>
    >> >> >> > >> >> Therfore I concated all the fileds together into the new

    > LOT.
    >> > To
    >> >> >> > >> >> validate
    >> >> >> > >> >> the LOT data entry some code is used to pull the LOT apart

    > and
    >> >> > compare
    >> >> >> > >> > each
    >> >> >> > >> >> part with the corisponding table of allowable entries.
    >> >> >> > >> >>
    >> >> >> > >> >> When I run reports or querries that use the information in
    >> > these
    >> >> >> > > orphaned
    >> >> >> > >> >> tables I also must write code to pull apart the LOT and get
    >> >> >> > >> >> the
    >> >> >> > >> >> correct
    >> >> >> > >> > part.
    >> >> >> > >> >>
    >> >> >> > >> >> My question is was their a better way to solve my original
    >> > issue?
    >> >> > I
    >> >> >> > >> > thought
    >> >> >> > >> >> about setting a new primary key in the original table that

    > was
    >> > an
    >> >> > auto
    >> >> >> > >> >> number. HOWEVER, the users af the data base were already
    >> >> > grumbling
    >> >> >> > > about
    >> >> >> > >> >> having to enter each part ofhte LOT seperatly. So
    >> >> >> > >> >> combining
    >> >> >> > >> >> it
    >> >> > made
    >> >> >> > > some
    >> >> >> > >> >> sense make them happy. But I fear I may have set myself up
    >> >> >> > >> >> for
    >> >> > bigger
    >> >> >> > >> > issues
    >> >> >> > >> >> down the road.
    >> >> >> > >> >>
    >> >> >> > >> >> Any other ideas as to what i should have done?
    >> >> >> > >> >>
    >> >> >> > >> >
    >> >> >> > >> >
    >> >> >> > >>
    >> >> >> > >>
    >> >> >> > >
    >> >> >> > >
    >> >> >> >
    >> >> >> >
    >> >> >> >
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     

Share This Page