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

    Unanswered: Data Input/Update issue

    Okay, this makes my head hurt, so bear with me that I can explain it well enough that maybe someone can help me.

    I have a huge database that is used to track Organizations and Individuals that are affliated with the org. The main inteface is a tabed form that is used to view/enter data.

    Tab 1 shows Org/Ind information. This is pulled from a Names table that contains a record for each Org and each individual.

    Tab 2 show the relationship between each Org and Ind, this pulls from a Table called Relationships where where are multiple records. One individual can have relationships with multiple orgs.

    During the course of an Organization it can change relationships (exp...new ownership) When this happens the process to makethe changes is extensive and takes over 40 minutes to do it. They are looking for a better way to do this. here is the current process they follow:

    Go to the Org Record - Change Relationship of the indv (current owner to former owner) in subform
    Go to Indv Record for each person (could be multiple) and change to inactive status and Former Owner - this needs to be done on both Tab 1 & Tab 2
    Create a New memberID for the new owner if not already a member - this is manual and done outside of database.
    Go directly into Names table and add record for new member, this generates a new Access ID
    Go back to Org Record, add the new person to Tab 2
    Go to the Indv Record, add the new person to Tab 2

    So as you can see they are duplicating data in multiple places and when change or update data it doesn't do so in other tables.

    I know this is probably not enough info to help, but ask questions and I'll answer to my best ability, my goal is to find a better way to add/change/update data and have it duplicat entry eliminated.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You don't mention the structure of your tables, but from your presentation of the problem, I understand two things: (1) - you're following a physical process instead of trying to do it with VBA. This makes me think that (2) - your data is not normalized.

    There's no reason on earth that, once it's programmed correctly, you can't do the whole operation in VBA in about 3 seconds.

    I suggest you read up on data normalization.

    Sam

  3. #3
    Join Date
    Aug 2011
    Posts
    23
    I will admit that I am not an expert here and that from what I have read on Data Normalization, it seems that this DB is, however I can't say for sure, as you say it doesn't seem like it is. As for the structure of the tables, they are all linked by an Access ID field, each table has this field. I have three days left after today to finish up this project, if this linking can't be done they will accpet that, but I want to do what I can to make it easier for them on a daily basis.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I hate to ask embarassing questions, but, um, is there a VBA programmer? This can't be solved any other way if you're using Access.

    Sam

  5. #5
    Join Date
    Aug 2011
    Posts
    23
    Sam, the answer to that would be NO! I am the only person in this office that knows anything about Access and I am being brought in as a consultant to make changes to forms, reports etc.

    So I'm assuming that they will need to continue to to this the way they have been.

Posting Permissions

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