Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: How Do You Use Effective Dating in Access

    If I am using a Look-up Table in Access what is the best to have it be one Type before a certain date and a different Type after the date.

    For Example

    Code Type Effective Date
    1 Good 1/1/1980 (Zero Date)
    2 Bad 1/1/1980
    3 Good 1/1/1980
    3 Bad 8/1/2006 (Date it changes)

    When I run my data against this I want the Type for 3 to be Good before 8/1/2006 and Bad from 8/1/2006 when the Date is another value in the Table.

    I really don't know how to do this without some ridiculous amounts of iif statements within a ton of queries.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Rich

    I am relieved by your last statement but just to be sure - you want to derive this in a query and not store it in a table right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Type 
      from daLookupTable as T
     where Code = 3
       and EffectiveDate
         = ( select max(EffectiveDate)
               from daLookupTable
              where Code = T.Code
                and EffectiveDate <= Date() )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2006
    Posts
    47
    Quote Originally Posted by pootle flump
    Hi Rich

    I am relieved by your last statement but just to be sure - you want to derive this in a query and not store it in a table right?
    How I would currently do this is that in every query that I write that goes against this table I would have to write:

    iif(Code = 3, iif (tblSomeTable.Date <8/1/2006,"Good","Bad"),Type)

    The problem with the above is that I will have to do this in multiple queries and if something changes I may not remember all the queries I did it in. Also, at this time I only have the 1 exception, however the more I get the more difficult to maintain.

    Another way I have done this when I have a large number of these changes is to essentially classify the Type for each possible date which is also very time consuming.

    Ideally, I would simple keep adding the date the Type (or Value) changes. I kind of understand the Query below but not entirely.

    Thank you for your help!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would strongly advise you derive this as and when required. In any event you will need to change your queries anyway.
    A Best of Both Worlds approach would be to have a single query that works out if a code\ date is good or bad and link this into your other queries. This way you have the logic in only one place.

    This is a common technique to flatten down your database or to propogate some sort of business logic across your application.

    What is the structure of tblSomeTable and could you post some sample data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Don't forget that you can craft your own functions and use those in the query or multiple queries. Update the function once and all queries are implicitly updated.

    In Query:
    Code:
    Result: fnType([TheDate],[TheCode])
    In Module:
    Code:
    Public Function fnType(dtVal As Variant, intCode As Variant) As String
    
    Dim strText As String
    Select Case intCode
        Case Is Null
            strText = "Bad"
        Case 3
            If dtVal < #8/1/2006# Then strText = "Good" Else strText = "Bad"
        Case Else
            strText = "Good"
    End Select
    You could even make it dynamic if, say, the cutoff for a good date or a bad date is the first day of this month, you could try something like this:
    Code:
    Dim dtFirst As Date
    dtFirst = DateValue(Format(Now(), "m/\1/yyyy"))
    note: I tried to follow what you needed - update the Case Select as required.

    hope this helps,
    tc

    PS: for other readers of this post, this is my prefered method for passing parameters to a query - use a public variable and assign it to the query using a function (you can use a function in the criteria) instead of the classic [Enter a date] prompt. Puts control back in the developer's hands.

  7. #7
    Join Date
    Jan 2006
    Posts
    47

    Thank you....

    Quote Originally Posted by pootle flump
    I would strongly advise you derive this as and when required. In any event you will need to change your queries anyway.
    A Best of Both Worlds approach would be to have a single query that works out if a code\ date is good or bad and link this into your other queries. This way you have the logic in only one place.

    This is a common technique to flatten down your database or to propogate some sort of business logic across your application.

    What is the structure of tblSomeTable and could you post some sample data?
    Thank you for your replies... I am not sure how to inlcude the sample data in the post but it comes up in many situations and I can never find the simplier way of doing it. I will try the idea of a query that all the others work off of and also the Public Function (although I haven't used it much because it confuses others who also use these databases.)

    Thank you all for all of your help!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - to give you an idea I am thinking that your lookup table could be something like:
    (___ added for formatting)
    Code:
    Code____DateCodeGoesBad 
    -----------------------
    1________01/01/2005
    3________08/01/2006
    6________09/09/2007
    7________03/06/2004
    In which case your query would be simple (air code - untested):
    Code:
    SELECT MyDataTable.Code, 
    Iif([Effective Date] >= DateCodeGoesBad, "Bad", "Good") AS Type, 
    [Effective Date]
    FROM MyDataTable Left Outer Join LookupTable ON MyDataTable.Code = LookupTable.Code
    You might even be able to get rid of the Iif() by using a Theta Join and a slightly different table structure too.... but that might be for another day

    Btw - with the greatest of respect to tc I'm not a big fan of having functions with loads of logic in them like this. I personally prefer data in tables rather than code. Also, queries typically handle native SQL better than calls to functions. However it is a perfectly viable alternative.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gentlemen, what if the thing goes good, then bad, then good...

    if so, then you need the table design the way it was described in post #1, with additional rows for each change of state

    and with that table design, the way to find out what state the thing is currently in is with the query in post #3



    moral of the story: keep an eye on when a simple requirement change ("okay, what if it goes good again, then bad again?") would force you to change your data structure

    there's nothing worse than writing code for an inflexible design (especially if the original design was flexible and you "improved" it!!)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    gentlemen, what if the thing goes good, then bad, then good...

    if so, then you need the table design the way it was described in post #1, with additional rows for each change of state

    and with that table design, the way to find out what state the thing is currently in is with the query in post #3



    moral of the story: keep an eye on when a simple requirement change ("okay, what if it goes good again, then bad again?") would force you to change your data structure

    there's nothing worse than writing code for an inflexible design (especially if the original design was flexible and you "improved" it!!)
    I did wonder if you had picked up something that I had missed.

    Totally agreed on the final point.

    I wonder what goes good then bad and then good again....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    I wonder what goes good then bad and then good again....
    odds you will see jupiter above the horizon tonight (if you want to, you know, go out looking for it tonight)

    availability of the honeymoon suite (if you want to, you know, try booking it tonight)

    likelihood of conception (if you want to, you know, try chancing it tonight)

    (the last two examples not being necessarily related)

    more?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by pootle
    I wonder what goes good then bad and then good again....
    milk? goes sour, eventually becomes cheese?

    In a reservation scheduling database (airline, hotel, etc.) you could have blackout periods (which can change) or administrative reservations (reservations made by management "just in case") that become open again. I'm sure there are others.

    tc

  13. #13
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Rudy the only thing I have an issue on in #3 is it checks against Date() which grabs the current date, and I think he needed a specific date to check against...(8/1/2006)
    Ryan
    My Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ryan, good call

    of course, my use of Date() was intended as representative

    feed in a date, get the status as of that date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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