Results 1 to 6 of 6
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    create a table from an existing table with ine to many relationship

    Hi,


    I have a table (table1) with several fields, I want to create a second table (table2) with only two fields (having these fields from table1) taking into consideration that table2 will be the one side of the relation and table1 will be the many side of the relation.
    How can I do this?

    Khalil

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not really clear, but try a query selecting those two fields, and either set the unique values property of the query or change it to a totals query and group by both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    To make it more clear:

    table1 has 6 fields as follows: pkId, field1, field2, field3, field4, field5, fkfield
    note: fkfield does not have any values since the relationship is not yet created until now.

    I want to split this table and create a second table named table2 as follow:
    table2 will have the fields: pkfield, field2, field5
    so table1 will look like this:
    table1 will have the fields: pkID, field1, field3, field5, fkfield

    where table1 is the many side of the relation and table2 is the one side of the relation
    the join fields will be fkfield from table1 (many side) and pkfield from table2 (the one side of the relation)

    Khalil

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use the query I described as the base for an append or make table query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi Paul,
    I have used the two fields as a base for a make table query also grouped by both and was able to create table2.
    I added a primary key to it and created a relationship with table1.
    Unfortunately there were no data in the fkfield in table1. fkfield was added to table1 so as I can make the one-to-many relationship and has no values for the existing record in the original table1.
    Am I missing something here?

    Thanks
    Khalil

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have to enter the pkfield value either manually or with an update query.

    I want to split this table and create a second table named table2 as follow:
    table2 will have the fields: pkfield, field2, field5 <<-- I think you meant field4 not field5
    so table1 will look like this:
    table1 will have the fields: pkID, field1, field3, field5, fkfield

    Here is another way. DO THIS ON A COPY OF THE DB....just in case......you have been warned


    1) Create a copy of table1. Copy the structure and data. Save as table2.
    2) Edit table 2 - delete the field fkfield and save the table.

    Now you have two tables with identical data in the records, but table 2 does not have the field fkfield.

    3) Create a new query and paste in this SQL:
    Code:
    UPDATE Table2, Table1 SET Table1.fkfield = [Table2].[pkId]
    WHERE (((Table2.field2)=[Table1].[field2]) AND ((Table2.field4)=[Table1].[field4]));
    4) Execute the query. You will get a warning about updating X number of records. Click YES.

    Open table1. Look at the field fkfield. All records should have a value for this field.
    Close table1.

    5) Open table2 in design view. Delete the fields field1, field3 and field5. Close and save the table.
    6) Open table1 in design view. Delete the fields field2 and field4. Close and save the table.


    Done!!??



    You can create another query and past in this SQL
    Code:
    SELECT Table2.pkId, Table1.fkfield, Table1.field1, Table2.field2, Table1.field3, Table2.field4, Table1.field5
    FROM Table2 INNER JOIN Table1 ON Table2.pkId = Table1.fkfield;
    Should return the original table 1 table data PLUS fkfield had values.

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

Similar Threads

  1. Create table from existing junction table
    By Khalil Handal in forum Access
    Replies: 2
    Last Post: 07-29-2017, 04:37 AM
  2. Replies: 5
    Last Post: 08-22-2013, 04:32 PM
  3. Replies: 3
    Last Post: 03-28-2013, 07:22 AM
  4. Replies: 2
    Last Post: 01-28-2013, 08:59 PM
  5. Using VBA create a new table from an existing table
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-25-2009, 04:07 PM

Tags for this Thread

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