Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    UPDATING TABLES (2000)

    I would like some information in regards to the following question.
    I have a database with 5 tables that are the same in structure:

    1. Baseline
    2. 3 Months
    3. 6 Months
    4. 9 Months
    5. 12 Months

    When I enter data (Client ID-not an autonumber, First Name and Last Name) from a form into the first table (Baseline), I would like the database to automatically update the same 3 fields on the remaining 4 tables.
    Easy Access

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATING TABLES (2000)

    If you store the same info in 5 tables, you have a serious table design problem.
    Francois

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: UPDATING TABLES (2000)

    To make this happen you probably want Append queries, either as saved queries or sql statements that get executed whenever you have finished entering the data in the baseline.

    I agree with Francois that you probably should not be doing this, but if you are determined to.....

    Instead have a client table with the fields (Client ID-not an autonumber, First Name and Last Name) and in each of the other tables put a clientid as a foreign key.

    You might even be able to have only other table, and have a field in it that identifies a record as baseline, 3 months etc.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UPDATING TABLES (2000)

    My database must have all the information that is stored in it on one table. The information that is stored in it will be downloaded out of it for another application for 5 separate periods. That is why the same Client #, First Name and Last Name must be in each database.
    Thanks for the information.
    Easy Access

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATING TABLES (2000)

    See the answer from John.
    Even if you want to export the data, you can export queries.
    Still no need to store the data 5 times.
    Francois

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UPDATING TABLES (2000)

    Thanks for the information John.
    How would I execute the saved query or sql statement once I have formatted them?
    Easy Access

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: UPDATING TABLES (2000)

    If you have saved some queries, qryAppend3months, qryappend6months etc

    then in the afterinsert event of you form put code like this

    docmd.openquery "qryAppend3months"
    docmd.openquery "qryAppend6months" etc

    If you are not comfortable with code, you could build a macro to do the same job, and run the macro in the same event.

    Do you know how to buidl the append queries?

    Francois is still right though. You don't need all these copies of the client name, and it would probably be a useful exercise to work out how to do the job you want with only copy of this info.
    Regards
    John



Posting Permissions

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