Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Relating two tables in MS Access

    I have created a database in MS Access 2010, In that I had only one table, because I had too many fields in my record (above 255 fields) , finally I have created another table.Now that one part of my record is in one table and the Other part is in another table .How to connect these two table.Now that information contains in one row will be in two separate table.How to connect these two table

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    113
    Thanks
    8
    Thanked 15 Times in 14 Posts
    Hi Yusuf

    Simply include a primary key in the first table (e.g. an autoincrement field) and a matching primary key (not an autoincrement) in the second table. Create a one to one relationship between the two tables on the primary key fields. You can then create queries from the two tables using fields from either.

    However, in my experience no-one has ever genuinely needed a table with over 255 fields. That usually means the table includes repeated fields, violating one of the normalisation rules. (http://en.wikipedia.org/wiki/Database_normalization)

    If that is the case, consider breaking your table into master and detail tables. A classic example is a customer order in a shipping database, which in non-nrmal form might be structured like this:

    table orders: orderid, custname, custaddr, custphone, custcontact, itemname1, itemprice1, itemquant1, itemname2, itemprice2, itemquant2, itemname3 (etc. ad nauseam)

    The issue is those 3 repeated fields, which to allow for orders for up to 100 items might give you a 300+ field table. The data should be normalised as

    table customers: custid, custname, custaddr, custphone, custcontact
    table orders: orderid, custid
    table lineitems: itemid, itemname, itemprice
    table orderlines: orderid, itemid, itemquant

    then a query for order lines might look like (in sql view)

    select orders.orderid, customers.custname, customers.custaddr, customers.custphone, customers.custcontact, lineitems.itemname, lineitems,itemprice, orderlines.itemquant
    from orderlines join orders on orderlines.orderid = orders.orderid join lineitems on orderlines.itemid = lineitems.itemid join customers on orders.custid = customers.custid

    or something like that.

    Ian.

  4. The Following User Says Thank You to iansavell For This Useful Post:

    Yusuf (2013-12-07)

  5. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you very much NewLonger I did the same thing, and it worked. Now my question is that what if we want to export those two table to Ms-Excel, will they lose their continuation,I meant that they are in one row.
    Thanks

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Yusuf,

    If you want to export to excel you need to create a query using the tables including all the desired fields and then export the query and you will get what you want. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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