Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2003
    Posts
    19

    Unanswered: Table Setup - new guy

    Hi Guys,

    I'm learning. I have some stuff set up that sortof works, but I think I might be burying myself - need guidance, possibly even a fresh start.

    Currently I have 5 tables.

    Table 1 - has a field with Unique Names which I set as the
    - Primary Key with no duplicates

    Other Tables
    -Fields for Unique Names (tried with and without setting as Primary Key)


    Relationships
    - Table 1.field with Unique Names points toward all other tables with a field with Unique Names.
    - Enforce Referencial Integrity
    - Cascade delete/update related fields

    Update Query
    Updates All Unique Names in other tables to reflect what I call the master table (Table1)


    Goal: All Unique Names entered into Table 1 (MASTER table) will be passed down to other tables


    Also: AND this is what really doesn't work
    I want the user to be able to select the Unique name from a combo box(it is set up). One problem. When I change the box, it will not allow me to change the primary key or delete it.

    Any suggestions are welcome. Main Problem is set up.

    Thank you in advance
    Steve

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whoa, Steve! I think you may need a quick primer on the rules of database normalization. (Do an internet search and you'll find lots of info). Table architecture is the foundation of a database application, and if it is not well though out you will be spending hundreds of hours down the line trying to code around its deficiencies.

    Does table1 have only a single field in it? Normally this sort of thing is only used as a lookup-list for drop-down boxes and such. If your other tables contain data related to your Unique Names, I'd bet some of it could be stored directly in the Unique Names table.

    What do you mean by "passed down to other tables"? Your cascade relationship will pass down changes automatically, but if you want to insert new records into the subtables every time a record is entered into the main table you will have to write code to do it. Code which, I suspect, would be compensating for not having your data table architecture normalized....

    blindman

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...also, DONT do what you are trying to do.

    set your Table 1 with an old-fashioned autonumber as primary key and the text field as indexed/unique.

    in your releated table(s), link to the text field (hide key field).

    now you can edit the text in Table 1 (as long as it stays unique) and it automatically reflects in the related tables with no code, no query, no hassle.

    izy

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    izyrider,
    If the text field is indexed unique and the subtable link to the text field, what is the point of the autonumber.

    Personally I agree with the use of an autonumber field as a primary key rather than using a table's natural key, but it is a matter of preference. Access will cascade updates to lower tables either way.

    blindman

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by blindman
    izyrider,
    If the text field is indexed unique and the subtable link to the text field, what is the point of the autonumber.

    Personally I agree with the use of an autonumber field as a primary key rather than using a table's natural key, but it is a matter of preference. Access will cascade updates to lower tables either way.

    blindman
    your supposed to link to the auto number not to the text field, this then allows you to modify the text value with out losing referential integrity there's also the fact that a number normally requires less HDD space to store the DB
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Wink

    That's what a thought you meant, but wasn't sure if it would be clear to Samandus. Link to the autonumber and display the text.

    blindman

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by blindman
    That's what a thought you meant, but wasn't sure if it would be clear to Samandus. Link to the autonumber and display the text.

    blindman
    err.. that was me pointing out what i thought izy meant, i can't read minds so i can't say if that is what they meant
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks m.timoney

    that's indeed what izy meant, he just typed it wrong 'cos he was thinking about a combo box at the time instead of thinking about a table.


    izy

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by izyrider
    thanks m.timoney

    that's indeed what izy meant, he just typed it wrong 'cos he was thinking about a combo box at the time instead of thinking about a table.


    izy
    i don't know why izy but every time i go to reffer to you i have to catch my self or i'd be reffering to you as a she, so just incase i don't catch myself at a future date i'd like to applogise now

    sorry

    (the best reason i can come up with is because izy sounds like lizzy)
    Last edited by m.timoney; 07-11-03 at 09:38.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    FIXED!

    to avoid any possible gender confusion i've added a photo of the real izy.
    full name: izy rider des lutins bienfaisants

    now there can be no doubt!

    izy

  11. #11
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    Re: Table Setup - new guy

    Originally posted by Samandus
    Hi Guys,

    I'm learning. I have some stuff set up that sortof works, but I think I might be burying myself - need guidance, possibly even a fresh start.

    Currently I have 5 tables.

    Table 1 - has a field with Unique Names which I set as the
    - Primary Key with no duplicates

    Other Tables
    -Fields for Unique Names (tried with and without setting as Primary Key)


    Relationships
    - Table 1.field with Unique Names points toward all other tables with a field with Unique Names.
    - Enforce Referencial Integrity
    - Cascade delete/update related fields

    Update Query
    Updates All Unique Names in other tables to reflect what I call the master table (Table1)


    Goal: All Unique Names entered into Table 1 (MASTER table) will be passed down to other tables


    Also: AND this is what really doesn't work
    I want the user to be able to select the Unique name from a combo box(it is set up). One problem. When I change the box, it will not allow me to change the primary key or delete it.

    Any suggestions are welcome. Main Problem is set up.

    Thank you in advance
    Steve
    Gees Steve, After those replies from blindman,Izzyand ? I hope you're not as confused as I am. )))))

    Geoff P

Posting Permissions

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