Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81

    Unanswered: One-To-Many Relationship

    I have 30 databases in my department. Each department has their own database. We are just now creating a Back-End database for UNIVERSAL data like Employee Info.

    Heres the situation. We are making an EMPLOYEE TABLE and have EMPLOYEE ID set as the Primary Key. I need to be able to CASCADE UPDATE so that if someone gets married, we can change their employee id. The Employee Id is like say "John Thomas" would be "THOMJO".

    This works in each database right now becuase each has its own EMPLOYEE TABLE. When I link in the BACK-END Emplopyee Table, I can create a One-To-Many but the CASCADE UPDATE is not highlighted. It will not let me choose that option. I have checked both tables to makes sure the relationships and field properties are the same.

    I was told by someone that this is not possible with Linked Tables. Is there something I can change to make it do a CASCADE UPDATE? We are all sharing the same Workgroup. All databases are stored on the same network drive.

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: One-To-Many Relationship

    Originally posted by tsyscps
    I have 30 databases in my department. Each department has their own database. We are just now creating a Back-End database for UNIVERSAL data like Employee Info.

    Heres the situation. We are making an EMPLOYEE TABLE and have EMPLOYEE ID set as the Primary Key. I need to be able to CASCADE UPDATE so that if someone gets married, we can change their employee id. The Employee Id is like say "John Thomas" would be "THOMJO".

    This works in each database right now becuase each has its own EMPLOYEE TABLE. When I link in the BACK-END Emplopyee Table, I can create a One-To-Many but the CASCADE UPDATE is not highlighted. It will not let me choose that option. I have checked both tables to makes sure the relationships and field properties are the same.

    I was told by someone that this is not possible with Linked Tables. Is there something I can change to make it do a CASCADE UPDATE? We are all sharing the same Workgroup. All databases are stored on the same network drive.

    I have neaver needed to mess with cascade update but...I think there are a few diff problems here. If you are truly going for a backend/frount end situation ALL the universal data needs to be in ONE database. There needs to be a limited number of addmins to that one database. The one to many relationships need to be created in that one back end app not in all of the other apps. When the apps link into the backend with that relationship/option settings on then it should do the cascade updates. Another thing to think about.....though it sounds like its too late to do anything about it.....but for future reference...you need to create ids like employe id or customer id as numarics to get the true value out of an ID as well as to avoid all of this changing nonsence. I mean when you have a lot of data do you realy want to do a cascade update on years of history........Numaric ID's are always better for performance and low maintanance. Just thought Id throw this out, I may not have completly understood your problem.
    Regards Jim

  3. #3
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    I aprpeciate it. I would love to have a numeric ID however we do have a situation,.

    This is a production envrionment and these ids are for entering production totals. When someone needs to run a report and choose between 2000 people, its easier to user their naming convention. Its hard to assign a number to everyone and remember the numebr for 2000 people. We use this as a reference, otherwise I would have used a numeric ID.

    The problem with your suggestion, and believe me we thought about it to, is that each of these 30 databases holds a production table that is formatted for that department. We cant have one universal production table. One department here generates 35000 records in one year, imagine having 30 departmes in that one table. Even keeping seperate tables, i wouldnt want to risk having that much data in an Access database period becuase we hold 2 years worth of data. Therefore we have decided to leave the production form in each individual database.

    That does help me to know that we can do it by creating the relationships in the backend. That means we may be able to do it on the tables that we do have stored in that database. Just not the linked ones. Many people have suggested SQL but its just not an option for us right now, we are simply limited to Access at this time. We are basically running 97 but are upgrading some to 2000 at this time and even considering ipgrading them all to XP (2002) within a year. These decisons havent been made. I definatl want to get out of 97 though.

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by tsyscps
    I aprpeciate it. I would love to have a numeric ID however we do have a situation,.

    This is a production envrionment and these ids are for entering production totals. When someone needs to run a report and choose between 2000 people, its easier to user their naming convention. Its hard to assign a number to everyone and remember the numebr for 2000 people. We use this as a reference, otherwise I would have used a numeric ID.

    The problem with your suggestion, and believe me we thought about it to, is that each of these 30 databases holds a production table that is formatted for that department. We cant have one universal production table. One department here generates 35000 records in one year, imagine having 30 departmes in that one table. Even keeping seperate tables, i wouldnt want to risk having that much data in an Access database period becuase we hold 2 years worth of data. Therefore we have decided to leave the production form in each individual database.

    That does help me to know that we can do it by creating the relationships in the backend. That means we may be able to do it on the tables that we do have stored in that database. Just not the linked ones. Many people have suggested SQL but its just not an option for us right now, we are simply limited to Access at this time. We are basically running 97 but are upgrading some to 2000 at this time and even considering ipgrading them all to XP (2002) within a year. These decisons havent been made. I definatl want to get out of 97 though.
    Im sorry that you cant get out of access...sounds like you are going to hit a wall very shortly. Keep in mind that access db's cant get more than 1 gig. After that they shut down in a very very harsh way. It may be time to start thinking of a plan to move it, and i would take the time to redesign the db. Remember just because you have a numeric id that dosent mean that the end user has to see it. You can creat a cross reference table with id to name. you can write a query like

    insert *
    from table prod p, xref x
    where p.id = x.id
    and x.username = John

    That way you get your id and they get there Long name. You definatly need to find a way to move to sql and compile these db's into one. take the time to design the db to meet all of the needs so that they can all be in one db. use nameing standards to keep departments seperate and keep universal info together. The maintenance right now that you have must be horid and god know what its like if you have a failure. Even when the data is in sql you can still us access so your end users will neaver even know. It will take time to move and thats why I say try to take some time to figure out the db design that way you will be one step in frount. Push for SQL for your next quarter, the ROI you will get out of it will be huge I promise.
    Jim

Posting Permissions

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