Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking Unanswered: Changing database structure

    I have a database that tracks staff training and I'm trying to resolve a many to many relationship. My current database has:

    tb_staff linked to tb_courses_done 1:n and also linked to tb_course 1:n so although this works 1 staff can take many courses but many staff can take many courses. I want to introduce booking too so staff can have courses pending (booked) and completed.

    I've come up with tb_staff, tb_staff_course, tb_course_date and tb_course for the new format and it seems to work.

    My problem now is, how can I get my existing 4000 odd records across? Is it possible given that the keys are changed?

    Thanks.
    Thomas J Marshall

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without knowing the detail of your table design its perfectly possible.

    The usual technique is to move the data into the new tablesusing a series of insert queries. The trick is to temporarily add c column containing the old key(s) from the old data. You retain these untill you are happy your data is imported correctly. Note if the original key value is an autonumber then create it in the new tables as number:long.

    Using the query wizard create an insert query to insert all relevant data to tb_Staff and tb_Course

    then you need to insert the the data into the cross reference tables tb_staff_course.

    so you need to match the old staffid in the new tables, and the old courseid, retrieve their new equaivalents from the new tables and insert the records into tb_staff_course. Likewise tb_CourseDate (if appropriate).

    Once you are happy the data has been correctly populated you could delete the old key columns, however there is an argument to leave them in place - the extra storage is minimal so I'd just leave then until you are absolutely confident the import has worked correctly.

    Hope this helps, its a little difficult to explain (well it is for me at least).
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    And for a full belts and braces approach it's worth taking a full backup of your current database before making any changes.
    Chris

  4. #4
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Quote Originally Posted by healdem
    without knowing the detail of your table design its perfectly possible.

    The usual technique is to move the data into the new tablesusing a series of insert queries. The trick is to temporarily add c column containing the old key(s) from the old data. You retain these untill you are happy your data is imported correctly. Note if the original key value is an autonumber then create it in the new tables as number:long.

    Hope this helps, its a little difficult to explain (well it is for me at least).
    I'm not expert at access hence my next question- do I create the new structure within my existing database (copy of!) and import from there?

    I'm not sure how I'll do with the insert queries (although I'll have a go) so is it possible to change the existing structure using the table analyser? Would that work instead?
    Thomas J Marshall

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Chris (Howey) helpfully points out take a backup first

    Where are your new and old tables?

    if the old tables are outside the current db then import them
    file | get external data | import

    open the new table(s) in design mode, add whatever columns you need being carefull to keep the data types & sizes the same (the only variation is that Access will not let you have more than one autonumber column (and in any event you do not want to renumber the existing (old) key)

    open the query tab, create a new query
    select the source table(s)
    in the query type select "append", specify the destiantion table
    then you associate the destination column with the source column, don't include the new autonumber key column, but store the old key in a new column.


    the only really fiddly query is where you have to retrieve the old value in the new tabels to find what the correct new key value is. as part of that append query you need to select the old data source and the new table(s), join (using a drag and drop) onm the old key columns but use the new value as part of the append.

    It sounds a lot more complicated than it is to do - have a look at the help system and have a "go" - but do so in your copy NOT your original data.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Thanks, i'll let you know how I get on.
    Thomas J Marshall

  7. #7
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Quote Originally Posted by healdem
    ...so you need to match the old staffid in the new tables, and the old courseid, retrieve their new equaivalents from the new tables and insert the records into tb_staff_course. Likewise tb_CourseDate (if appropriate).

    the only really fiddly query is where you have to retrieve the old value in the new tabels to find what the correct new key value is. as part of that append query you need to select the old data source and the new table(s), join (using a drag and drop) onm the old key columns but use the new value as part of the append.
    I'm stuck I think. My original database has:

    tb_staff - staff_id, fname, lname, job_title, base etc.
    tb_courses_done - courses_done_id, staff_id, start_date, end_date, venue, trainer etc.
    tb_courses - tb_course_id, course

    The bold fields are primary/foreign keys. I have tb_staff to tb_course 1:n and tb_staff to tb_courses_done 1:n on a composite of the 3 fields.

    My proposed setup is:

    tb_staff 1:n to tb_staff_course
    tb_course_date 1:n to tb_staff_course
    tb_course 1:n to tb_course_date

    I'm not sure what keys to link.
    Thomas J Marshall

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Thomas
    I think its going to be difficult to talk you through this....

    what are the discrepancies between the current and the proposed table design. Effectively what data have you got to carry over from the old system. Are you planning to move the data to the new structure or modify the existing structure?

    Is you problem moving all the data or just moving some of the existing data to match the new format.

    perhaps it would be better if you could give a detail of the old and new design.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by tmarsh
    My proposed setup is:

    tb_staff 1:n to tb_staff_course
    tb_course_date 1:n to tb_staff_course
    tb_course 1:n to tb_course_date

    I'm not sure what keys to link.
    So what you are saying is you don't think you have got your design nailed? Do as healdem suggests and list your old and proposed new design. For there we should probably revisit your design and make sure it meets your requirements before moving data around.

    Here's my stab at your new design:

    tb_staff (staff_id, fname, lname, job_title, base etc)

    tb_staff_course(staff_id, course_date_id)
    I assume by tb_staff_course you mean courses attended ?

    tb_course_date (course_date_id, course_id, start_date,end_date, venue, trainer, etc...)
    I assume by tb_course_date you mean a table of schedules (scheduled courses)

    tb_course (course_id, description etc)

    Note the underlined fields are the primary keys. You should be able to work out the links (foreign keys) from this.

    If this is along the lines you are thinking of then I would rename tb_staff_course as something like tb_courses_attended and tb_course_date as tb_scheduled_courses as it will be clearer in your head what it is - but it's a personal thing.

Posting Permissions

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