Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Exclamation Unanswered: Remake of the database model without loss of Data

    Hello,

    attached is the model of my database in MS Access.
    now i have been said to remake it, cuz now every employee may have many cards...

    what i have to do now is, create one new table c_cards. Every employee will have MANY cards, and every card will have CardExpireDate, CardIssueDate,CardID and details fields.

    next step is to rename EmployeeID field in the rrights table to the CardID. delete its relationship with e_employees.

    next i create the c_cards table, with the fields specified above.

    then copy CardExpireDate and CardIssueDate fields from e_employees to c_cards

    now could u help me with SQL command which will stick every employee with his card and permissions from the r_rights through the c_cards table?

    any other suggestions?

    p.s. i dont keep any real card ID... so the CardID assigned by MS Access would be fine for me...

    thanks in advance.
    Attached Thumbnails Attached Thumbnails data.gif  

  2. #2
    Join Date
    Nov 2007
    Posts
    43
    ok, what i have did now is, i have created new tables cards and rights, and disconnected old r_rights table. now i must copy data from r_rights to the rights table.

    and then change the "EmployeeID" field of the rights table to the appropriate CardID field from the new cards table...

    how to do it using SQL in Access?

    thanks

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Question1... why do you name your tables l_like t_this ??

    Question2... what has r_rights got to do with it? Seems completely unaffected by this modification to me.

    Steps to upgrade imo are:
    0. Make a backup!
    1. Create Card (c_cards) table.
    2. Copy the CardExpireDate, CardIssueDate and Details fields to the new table. Create a new EmployeeID field in the new table.
    3. Relate the Employee and Cards tables.
    4. Use an append query to append CardExpireDate, CardIssueDate, Details and EmployeeID from your Employee table into the Card table.
    5. Check everything is ok.
    6. Delete the CardExpireDate, CardIssueDate and Details fields from your Employee table.

    Hope this helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2007
    Posts
    43
    r_rights is a cross table for permissions.. every card maybe have one or more permission areas associated with it.. r_rights is the very important table! and the problem is to relate this table to the new Cards table so that not every employee will have one or more area permissions but every CARD will have one or more area permissions .otherwise of cuz i can copy those fields and it would be ok

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    [QUOTE=StarTrekker]Question1... why do you name your tables l_like t_this ??

    Yes, I was wondering that also. You're making extra work for yourself by prefacing each name with it's own initial. Nothing serious but just a tad more work for you.

    BUD

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok so if the r_rights table needs to be related to c_cards instead of e_employees, you'll need to:

    0. Take a backup!
    1. Do what I said in my previous post. At this point every e_employee should have ONE c_card.
    2. Rename r_rights.EmployeeID to CardID.
    3. Create a query that gets all the r_rights entries with all fields. Relate it to your c_cards table (r_rights.CardID to c_card.EmployeeID).
    4. Change the query to an update query, and update the r_rights.CardID field to c_card.CardID.
    5. Run the query.
    6. Delete the relationship between r_rights and e_employees.
    7. Create the relationship between r_rights and c_cards.
    8. Check the results.

    I hope this works for you, I've created the list in my head and it's late. Always do step ZERO!!! :P

    I'd still like to know why you name your tables l_like t_this!

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I like step 0 the best
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, it gives me a warm-fuzzy feeling :P
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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