Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Referancing other tables

Discussion in 'Information Technology' started by pltaylor3@gmail.com, Jul 28, 2006.

  1. pltaylor3@gmail.com

    pltaylor3@gmail.com
    Expand Collapse
    Guest

    I have three tables. The first one has just a single list of parts.
    The second one has a parts that correspond to the first table and
    various properties that those parts have. (one to many relation with
    the first table). The problem lies in the third table. The first
    column lists the possible parts referancing from the first table (no
    problem here) I would like the second colum to read the first and then
    give me the various part properties that are defined for that part in
    the second table.

    For instance
    1st table

    Calipers
    Hubs
    Rotors
    Spindles

    2nd table

    Part Type Name Part Property Name
    Calipers Manufacturer
    Hubs manufacturer
    Hubs offset

    3rd table

    Part Type Name Part Property Name Part Serial # Part Property Value

    any ideas?
    thanks in advance
     
  2. Loading...

    Similar Threads Forum Date
    India 11 Constables Get Pregnant at Training School Breaking News Apr 26, 2011
    Warm Up with Winter Vegetables (Vegetarian Casseroles) Cooking & Recipies Feb 7, 2011
    India Two constables arrested for beating, extortion Breaking News Jul 27, 2010
    History Cleaning Out the Augean Stables Punjab, Punjabi, Punjabiyat Aug 25, 2009
    Sikh News Cold weather damages sugarcane, vegetables in Punjab (New Kerala) Breaking News Feb 1, 2008

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Create a query that joins the tables together, and use the query wherever
    you would otherwise have used the table.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <pltaylor3@gmail.com> wrote in message
    news:1151323128.229589.199920@i40g2000cwc.googlegroups.com...
    > I have three tables. The first one has just a single list of parts.
    > The second one has a parts that correspond to the first table and
    > various properties that those parts have. (one to many relation with
    > the first table). The problem lies in the third table. The first
    > column lists the possible parts referancing from the first table (no
    > problem here) I would like the second colum to read the first and then
    > give me the various part properties that are defined for that part in
    > the second table.
    >
    > For instance
    > 1st table
    >
    > Calipers
    > Hubs
    > Rotors
    > Spindles
    >
    > 2nd table
    >
    > Part Type Name Part Property Name
    > Calipers Manufacturer
    > Hubs manufacturer
    > Hubs offset
    >
    > 3rd table
    >
    > Part Type Name Part Property Name Part Serial # Part Property Value
    >
    > any ideas?
    > thanks in advance
    >
     
  4. pltaylor3@gmail.com

    pltaylor3@gmail.com
    Expand Collapse
    Guest

    Now I'm going to show just how much of a beginner I am. How would i
    set the query up? Just reference the first table then reference the
    second table with a where statement? What would that where statement
    look like?
    thanks for your help
    Perry



    Douglas J Steele wrote:
    > Create a query that joins the tables together, and use the query wherever
    > you would otherwise have used the table.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > <pltaylor3@gmail.com> wrote in message
    > news:1151323128.229589.199920@i40g2000cwc.googlegroups.com...
    > > I have three tables. The first one has just a single list of parts.
    > > The second one has a parts that correspond to the first table and
    > > various properties that those parts have. (one to many relation with
    > > the first table). The problem lies in the third table. The first
    > > column lists the possible parts referancing from the first table (no
    > > problem here) I would like the second colum to read the first and then
    > > give me the various part properties that are defined for that part in
    > > the second table.
    > >
    > > For instance
    > > 1st table
    > >
    > > Calipers
    > > Hubs
    > > Rotors
    > > Spindles
    > >
    > > 2nd table
    > >
    > > Part Type Name Part Property Name
    > > Calipers Manufacturer
    > > Hubs manufacturer
    > > Hubs offset
    > >
    > > 3rd table
    > >
    > > Part Type Name Part Property Name Part Serial # Part Property Value
    > >
    > > any ideas?
    > > thanks in advance
    > >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Create a new query. Add the tables to it. If you haven't got relationships
    set up between the tables, draw lines to connect them (select the field(s)
    in common from one table and drag them onto the other table).

    Queries are a fundamental part of Access. If you're not familiar with them,
    you really need to be.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <pltaylor3@gmail.com> wrote in message
    news:1151330255.764516.197720@c74g2000cwc.googlegroups.com...
    > Now I'm going to show just how much of a beginner I am. How would i
    > set the query up? Just reference the first table then reference the
    > second table with a where statement? What would that where statement
    > look like?
    > thanks for your help
    > Perry
    >
    >
    >
    > Douglas J Steele wrote:
    > > Create a query that joins the tables together, and use the query

    wherever
    > > you would otherwise have used the table.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > <pltaylor3@gmail.com> wrote in message
    > > news:1151323128.229589.199920@i40g2000cwc.googlegroups.com...
    > > > I have three tables. The first one has just a single list of parts.
    > > > The second one has a parts that correspond to the first table and
    > > > various properties that those parts have. (one to many relation with
    > > > the first table). The problem lies in the third table. The first
    > > > column lists the possible parts referancing from the first table (no
    > > > problem here) I would like the second colum to read the first and then
    > > > give me the various part properties that are defined for that part in
    > > > the second table.
    > > >
    > > > For instance
    > > > 1st table
    > > >
    > > > Calipers
    > > > Hubs
    > > > Rotors
    > > > Spindles
    > > >
    > > > 2nd table
    > > >
    > > > Part Type Name Part Property Name
    > > > Calipers Manufacturer
    > > > Hubs manufacturer
    > > > Hubs offset
    > > >
    > > > 3rd table
    > > >
    > > > Part Type Name Part Property Name Part Serial # Part Property

    Value
    > > >
    > > > any ideas?
    > > > thanks in advance
    > > >

    >
     
  6. pltaylor3@gmail.com

    pltaylor3@gmail.com
    Expand Collapse
    Guest

    Douglas-
    Maybe I am not phrasing myself clear enough. I am pretty familiar
    w/ queries. What I am not sure about is how to get the query to have a
    drop down menu that shows options that are the properties in the second
    table according to what item was put in the first column. From my
    example above i would like a drop down menu for the first column
    (already have that) that shows

    Calipers
    Hubs
    Rotors
    Spindles

    the second column would have a drop down menu that would be dependent
    on the first colum....i.e. if i select Calipers in the first column i
    want the second column to show manufacturers in a drop down (from the
    second table above) or if i select hubs in the first column i would
    like
    manufacturer
    offset
    to appear. Does this make sense? How can i do this. Also i know i am
    missing something simple here but when i write my query i can not add
    any new records to the query and therefore the underlying tables. What
    preferance do i select on the query to allow to add records to the
    query and the underlying tables?
    thanks


    Douglas J Steele wrote:
    > Create a new query. Add the tables to it. If you haven't got relationships
    > set up between the tables, draw lines to connect them (select the field(s)
    > in common from one table and drag them onto the other table).
    >
    > Queries are a fundamental part of Access. If you're not familiar with them,
    > you really need to be.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > <pltaylor3@gmail.com> wrote in message
    > news:1151330255.764516.197720@c74g2000cwc.googlegroups.com...
    > > Now I'm going to show just how much of a beginner I am. How would i
    > > set the query up? Just reference the first table then reference the
    > > second table with a where statement? What would that where statement
    > > look like?
    > > thanks for your help
    > > Perry
    > >
    > >
    > >
    > > Douglas J Steele wrote:
    > > > Create a query that joins the tables together, and use the query

    > wherever
    > > > you would otherwise have used the table.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > <pltaylor3@gmail.com> wrote in message
    > > > news:1151323128.229589.199920@i40g2000cwc.googlegroups.com...
    > > > > I have three tables. The first one has just a single list of parts.
    > > > > The second one has a parts that correspond to the first table and
    > > > > various properties that those parts have. (one to many relation with
    > > > > the first table). The problem lies in the third table. The first
    > > > > column lists the possible parts referancing from the first table (no
    > > > > problem here) I would like the second colum to read the first and then
    > > > > give me the various part properties that are defined for that part in
    > > > > the second table.
    > > > >
    > > > > For instance
    > > > > 1st table
    > > > >
    > > > > Calipers
    > > > > Hubs
    > > > > Rotors
    > > > > Spindles
    > > > >
    > > > > 2nd table
    > > > >
    > > > > Part Type Name Part Property Name
    > > > > Calipers Manufacturer
    > > > > Hubs manufacturer
    > > > > Hubs offset
    > > > >
    > > > > 3rd table
    > > > >
    > > > > Part Type Name Part Property Name Part Serial # Part Property

    > Value
    > > > >
    > > > > any ideas?
    > > > > thanks in advance
    > > > >

    > >
     
  7. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    This is not something you can do within a table. You can only do this with
    a form because you need an event to trigger a requery to synchronize the
    second combo to the first and tables do not expose events. The structure is
    called "cascading combos" and you should find lots of suggestions if you
    search on that phrase. Essentially you use criteria in the second combo
    that references the value in the first:
    Select ...
    From ..
    Where SomeField = Forms!yourformname!firstcomboname;

    Then in the AfterUpdate event of the FIRST combo, you requery the SECOND
    combo.

    Me.SecondCombo.Requery


    <pltaylor3@gmail.com> wrote in message
    news:1151342714.833398.155230@u72g2000cwu.googlegroups.com...
    > Douglas-
    > Maybe I am not phrasing myself clear enough. I am pretty familiar
    > w/ queries. What I am not sure about is how to get the query to have a
    > drop down menu that shows options that are the properties in the second
    > table according to what item was put in the first column. From my
    > example above i would like a drop down menu for the first column
    > (already have that) that shows
    >
    > Calipers
    > Hubs
    > Rotors
    > Spindles
    >
    > the second column would have a drop down menu that would be dependent
    > on the first colum....i.e. if i select Calipers in the first column i
    > want the second column to show manufacturers in a drop down (from the
    > second table above) or if i select hubs in the first column i would
    > like
    > manufacturer
    > offset
    > to appear. Does this make sense? How can i do this. Also i know i am
    > missing something simple here but when i write my query i can not add
    > any new records to the query and therefore the underlying tables. What
    > preferance do i select on the query to allow to add records to the
    > query and the underlying tables?
    > thanks
    >
    >
    > Douglas J Steele wrote:
    >> Create a new query. Add the tables to it. If you haven't got
    >> relationships
    >> set up between the tables, draw lines to connect them (select the
    >> field(s)
    >> in common from one table and drag them onto the other table).
    >>
    >> Queries are a fundamental part of Access. If you're not familiar with
    >> them,
    >> you really need to be.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> <pltaylor3@gmail.com> wrote in message
    >> news:1151330255.764516.197720@c74g2000cwc.googlegroups.com...
    >> > Now I'm going to show just how much of a beginner I am. How would i
    >> > set the query up? Just reference the first table then reference the
    >> > second table with a where statement? What would that where statement
    >> > look like?
    >> > thanks for your help
    >> > Perry
    >> >
    >> >
    >> >
    >> > Douglas J Steele wrote:
    >> > > Create a query that joins the tables together, and use the query

    >> wherever
    >> > > you would otherwise have used the table.
    >> > >
    >> > > --
    >> > > Doug Steele, Microsoft Access MVP
    >> > > http://I.Am/DougSteele
    >> > > (no e-mails, please!)
    >> > >
    >> > >
    >> > > <pltaylor3@gmail.com> wrote in message
    >> > > news:1151323128.229589.199920@i40g2000cwc.googlegroups.com...
    >> > > > I have three tables. The first one has just a single list of
    >> > > > parts.
    >> > > > The second one has a parts that correspond to the first table and
    >> > > > various properties that those parts have. (one to many relation
    >> > > > with
    >> > > > the first table). The problem lies in the third table. The first
    >> > > > column lists the possible parts referancing from the first table
    >> > > > (no
    >> > > > problem here) I would like the second colum to read the first and
    >> > > > then
    >> > > > give me the various part properties that are defined for that part
    >> > > > in
    >> > > > the second table.
    >> > > >
    >> > > > For instance
    >> > > > 1st table
    >> > > >
    >> > > > Calipers
    >> > > > Hubs
    >> > > > Rotors
    >> > > > Spindles
    >> > > >
    >> > > > 2nd table
    >> > > >
    >> > > > Part Type Name Part Property Name
    >> > > > Calipers Manufacturer
    >> > > > Hubs manufacturer
    >> > > > Hubs offset
    >> > > >
    >> > > > 3rd table
    >> > > >
    >> > > > Part Type Name Part Property Name Part Serial # Part Property

    >> Value
    >> > > >
    >> > > > any ideas?
    >> > > > thanks in advance
    >> > > >
    >> >

    >
     

Share This Page