Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Tables (Access 2002)

    I have been creating a form for the past couple of weeks and am at the point where I need to link tables because I've got more fields then the 255 allowed per table. However I have no idea what I am suppose to do or how to do it, and I haven't been able to find a good resource on exactly what is table linking and how it works on the web. Can someone help me out? I would be willing to email my current database to you or attach it (its 1mb). Can anyone help me out? I'm sure you'll need more information...

    Thanks again,
    Leo

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking Tables (Access 2002)

    You need to learn about database normalization first. These are the basic principles by which databases are designed to maximize performance and minimize data problems.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    ok sounds good, where do i go to learn that?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (Access 2002)

    There are many books about this subject, but here is a short introduction: ACC2002: "Understanding Relational Database Design" Document Available in Download Center.

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    thanks for the article, im currently reading it. Here is a link to my table (800k), if you guys wouldn't mind taking a quick peek and telling me what I could group it would make life a lot easier for me. I am on the brink of tossing my computer out the window as I do consider myself a pretty smart guy but this concept I just can't grasp. Thanks. http://www.cleoent.com/Quote.zip

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (Access 2002)

    You have a lot of fields in sequences, for example

    (LType, LevolorPrice, Quantity)
    (Type2, LP2, Q2)
    ...
    (Type20, LP20, Q20)

    This violates relational design because it is a repetition of data of the same type. What you would do here is create a new table with four fields:

    InvoiceID (number, long integer), Type, LevolorPrice, Quantity

    The InvoiceID would be linked to the primary key in the main table (Auto in the Fields table). Instead of 20 x 3 fields in the main table, you would create a series of records in the new table, one for each type. The number of records per InvoiceID can be flexible, it isn't necessary to have 20 records for each InvoiceID.

    You would display these data in a subform of the main form linked by Auto vs InvoiceID. When the user starts to enter a type, the InvoiceID will be filled in automatically.

    This new table has 4 fields, replacing 60 fields in the main table. Others will be less spectacular, but you would follow the same principle, for instance the Track Type and Track Quantity fields would go into a new table with (InvoiceID, TrackType, TreackQuantity): 3 fields in a new table replace 20 fields in the main table.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking Tables (Access 2002)

    In addition to Hans' suggestion, you might try getting a book titled "Running Microsoft Access 2002" by John Viescas. He covers this among many other useful topics.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    Hans thank you so much for taking the time to look into my database. A follow up question would be:

    If I do what you suggested would I have to work in between two different forms to fill out all the neccessary fields ? I guess that's the only thing I'm trying to avoid.

    Mark: I think I will indeed purchase this book, I appreciate the help!

    Thanks again,
    Leo

  9. #9
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    Leo, you definitely don't have to work between two forms. You can create a main form based on your main table and put a subform inside which is based on your secondary table. You can also create a query to join your tables and use that as the data source of your form. However, if you do this, I'm not sure whether the data that you enter will be saved in the tables. Maybe this is only a good solution for a report based on both tables. Good luck!

  10. #10
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    You guys have been so very helpful!

    Hans: I did what you said step by step exactly how you said it. I also inserted a subform but it looks really out of place on my form, it looks like a table, is that what its suppose to look like?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (Access 2002)

    The table structure would be:
    1. <LI>A main table with fields such as Invoice No, Date, Company, Account Manager.
      <LI>A series of "subtables" with multiple records per Invoice No, such as
      1. <LI>Invoice No, Type, LevolorPrice, Quantity (as explained in my previous reply)
        <LI>Invoice No, Tracking Type, Tracking Quantity
        <LI>Invoice No, Room Darkening Type, Cost Per Width, Width
        <LI>etc.
      <LI>Lookup tables such as Account Managers, Customers etc. You already have those.
    You would display the invoice on a main form with subforms on the main form - one subform for each "subtable" as listed above. You can create a "long" form with all the subforms, or organize them into pages on a tab control on the main form. So the end user would see one form.

    Take a look at the Northwind database that comes with Access (it's in C:Program FilesMicrosoft OfficeOffice10Samples). See how the tables are stuctured, and how for instance the Orders form has a subform that displays order details.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (Access 2002)

    Subforms can be single forms (displaying only one record at a time), continuous forms (displaying several records at a time) and datasheet forms (looking like a table). The layout you have on your Quote form is best imitated by a continuous form.

  13. #13
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    hans again, I owe you a million thanks, I'll play around for a bit and report back. Your help has been awesome, thanks again.

  14. #14
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    This site Database Answers has some useful data models to look at which may help with the concepts of normalisation. Hopefully soon the culled posts will re-appear and there is one from me with a handout on normalisation in it.

    If the moderators feel it is appropriate I can re-post it tomorrow.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  15. #15
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (Access 2002)

    Hans,

    So far so good. What I did was I created another form with ltype1 (60 fields) so that I could have them all on the page at once like the original form. That way I could get access to calculate. Its looking good however in the subform there is a total field (adds all of the price x quanitty fields to get a total cost of the blinds). How do I get the number that is generated in the subforms "Total" box to appear automatically on the main form in a box I've named "Levolor Price"??

    The newest version of the database can be found here www.cleoent.com/Quote.zip
    If you have a few more seconds I'd really appreciate it. Thank you.

    Leo

Page 1 of 2 12 LastLast

Posting Permissions

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