Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unanswered: Updating fields based on date criteria

    Hi

    I have an access database which amongst others, in teh subscription table has 2 fields

    Sub_due ( possible values V, R1, R2, D, S, X)
    Renewal date ( date format eg 01/09/2014)

    I want to run an Update query to change values for the Sub_due field based on the Renewal date
    and the current date in three steps
    This is the logic

    If

    the Renewal date is within the Current date Plus 2months, Update Sub_due to R1

    Else if

    the Renewal date is within the Current date Plus 1 months, Update Sub_due to R2
    else ( ie all others)
    Update Sub_due to V

    where sub_type= [subType] and langauge=[Langauge] and subdue <> "S"

    It would be great if the parameters current date, Langauge and SubType were asked and supplied
    each time the query run.



    This was my attempt, but I have failed in syntax. I am not very good with SQL.



    UPDATE Subscription
    SET Subscription.sub_due = "R1"
    WHERE (((Subscription.sub_due)<>'S') AND
    ((Subscription.renewal_date) Between #1/09/2014# And #30/11/2014#) AND
    ((Subscription.language)=[Language Version]) AND ((Subscription.sub_type)="Newsletter"));



    UPDATE Subscription
    SET Subscription.sub_due = "R2"
    WHERE (((Subscription.sub_due)<>'S') AND
    ((Subscription.renewal_date) Between #01/9/2014# And #30/9/2014#) AND
    ((Subscription.language)=[Language version]) AND ((Subscription.sub_type)="Newsletter"));



    UPDATE Subscription
    SET Subscription.sub_due = "V"
    WHERE (((Subscription.sub_due)<>'S') AND
    ((Subscription.renewal_date)>[PubDate]) AND ((Subscription.sub_due) not like (R1,R2,S) AND
    ((Subscription.language)=[Language Version]) AND
    ((Subscription.sub_type)="Newsletter"));


    Can anyone help?

    Thanks.

    Talat

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Talat52 View Post
    Hi

    I have an access database which amongst others, in teh subscription table has 2 fields

    Sub_due ( possible values V, R1, R2, D, S, X)
    Renewal date ( date format eg 01/09/2014)
    lets hope that 'Renewal Date' is a date and not a string column. if not re design the db so that it is, and whilst you are at it get rid of any spaces in table and column names

    however why would you want to do this, you can always get the number of time periods using the appropriate datetime function.. that is just one of the many reasons WHY date/time data shoudl be stored in a datetime datatype column

    what you are doing is storing derived data...... it violates normalisation and it build a possible source of error in your application.. primarily what happens when you forget to run your update query(ies). the risk is that a user will see the code and think everythign is OK.

    OK so what is the 'right' way to do this.
    Well I'd argue its use the datediff or datesub/dateadd function to return the number of days and use that number in your application to do wahtever you need

    Code:
    SELECT my,
      column,
      list,
      datediff("m", [Renewal Date], date()) as NoMonthsToRenewal
      FROM Subscription
    your where clause to be added to the above will return rows whose renewal is within the next 2 months
    Code:
    WHERE dateadd("m",2,[Renewal Date]) > date()
    the square brackets around [Renewal Date] are there becuase you've used spaces in table/column names

    you could instead retrieve the number of days in place of months
    you could refine your where clause to say exclude rows whose membership has already elapsed

    in an ideal world you would define a subscription / membership table, which would also defien the validity of the subscription and store the date the subscriptuion was last changed.
    Last edited by healdem; 09-03-14 at 07:57.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Posts
    3
    Hi,

    Thanks for the quick response.

    Firstly, the Renewal_date field and any other dates are are in Date format. So no problems there.

    Secondly, although I referred to some fields by their common language name, in the design of the database they are without any spaces. So Sub_due, Renewal_date, etc. are written with an underscore.

    You have given me some pointers for which I thank you. However, I am not very good at writing SQL code and am still at a bit of a loss how I put all this together.

    I will have a go, but I have little confidence in myself that I can. So if you, or anyone else can help me a little further with this, particularly the part where parameters are asked for at runtime and the Update is made to all there options ( V,R1, and R2) that would be graetly appreciated.

    By the way; The sub-due code will be used after running this code prior to producing labels using a Crystal report which checks the Sub_due field.

    Thanks.

    Talat.








    Quote Originally Posted by healdem View Post
    lets hope that 'Renewal Date' is a date and not a string column. if not re design the db so that it is, and whilst you are at it get rid of any spaces in table and column names

    however why would you want to do this, you can always get the number of time periods using the appropriate datetime function.. that is just one of the many reasons WHY date/time data shoudl be stored in a datetime datatype column

    what you are doing is storing derived data...... it violates normalisation and it build a possible source of error in your application.. primarily what happens when you forget to run your update query(ies). the risk is that a user will see the code and think everythign is OK.

    OK so what is the 'right' way to do this.
    Well I'd argue its use the datediff or datesub/dateadd function to return the number of days and use that number in your application to do wahtever you need

    Code:
    SELECT my,
      column,
      list,
      datediff("m", [Renewal Date], date()) as NoMonthsToRenewal
      FROM Subscription
    your where clause to be added to the above will return rows whose renewal is within the next 2 months
    Code:
    WHERE dateadd("m",2,[Renewal Date]) > date()
    the square brackets around [Renewal Date] are there becuase you've used spaces in table/column names

    you could instead retrieve the number of days in place of months
    you could refine your where clause to say exclude rows whose membership has already elapsed

    in an ideal world you would define a subscription / membership table, which would also defien the validity of the subscription and store the date the subscriptuion was last changed.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dont use V1, R1 & R2... you are violating normal form... its derived data its a no no......
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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