Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: Relationship with date

    Thinking out loud here

    Have a table

    Table1
    Table1id (pk)
    Adate
    ..
    ...
    ...

    Table2
    Table2id (pk)
    Table1id (pk form table1)
    Adate
    ..
    ..
    ..
    ..
    In table2 there will many records related to table1
    By the table1id

    So my thinking is if I add a new record to table2 and update table1 Adate record

    Then if I join table1 with table2 by the table1id and Adate that would give me the current join but will still have the history of it in table2

    Has any one done this

    Or is this a bad idea

    I'm think of a trigger when a New record is added to Table2 to chnage table1

    guess what my next post would be if I can't work out the trigger thing
    Last edited by myle; 06-25-12 at 05:55. Reason: added Trigger
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sure a trigger would do it, but why bother?

    you can always pull the current row when you retrieve the data with a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's a fine idea. I do it all the time. I have a script that generates the archive table and triggers necessary to support it.
    The advantage of keeping two separate tables (the production table and the archive table) is that even though the archive table has a full history of the production table, including its current state, most queries will only need to access the current data making the production table more efficient. It also simplifies maintaining relational integrity.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I have few tables in my current solution that represent results of a query to a client system that gives us a point in time look at a business object. Right now I have it all in the same table and we find the most recent for all our production logic. It is a HUGE PITA on the production side and makes things much more complex then they really ought to be. I'm leaning heavily on this pattern to simplify the solution and substantially increase performance.

    Conceptually it makes sense to have it all in one table because each record represents its own call to the client system. In practice, it's really messy when the production system only ever cares about the most recent row.

    I would have done it the way you're looking at if I had it to do over again. It is just too expensive to deal with historical and live data in the same table. Now I have to weigh the benefits against the cost of rework.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Thanks guys now I can put that plan into place.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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