Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    23

    Unanswered: Duplication of data in multiple tables...

    As i studied the tables last night and how they are working...here is an overview of what I've found:

    The Names table contains all the records for Organizations and Individuals.
    The Address table contains all the address for Organizations and Individulals, there are multiple records for in this table for each Org/Ind...field Type indicates type of address (mailing, shipping, physical) So there may be 3 record in Address for each Org/Ind, this table is linked to Names table by Access ID (autonumber field)
    The Relationship table contains records for each relationship an Indv has with an Org (Member, Former Member, Coach, Judge, etc). There is a record for each relations so multiple records for each Org. Note: There Indv records in the Names table that are duplicate info of Records in the Relationship table. Example: Joe Smith is the Owner of ABC Organization. There are two records in the Name table, one for Joe Smith as an Indv and one for ABC Org as an Org, then there is a record in teh Relationship table for Joe Smith for his relationship to ABC Org as the Owner, but this record doesn't seem to be linked to the names table and has to be updated in addition to the ABC Org and Joe Smith records in the Names table.

    Is there an easy way to link the relationships to the Names table so that when the Indv record in the Names table is updated that the relationship is updated as well? Right now if Joe Smith becomes a Former Owner, they have to go to his Indv record change his membership Type to Former Owner, then go to the Relationship tab, change his type to Former Owner, then go to the Org record and change his relationship there to Former Owner. Then once teh new owner is added, this all has to be repeated by adding new record in Names table for new owner, Going to ABC Org record adding relationship for new owner there.

    That to me seems like a lot of duplication. It takes them about 40 minutes just to make a change/add to a record.

    Any thoughts?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Normally there should be one row (and only one) for each set of data in each table (i.e. no duplicate values in the [Org/Ind] table nor in the [Address] table), but there can be several rows in the junction table (i.e. the one that creates many-to-may relationships) for each row in the data tables ([Org/Ind] and [Address]), etc.

    When the system is complex and uses many relationships (often with junction tables), it's easier to base them on a surrogate key (AutoNumber, GUID, TimeStamp, ...) that will never change than using columns the contents of which is subject to modifications.

    You then create an interface (Form and/or Query) to manipulate the different data sets. Trying to do it manually will introduce inconsistencies, sooner or later.
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    23
    That is kind of what I thought and I don't have time in my assignement to over-haul how the tables are created. So what I thought I would do was create a data entry form that contains all the data from the different tables (in subforms) so at least everything can be done from one spot. Right now they go directly into the tables do most of it.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by clpayton View Post
    That is kind of what I thought and I don't have time in my assignement to over-haul how the tables are created. So what I thought I would do was create a data entry form that contains all the data from the different tables (in subforms) so at least everything can be done from one spot. Right now they go directly into the tables do most of it.
    if you have the time fix the problem
    allowing users to directly access the tables is dangerous.. you haven't got a scooby who is oding what and where, you have no tracability, you are loosing control of the data (no validations, aside form column level validations, no checks on overall data integrity.

    if this is an assignement you will get more credit for doing it right than handing in a kludged workaround
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2011
    Posts
    23
    I agree Healdem, but unfortunately I am being brought in as a consultant and my time is up on Friday. I don't think they realized how the DB is put together, it have been passed on and there is no one here that knows Access as all.

    Truthfully, I don't think I have the knowledge to fix the problem. I am going to put in my recommendations that the data design needs to be over-hauled and fixed. I think this needs to be done by someone with more Access knowledge than I posess. I think I could do it with some time and help, but I don't know that they can/will do this. It's a non-profit org and they got approval to bring someone in for 2 weeks to make make design changes to forms, create some reports, add fields, etc. I have completed most everything on their "Wish List" the data linking issue was something I noticed.

    So, although I wish I could fix it, I don't think I will have the opportunity to do so. I hate to leave it like this, but not sure what else I can do other than make them aware that it needs to be done.

Posting Permissions

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