Welcome to SPN

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

Sign Up Now!

table relationships

Discussion in 'Information Technology' started by bchasse, Jul 28, 2006.

  1. bchasse

    bchasse
    Expand Collapse
    Guest

    I am managing a project and have multiple spreadsheets (originally in Excel
    that were imported into Access) all of which share a common field. How do I
    set up a relationship between all the spreadsheets so as to ensure that if I
    update a common field in one spreadsheet it will automatically update the
    same field in all the other spreadsheets? I assume there is a way to do this
    and that not every database is based strictly on one super huge spreadsheet
    with hundreds of columns. I'm so frustrated with this. Please help!
    Thanks!!!
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Do not think in terms of spreadsheets when you're dealing with Access. While
    the interface may look similar, they are definitely two very different
    applications. What you're trying to do doesn't really make sense in terms of
    databases.

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


    "bchasse" <bchasse@discussions.microsoft.com> wrote in message
    news:CEC2C0D7-2B22-438B-A56B-C16F5420AF03@microsoft.com...
    >I am managing a project and have multiple spreadsheets (originally in Excel
    > that were imported into Access) all of which share a common field. How do
    > I
    > set up a relationship between all the spreadsheets so as to ensure that if
    > I
    > update a common field in one spreadsheet it will automatically update the
    > same field in all the other spreadsheets? I assume there is a way to do
    > this
    > and that not every database is based strictly on one super huge
    > spreadsheet
    > with hundreds of columns. I'm so frustrated with this. Please help!
    > Thanks!!!
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 4 Jul 2006 13:56:01 -0700, bchasse
    <bchasse@discussions.microsoft.com> wrote:

    >I am managing a project and have multiple spreadsheets (originally in Excel
    >that were imported into Access) all of which share a common field. How do I
    >set up a relationship between all the spreadsheets so as to ensure that if I
    >update a common field in one spreadsheet it will automatically update the
    >same field in all the other spreadsheets? I assume there is a way to do this
    >and that not every database is based strictly on one super huge spreadsheet
    >with hundreds of columns. I'm so frustrated with this. Please help!
    >Thanks!!!


    That's not how relationships work, nor how they are intended to work.

    As Douglas says, a database IS NOT A SPREADSHEET. They are
    *different*, and require different approaches and a different mindset!

    I'd suggest that if you're not familiar with database design and the
    concept of database normalization, that you read some of the resources
    at
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

    particularly the Database Design 101 link. I very strongly suspect
    that you will need several tables which will NOT closely resemble your
    current spreadsheets, but will contain the same information arranged
    differently. Any given piece of information will typically be stored
    ONCE only, in only one table; there will be links between the tables,
    but they almost surely will NOT all be the "same field". When you need
    to see a field in conjunction with other fields, you don't need to
    store it in both tables - instead you will create a Query joining the
    two tables.

    John W. Vinson[MVP]
     
  5. bchasse

    bchasse
    Expand Collapse
    Guest

    I probably should have used the word "tables" (instead of spreadsheet) as
    this is actually what I have. In other words, I have multiple "tables," all
    of which share a common field (e.g., ID, site name, etc.). I want to set
    them up such that if I update something in one table (and that update occurs
    in a field that is common with another table), that it will update it in all
    locations, not just in the table that the change occured in. This is where
    I'm running into problems. I don't know how to link them such that updates
    will occur in all common fields across all tables.

    Any help would be GREATLY appreciated. Thanks!!!

    "John Vinson" wrote:

    > On Tue, 4 Jul 2006 13:56:01 -0700, bchasse
    > <bchasse@discussions.microsoft.com> wrote:
    >
    > >I am managing a project and have multiple spreadsheets (originally in Excel
    > >that were imported into Access) all of which share a common field. How do I
    > >set up a relationship between all the spreadsheets so as to ensure that if I
    > >update a common field in one spreadsheet it will automatically update the
    > >same field in all the other spreadsheets? I assume there is a way to do this
    > >and that not every database is based strictly on one super huge spreadsheet
    > >with hundreds of columns. I'm so frustrated with this. Please help!
    > >Thanks!!!

    >
    > That's not how relationships work, nor how they are intended to work.
    >
    > As Douglas says, a database IS NOT A SPREADSHEET. They are
    > *different*, and require different approaches and a different mindset!
    >
    > I'd suggest that if you're not familiar with database design and the
    > concept of database normalization, that you read some of the resources
    > at
    > http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
    >
    > particularly the Database Design 101 link. I very strongly suspect
    > that you will need several tables which will NOT closely resemble your
    > current spreadsheets, but will contain the same information arranged
    > differently. Any given piece of information will typically be stored
    > ONCE only, in only one table; there will be links between the tables,
    > but they almost surely will NOT all be the "same field". When you need
    > to see a field in conjunction with other fields, you don't need to
    > store it in both tables - instead you will create a Query joining the
    > two tables.
    >
    > John W. Vinson[MVP]
    >
    >
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    That's the point John & I are trying to make.

    In a properly designed database, you shouldn't have to update fields in
    multiple tables: each piece of data will exist in one, and only one,
    location.

    Yes, there is something called Cascade Updating through relationships, which
    means that if you've related the primary key in table 1 to a foreign key in
    table 2, changes to the primary key in table 1 will automatically propagate
    to the foreign key in table 2. However, in my mind it's actually a bad thing
    to be changing the values of primary keys.

    Maybe you should give an example of what you're trying to do.

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


    "bchasse" <bchasse@discussions.microsoft.com> wrote in message
    news:A8C6BE55-936B-438D-ADAF-F4F4851E344F@microsoft.com...
    >I probably should have used the word "tables" (instead of spreadsheet) as
    > this is actually what I have. In other words, I have multiple "tables,"
    > all
    > of which share a common field (e.g., ID, site name, etc.). I want to set
    > them up such that if I update something in one table (and that update
    > occurs
    > in a field that is common with another table), that it will update it in
    > all
    > locations, not just in the table that the change occured in. This is
    > where
    > I'm running into problems. I don't know how to link them such that
    > updates
    > will occur in all common fields across all tables.
    >
    > Any help would be GREATLY appreciated. Thanks!!!
    >
    > "John Vinson" wrote:
    >
    >> On Tue, 4 Jul 2006 13:56:01 -0700, bchasse
    >> <bchasse@discussions.microsoft.com> wrote:
    >>
    >> >I am managing a project and have multiple spreadsheets (originally in
    >> >Excel
    >> >that were imported into Access) all of which share a common field. How
    >> >do I
    >> >set up a relationship between all the spreadsheets so as to ensure that
    >> >if I
    >> >update a common field in one spreadsheet it will automatically update
    >> >the
    >> >same field in all the other spreadsheets? I assume there is a way to do
    >> >this
    >> >and that not every database is based strictly on one super huge
    >> >spreadsheet
    >> >with hundreds of columns. I'm so frustrated with this. Please help!
    >> >Thanks!!!

    >>
    >> That's not how relationships work, nor how they are intended to work.
    >>
    >> As Douglas says, a database IS NOT A SPREADSHEET. They are
    >> *different*, and require different approaches and a different mindset!
    >>
    >> I'd suggest that if you're not familiar with database design and the
    >> concept of database normalization, that you read some of the resources
    >> at
    >> http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
    >>
    >> particularly the Database Design 101 link. I very strongly suspect
    >> that you will need several tables which will NOT closely resemble your
    >> current spreadsheets, but will contain the same information arranged
    >> differently. Any given piece of information will typically be stored
    >> ONCE only, in only one table; there will be links between the tables,
    >> but they almost surely will NOT all be the "same field". When you need
    >> to see a field in conjunction with other fields, you don't need to
    >> store it in both tables - instead you will create a Query joining the
    >> two tables.
    >>
    >> John W. Vinson[MVP]
    >>
    >>
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 4 Jul 2006 15:54:01 -0700, bchasse
    <bchasse@discussions.microsoft.com> wrote:

    >In other words, I have multiple "tables," all
    >of which share a common field (e.g., ID, site name, etc.). I want to set
    >them up such that if I update something in one table (and that update occurs
    >in a field that is common with another table), that it will update it in all
    >locations, not just in the table that the change occured in.


    Again... *that's not how Tables work*.

    Relational tables use the "Grandmother's Pantry Principle": "a place -
    ONE place! - for everything, everything in its place".

    If you have the same information stored in multiple tables - other
    than the linking ID field - then *YOUR DESIGN IS WRONG*.

    You should store the field once, and once only, and then use Queries
    to link the tables.

    As Douglas has requested, please give us a little help here. What is
    stored in these tables? How are the tables related? Why so many
    tables?

    John W. Vinson[MVP]
     
  8. Pizza

    Pizza
    Expand Collapse
    Guest

    If you place a Form on top of the table with fields relating to the spicific
    table and these fields is similar to those on other forms(also from tables)
    you can try and use the SetValue Function, but remember for the Setvalue to
    work the form of which you want to update a field needs to be open. You can
    do this by using event procedures. For Example on the After Update, or lost
    focus property. Use the stLinkCriteria Function, DoCmd.Openform also
    DoCmd.RunMacro combinations of these will set the value of the field you want
    to set just remember to Automate the close of the form as well. Try this and
    let me know if it works

    "John Vinson" wrote:

    > On Tue, 4 Jul 2006 15:54:01 -0700, bchasse
    > <bchasse@discussions.microsoft.com> wrote:
    >
    > >In other words, I have multiple "tables," all
    > >of which share a common field (e.g., ID, site name, etc.). I want to set
    > >them up such that if I update something in one table (and that update occurs
    > >in a field that is common with another table), that it will update it in all
    > >locations, not just in the table that the change occured in.

    >
    > Again... *that's not how Tables work*.
    >
    > Relational tables use the "Grandmother's Pantry Principle": "a place -
    > ONE place! - for everything, everything in its place".
    >
    > If you have the same information stored in multiple tables - other
    > than the linking ID field - then *YOUR DESIGN IS WRONG*.
    >
    > You should store the field once, and once only, and then use Queries
    > to link the tables.
    >
    > As Douglas has requested, please give us a little help here. What is
    > stored in these tables? How are the tables related? Why so many
    > tables?
    >
    > John W. Vinson[MVP]
    >
     

Share This Page