Table relationships between 42 tables

Discussion in 'Access Table Design' started by Reality, Jul 30, 2011.

  1. Reality

    Reality Guest

    I cannot seem to find the correct link relationship combination that
    will allow me to connect 42 Excel worksheets to 26 to 77 Word
    Documents.

    Example:
    In my Product/Inventory Table, I have the name Ferric Ammonium Oxalate
    In my Excel Work Sheet, I have 1 to 5 Word Document titles that may
    apply to this one product.

    My question is how to resolve garbage in and garbage out results with
    the following:

    Product/Inventory Table
    PK=Ferric Ammonium Oxalate
    Once the 42 Excel worksheet are transferred over to Access 2010 into
    Tables
    FK= Each of the five documents, such as;
    WordChemicaTitrationProcedureListingFormatDocument

    Thank you for sharing both your professional time and your
    professional experiences with me in this personal matter


    Everything below here is just background information for my question
    to the Group.

    The elderly owner of this very small office/warehouse sells and buys
    chemicals. There are 26 basic chemicals*. Out of these 26 basic
    chemicals there are 77 products produced and sold.

    In the owner’s Windows XP C Drive folder named Chemical, the owner is
    using Windows Office 2007, are these Excel Labels and Word Documents.

    The basic process:
    An order is received.
    The order is prepared, that is the powder or liquid chemical(s) are
    selected
    Depending on the quantity a pale or drum is assigned
    The order is named based on one of the 77 products titles
    An Excel label is pulled from the Owner’s drive and printed.
    Than the accompany document(s) are pulled from this same directory and
    printed.
    The shipping company is called and an arrangement for pickup and
    delivery are made.
    The deliver is picked up with the Excel label attached to the
    container and the documents along with the container are shipped.

    Over looking for now the tracking of buyers, sellers, quantities on
    hand, last price, shippers, invoices, employees, payroll, tax rate and
    tax locations and a host of other factors; an Excel Label is to be
    printed and attached to each product to be shipped out. In addition to
    the product and label, there are industry wide informational sheet
    that should either be embedded in the label to automatically print or
    some way to let the printer of the Excel Label know that other
    documents need to be printed before the shipment is labeled and
    shipped.

    The immediate problem is how to attach the correct Excel Sheet
    converted to an Access information Table, which could be up to five
    documents, to the correct Excel Label. That is, each of the 26 to 77
    products in inventory matched to the appropriate one of many, in some
    cases five, Word Documents: calls for a PK on each of Excel Labels
    with a FK on each of the 42 pages.

    I am using Windows 7, 64-bit, Office 2010 Pro. I copied the titles in
    the Owner’s Chemical folder to my drive and folder.

    Using the owner’s 2007 Access with CD book, I sat out thinking I was
    going toward a solution only to realize that I have created a living
    nightmare for myself. I first copied all the examples on the CD to my
    computer and then attempted to exchange the information in the
    examples with that of the owner. That was my first steps into this
    madness of index key issues, data not formatted correctly, you need to
    fill out that table before you can enter data into this table, do want
    to delete current relationships, and on and on. I finally realize that
    I was either going to run an example or attempt to run an example that
    was going to permanently turn off my computer.

    So as the rule goes, when all else fails read the instruction. I
    started with Chapter one and by Chapter 10, yes using shortcut that is
    searching for the graphic figures and then reading the accompany text
    in the book that applied to the example on the CD, I realized that I
    could not apply what I was reading. I would see the relationship in
    the text graphic figure, even though it was not my data, I attempted
    to drawn the same linked relationship, the one to one, one to many,
    left, right or grandparent relationship left me wondering if a pen and
    paper were a more efficient solution. I looked at the solution in the
    User group, and gasp in wonderment with the word wow, that solution
    worked for that person. I then copy the solution word for word and
    then failure occurred at the end. So as to not further my nightmare, I
    decided I would stay away from using macro and VBA for the time being
    until I see some type of progress towards daylight.

    So I went into Excel and created 42 pages, I had to do something to
    stay sane, and/or at least establish the illusion of achieving some
    progress.

    An Excel Label will have at least one and maybe all five applicable
    Word Documents:
    A Word Document that applies to that Excel Label
    A Literature that applies to that Excel Label
    A MSDS that applies to that Excel Label
    A Titration Procedure that applies to that Excel Label
    The owner’s word documents that applies to that Excel Label

    Example
    Presently: Excel Label Name for Ferric Ammonium Oxalate
    Presently: An Excel sheet listing each category, such as;
    1. WordChemicaTitrationProcedureListingFormatDocument
    Ferric Ammonium Oxalate
    2. WordTitrationProcedureChemicalDocumentFormatEndingWithALetter
    Ferric Ammonium Oxalate CAS
    3. WordTitrationProcedureChemicalDocumentFormatEndingWithAMeasurement
    4. WordTitrationProcedureChemicalDocumentFormatEndingWithANumber
    Ferric Ammonium Oxalate 30
    5. WordTitrationProcedureChemicalDocumentFormatEndingWithAPercent
    6. WordTitrationProcedureChemicalDocumentFormatEndingWithAPro

    Would be associated or linked to this one Excel Label name for Ferric
    Ammonium Oxalate.

    *Chemistry for me is as Kryptonite is to Superman in the Superman
    mythos, an ultimate weakness, so I apology for miss using chemistry in
    such way as to state that it is a basic chemical. I am meaning to say
    that the term, that is the legitimate Chemical term, has no
    significance to me in this application other than its … well a basic
    something. I suppose I could have used XYZ.
     
    Reality, Jul 30, 2011
    #1
    1. Advertisements

  2. Reality

    Reality Guest

    On Aug 1, 2:20 pm, "David-W-Fenton" <>
    wrote:
    > Reality <> wrote innews:
    > m:
    >
    > > I cannot seem to find the correct link relationship combination
    > > that will allow me to connect 42 Excel worksheets to 26 to 77 Word
    > > Documents.

    >
    > You can't actually enforce RI between Excel workstheets -- all
    > you're doing is drawing lines that make drag and drop in the query
    > designer easier.
    >
    > --
    > David W. Fenton                  http://www.dfenton.com/
    > contact via website only    http://www.dfenton.com/DFA/


    A thousand thank you Mr. Fenton for your response and thank you for
    sharing with me your foresightedness into my problem.

    Since posting, overlooking my banging my head against all available
    walls, and threatening my computer to come up with an answer or else
    face being thrown out the window, and after evaluating my Excel skills
    on a scale of 1 to 10, 10 being the highest, my skills sat at an one,
    while my Access skills on the same type of scale sat at an minus one:
    I have temporarily settled on constricting each of the 42 buyer/
    sellers to separate Excel worksheets with just four columns of data:
    Name, Date, Item sold or purchased and invoice number.

    Admittedly, this defeats the immediate purpose of the Access project
    but it sets an immediate objective that being some progress even if it
    is only a little progress which is progress being attained through the
    combing of buyer/sellers into one format, which is Excel. This data
    alone will extend from this year back to January 1, 1997.

    The Plan will then become that of transferring each of the buyer/
    seller Excel worksheet into Access as a Table, thus the 42 link table
    requirement.

    Once in the Access project I will add the pertinent information, such
    as, address, buyer, seller or both, terms of payment, the name(s) of
    the chemicals—72 chemical option-- bought or sold, quantity and
    delivery information along with the regulated or non-regulated
    information and/or the features that distinguish the two; placards
    requirements, driver license of the driver properly State stamped,
    route restrictions, etc.,.

    Mr. Fenton, I apologize for this unavoidable delayed response, but I
    certainly thank you for the light of hope that you have projected onto
    this project.
    [Monitors please edit as needed]
     
    Reality, Aug 6, 2011
    #2
    1. Advertisements

  3. On Sat, 6 Aug 2011 01:53:18 -0700 (PDT), Reality <> wrote:

    >On Aug 1, 2:20 pm, "David-W-Fenton" <>
    >wrote:
    >> Reality <> wrote innews:
    >> m:
    >>
    >> > I cannot seem to find the correct link relationship combination
    >> > that will allow me to connect 42 Excel worksheets to 26 to 77 Word
    >> > Documents.

    >>
    >> You can't actually enforce RI between Excel workstheets -- all
    >> you're doing is drawing lines that make drag and drop in the query
    >> designer easier.
    >>
    >> --
    >> David W. Fenton                  http://www.dfenton.com/
    >> contact via website only    http://www.dfenton.com/DFA/

    >
    >A thousand thank you Mr. Fenton for your response and thank you for
    >sharing with me your foresightedness into my problem.
    >
    >Since posting, overlooking my banging my head against all available
    >walls, and threatening my computer to come up with an answer or else
    >face being thrown out the window, and after evaluating my Excel skills
    >on a scale of 1 to 10, 10 being the highest, my skills sat at an one,
    >while my Access skills on the same type of scale sat at an minus one:
    >I have temporarily settled on constricting each of the 42 buyer/
    >sellers to separate Excel worksheets with just four columns of data:
    >Name, Date, Item sold or purchased and invoice number.
    >
    >Admittedly, this defeats the immediate purpose of the Access project
    >but it sets an immediate objective that being some progress even if it
    >is only a little progress which is progress being attained through the
    >combing of buyer/sellers into one format, which is Excel. This data
    >alone will extend from this year back to January 1, 1997.
    >
    >The Plan will then become that of transferring each of the buyer/
    >seller Excel worksheet into Access as a Table, thus the 42 link table
    >requirement.
    >
    >Once in the Access project I will add the pertinent information, such
    >as, address, buyer, seller or both, terms of payment, the name(s) of
    >the chemicals—72 chemical option-- bought or sold, quantity and
    >delivery information along with the regulated or non-regulated
    >information and/or the features that distinguish the two; placards
    >requirements, driver license of the driver properly State stamped,
    >route restrictions, etc.,.
    >
    >Mr. Fenton, I apologize for this unavoidable delayed response, but I
    >certainly thank you for the light of hope that you have projected onto
    >this project.
    >[Monitors please edit as needed]


    You don't need 42 tables. You need 2! Or maybe 3.

    In Access, you would have one table for Buyers (with fields shuch as BuyerID -
    an autonumber primary key; BuyerName; Address; and other info about the
    buyer/seller as an entity). In a separate table you would have Transactions,
    with fields for BuyerID (linked to Buyers, who bought or sold); SaleDate
    (don't use the reserved word Date as a fieldname), ItemID (a link to a table
    of items, or if they're all unique you could just have a text field for the
    name of the item) and InvoiceNo.

    It would certainly be WRONG to have separate Access tables for each buyer
    <shudder>. Instead you would have one big table with a field identifying the
    buyer.

    You can link to the existing Excel data and run Append queries to migrate the
    data into your normalized tables.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Aug 10, 2011
    #3
  4. Reality

    Reality Guest

    On Aug 10, 2:42 pm, John W. Vinson
    <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    > On Sat, 6 Aug 2011 01:53:18 -0700 (PDT), Reality <> wrote:
    > >On Aug 1, 2:20 pm, "David-W-Fenton" <>
    > >wrote:
    > >> Reality <> wrote innews:
    > >> m:

    >
    > >> > I cannot seem to find the correct link relationship combination
    > >> > that will allow me to connect 42 Excel worksheets to 26 to 77 Word
    > >> > Documents.

    >
    > >> You can't actually enforce RI between Excel workstheets -- all
    > >> you're doing is drawing lines that make drag and drop in the query
    > >> designer easier.

    >
    > >> --
    > >> David W. Fenton                  http://www.dfenton.com/
    > >> contact via website only    http://www.dfenton.com/DFA/

    >
    > >A thousand thank you Mr. Fenton for your response and thank you for
    > >sharing with me your foresightedness into my problem.

    >
    > >Since posting, overlooking my banging my head against all available
    > >walls, and threatening my computer to come up with an answer or else
    > >face being thrown out the window, and after evaluating my Excel skills
    > >on a scale of 1 to 10, 10 being the highest, my skills sat at an one,
    > >while my Access skills on the same type of scale sat at an minus one:
    > >I have temporarily settled on constricting each of the 42 buyer/
    > >sellers to separate Excel worksheets with just four columns of data:
    > >Name, Date, Item sold or purchased and invoice number.

    >
    > >Admittedly, this defeats the immediate purpose of the Access project
    > >but it sets an immediate objective that being some progress even if it
    > >is only a little progress which is progress being attained through the
    > >combing of buyer/sellers into one format, which is Excel. This data
    > >alone will extend from this year back to January 1, 1997.

    >
    > >The Plan will then become that of transferring each of the buyer/
    > >seller Excel worksheet into Access as a Table, thus the 42 link table
    > >requirement.

    >
    > >Once in the Access project I will add the pertinent information, such
    > >as, address, buyer, seller or both, terms of payment, the name(s) of
    > >the chemicals—72 chemical option-- bought or sold, quantity and
    > >delivery information along with the regulated or non-regulated
    > >information and/or the features that distinguish the two; placards
    > >requirements, driver license of the driver properly State stamped,
    > >route restrictions, etc.,.

    >
    > >Mr. Fenton, I apologize for this unavoidable delayed response, but I
    > >certainly thank you for the light of hope that you have projected onto
    > >this project.
    > >[Monitors please edit as needed]

    >
    > You don't need 42 tables. You need 2! Or maybe 3.
    >
    > In Access, you would have one table for Buyers (with fields shuch as BuyerID -
    > an autonumber primary key; BuyerName; Address; and other info about the
    > buyer/seller as an entity). In a separate table you would have Transactions,
    > with fields for BuyerID (linked to Buyers, who bought or sold); SaleDate
    > (don't use the reserved word Date as a fieldname), ItemID (a link to a table
    > of items, or if they're all unique you could just have a text field for the
    > name of the item) and InvoiceNo.
    >
    > It would certainly be WRONG to have separate Access tables for each buyer
    > <shudder>. Instead you would have one big table with a field identifying the
    > buyer.
    >
    > You can link to the existing Excel data and run Append queries to migratethe
    > data into your normalized tables.
    > --
    >
    >              John W. Vinson [MVP]
    >  Microsoft's replacements for these newsgroups:
    >  http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    >  http://social.answers.microsoft.com/Forums/en-US/addbuz/
    >  and see alsohttp://www.utteraccess.com


    Mr. Vinson:
    Thank you for stopping this continual circular nightmare—I have indeed
    implemented your solution: I have received the effective and efficient
    results.

    Thank you for understanding my inability to see the Access solution
    and I do thank you for showing to me how to perceive the Life Lesson
    that was involved in this entanglement.

    Please stay well, Live in Peace

    G. Kennedy
    [Moderators for the benefit of this Group please edit appropriately]
     
    Reality, Aug 29, 2011
    #4
  5. On Mon, 29 Aug 2011 12:28:19 -0700 (PDT), Reality <>
    wrote:

    >Thank you for understanding my inability to see the Access solution
    >and I do thank you for showing to me how to perceive the Life Lesson
    >that was involved in this entanglement.


    <g> Glad to have been of assistance!
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Aug 29, 2011
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Yor
    Replies:
    3
    Views:
    154
    Tim Ferguson
    Nov 13, 2003
  2. BC

    Why Setup Relationships between tables?

    BC, Nov 22, 2003, in forum: Access Table Design
    Replies:
    5
    Views:
    412
    Tim Ferguson
    Nov 23, 2003
  3. Giovanni

    Relationships between Tables

    Giovanni, Mar 1, 2005, in forum: Access Table Design
    Replies:
    4
    Views:
    182
  4. Blenvid

    multiple relationships between 2 tables

    Blenvid, Oct 20, 2005, in forum: Access Table Design
    Replies:
    20
    Views:
    237
    Vincent Johns
    Oct 25, 2005
  5. gurl_bytes

    primary keys and relationships between tables

    gurl_bytes, Mar 6, 2009, in forum: Access Table Design
    Replies:
    1
    Views:
    112
    John W. Vinson
    Mar 6, 2009
  6. Rachel

    Creating relationships between tables

    Rachel, May 23, 2009, in forum: Access Table Design
    Replies:
    3
    Views:
    111
    Bernard Peek
    May 24, 2009
  7. Jack Leach

    Relationships between tables and querys

    Jack Leach, Oct 16, 2009, in forum: Access Table Design
    Replies:
    1
    Views:
    99
    Jack Leach
    Oct 17, 2009
  8. ScottMsp

    Defining Relationships/Primary Keys between two tables

    ScottMsp, Nov 2, 2009, in forum: Access Table Design
    Replies:
    4
    Views:
    201
    Bernard Peek
    Nov 3, 2009
Loading...