Results 1 to 2 of 2
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

    design problem

    In my database there are 3 tables. Like below



    Table1:

    Doc No (PK) (text type)
    Title
    Unit

    Table2:

    Transno (PK) (text type)
    Date

    Table3:
    Doc no (FK) (text type)
    Trans no (FK) (text type)
    page


    In table 1, I selected “docno” field as primary key. And value of “docno” field is text similarly to below

    “Dw-999-1340-0001” (some times text value is more than of 50 characters).

    But database is slow.

    Now I do not know for repairing design I have to add another field to my tables? For example

    Table 1:
    Docid (PK) auto number
    Doc no
    Title
    Unit

    Now if I have to add auto numbers fields to my 3 tables, what I have to do with previous records because each table has more than 20000 records and they are related. I do not know how I have to update that?

    Please help what I should do for repairing.

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Add the DocID field to table3 as a number not autonumber.

    Build an update query Link table3 to table1 on docno. and update the docid. After confirming that everything worked ok set docid as the foreign key and delete the docno from table3

    Make sure to backup table3 prior to running this just in case.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. complex design problem
    By Madmax in forum Access
    Replies: 2
    Last Post: 12-09-2011, 08:25 AM
  2. design problem
    By marianne in forum Database Design
    Replies: 7
    Last Post: 05-26-2009, 07:25 PM
  3. Table design problem
    By wasim_sono in forum Database Design
    Replies: 0
    Last Post: 04-08-2009, 06:21 AM
  4. Database Design Problem
    By Kurth in forum Access
    Replies: 0
    Last Post: 08-14-2008, 04:09 AM
  5. Forms design problem
    By GeorgeD in forum Forms
    Replies: 2
    Last Post: 05-08-2008, 12:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums