Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to decide the best way to setup a new database that I am creating that right now has two main tables in it. Right now the tables are linked by the LNFN field so I can put together queries and draw data from both tables.

    Is LNFN the best way to link the tables?

    How do you add a new name to the database that would show up in both tables or additional tables that I may add?

    Thanks for the suggestions.

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd use an AutoNumber field in one of the tables as primary key, and a number (long integer) field in other tables that links to the AutoNumber field.

    The advantage of an AutoNumber field is that it is guaranteed to be unique and that it never changes. The last name+first name of a person is not necessarily unique, it can be misspelled and it can change over time.

    You can create a form based on the table with the AutoNumber field, and a subform based on the other table, linked on the appropriate field (through the Link Master Fields and Link Child Fields properties). When the user starts entering data in a new record in the subform, the link field will automatically be filled in with the AutoNumber value from the main form.


  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    I'd use an AutoNumber field in one of the tables as primary key, and a number (long integer) field in other tables that links to the AutoNumber field.

    The advantage of an AutoNumber field is that it is guaranteed to be unique and that it never changes. The last name+first name of a person is not necessarily unique, it can be misspelled and it can change over time.

    You can create a form based on the table with the AutoNumber field, and a subform based on the other table, linked on the appropriate field (through the Link Master Fields and Link Child Fields properties). When the user starts entering data in a new record in the subform, the link field will automatically be filled in with the AutoNumber value from the main form.
    Thanks Hans.

    I'll experiment with that approach.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pbien View Post
    Thanks Hans.

    I'll experiment with that approach.
    Attached is my practice effort. On the form frmAddNewStudent what's the code to link the Ids to the two tables in the subforms so the new student will have a record in each table.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    At the moment, the user can't fill in anything useful in the subforms since they only contain the link field.
    You should add some fields to each of the subforms.
    The moment the user starts to enter data in a subform, the link field will be filled in automatically. So there is no need to write code that creates a record in each subform.


  6. #6
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    At the moment, the user can't fill in anything useful in the subforms since they only contain the link field.
    You should add some fields to each of the subforms.
    The moment the user starts to enter data in a subform, the link field will be filled in automatically. So there is no need to write code that creates a record in each subform.

    Ok, I added fields and I think I linked them but it still does not work. See attachment.

    The subform sbfConditions does not link the ID number.

    I'm missing something?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think there's a misunderstanding here. As far as I can tell, the subforms should be linked to the main form by the ID field only.

    The table tblHealthConditions shouldn't contain fields LNFN and gradyear - they are determined by ConditionsID (which provides the link to tblBasicData.
    ConditionsID should NOT be the primary key of this table - you wouldn't be able to create more than one record for a student.

    The table tblImmunizationRecords likewise shouldn't contain fields FN, LN, LNFN, bdate and gradyear - they are all determined by ImmunizationID.
    ImmunizationID should NOT be the primary key of this table, for the same reason.

    If your intention is that there should be one and only one record per student in tblHealthConditions and in tblImmunizationRecords, you shouldn't have these tables, but include their fields in tblBasicData.


  8. #8
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    I think there's a misunderstanding here. As far as I can tell, the subforms should be linked to the main form by the ID field only.

    The table tblHealthConditions shouldn't contain fields LNFN and gradyear - they are determined by ConditionsID (which provides the link to tblBasicData.
    ConditionsID should NOT be the primary key of this table - you wouldn't be able to create more than one record for a student.

    The table tblImmunizationRecords likewise shouldn't contain fields FN, LN, LNFN, bdate and gradyear - they are all determined by ImmunizationID.
    ImmunizationID should NOT be the primary key of this table, for the same reason.

    If your intention is that there should be one and only one record per student in tblHealthConditions and in tblImmunizationRecords, you shouldn't have these tables, but include their fields in tblBasicData.
    This is what I was trying to do. Hopefully you can suggest the best way to do it.

    I was trying to take tables from three databases and combine them into one database.

    I know I could just add all the fields from all the databases into one big table, but I thought it would be better to keep the data in the three tables and add a new basic info table and then link the tables together so that you could get whatever info you wanted about a student. My problem started when I tried to figure out how to add a record for each student in each table when necessary.

    What you are saying is that this appraoch is not a great idea.

    Should I stick with one big table?

    Paul

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by pbien View Post
    Should I stick with one big table?
    If possible, yes. You'll avoid a lot of problems if you use one big table.

Posting Permissions

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