Results 1 to 6 of 6
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I have two tables in a 2007 database. Table 1 (my primary table) has fields of ID (primary, indexed, auto-number), Case Name (text, indexed, unique), and Case # (number, indexed, unique). Table 2 has fields of ID (primary, indexed, auto-number), Case Name (text, indexed, unique), Case # (number, indexed, unique), Disposition (text, indexed, unique), and Dispo Date (date, indexed, dupes-okay). The Disposition field is comprised of a lookup with a variety of disposition types. There is a one-to-many relationship from Table 1 to Table 2 on the Case # and Case Name fields. I chose to cascade-update related fields. Join Properties is #2 (include all records from Table 1...). I placed Table 2 as a subform in another form. In my subform, I select a Disposition and enter a Dispo Date. Table 2 is updated with that informtion, plus the Case Name, but not the Case #. In other words, the Case Name is added automatically to Table 2, but Case # is not. So, how can I get the Case # from Table 1 to populate the Case # field in Table 2, just like Case Name does? As you can tell, I'm far from an expert in Access! I can provide some screen shots, perhaps.
    JimmyW
    Helena, MT

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have you set the Master and Child li k between the main form and subform to use both fields? Look in the properties of the subform control on the main form.

    But generally you link tables via Key fields, so if the Primary Key of Table 1 is ID, (this would be better to be called CaseID) then table 2 should just have a CaseID rather than the Case Name and CASE #.
    Why have a CASE # and a CaseID as two separate numerical identifiers? Can't you just choose one or the other, and remove the one you don't need?
    Regards
    John



  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks very much, John! I had no idea of Master/Child linking, but that fixed my problem. I also removed the ID fields. I attached a screen shot of my relationships. Each table is linked on ite Case # field. Among all of the tables, I have one Primary key (Case #) in my main table, which is Cases. The Case # field in each of the other table is not unique to those tables. If you have a comment or suggestion, it would be quite welcome. Thanks again.

    Jimmy
    Attached Images Attached Images
    JimmyW
    Helena, MT

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Every table should have a Primary key. Just add an autonumber if you don't otherwise have a key.

    The only field that gets repeated across tables is the key. Other than the Cases table, no table should have a Case Name field. It can be retrieved from the Cases table when needed.

    I would suggest you always preface ID with something else if you use it as a field name. You can easily end up with a string of fields named ID, which can be confusing. So I would have DispositionID in this case.

    Many people adopt a set of naming conventions, that tend to make things work better. There are various forms of this, but a basic one would include:
    • No spaces in field names. (either use CamelCase or underscores instead of spaces)
    • No special characters in field names. (# is a special character)
    This post is an example where the combination of # and spaces in a field name caused a problem.

    Many people extend the naming convention to tables queries etc, and would at least use the two rules above, together with a prefix to indicate the type of thing.
    So Dispositions would be tblDispositions, and Archive Drives would be tblArchiveDrives (or tblArchive_Drives if you like underscores).
    Regards
    John



  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks very much, John. I added an ID (like DispoID) primary field to every table, except Cases. I left Case_# as the primary key, but I can change that and create CaseID if it would improve efficiency. If I understand corredctly, I should not repeat the field names from Cases in any other table. This is where I'm confused. For example, Case name is unique in the Cases table, but a given case name can appear any number of times in the Archive Drives table. When I open the Archive Drive table, I want to see a populated Case Name field, unless you're suggesting a query to accomplish that. I created subforms from the three smaller tables, which I embedded in a Case form created from the main table. I enter my data in the form. It may be a little late to rename my fields to remove spaces, though I'll give it a shot. I have to make sure that I don't mess up my control sources. Thanks again; I apologize for taking so much of your time.
    JimmyW
    Helena, MT

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You should only store each piece of information once, so the Case Name is stored in the Cases table and nowhere else.
    Tables are your data store, but tables are not "user friendly". They don 't display it the way you and others want to see it.

    To view the data the way you want to see it you use forms and reports, that use queries (most of the time) as their record sources.

    Where you have a primary record (Case) and a number of related records, the normal display format would be a form with several subforms.
    The form would display one Case at a time, and each subform would show multiple records from the other tables.
    In that case the Case Name would be visible in the Main form, so there is no need to repeat it in the subform.
    (this sounds like what you have done already.)

    Rick Fisher's Find and Replace is a utility you can use to rename fields and have all references updated for you.
    Regards
    John



Posting Permissions

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