Results 1 to 8 of 8

Thread: update a field

  1. #1
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Unanswered: update a field

    hello,
    I need to write an update query in access that will update a fild based on the calculation of a week data from another field. Meening:

    I need to update Col1 to the week of the year 2005
    Col1 should have a value of 2, 51, 52 and so on based on the calc. value of Col2.

    Col 1 Col2
    0 01022005 = week 2
    0 12142005 = week 51
    0 12222005 = week 52
    0 12292005

    Please help me, I do not know how to set this query?

    Thank you so much

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    sql> SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;

    HTH
    Gregg

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    The 'WW' option (Number of weeks since Jan 1) is different from the 'IW' option (which is the ISO week), therefore you may want to consider using 'IW':
    Code:
    SQL> select  dt, to_char(dt,'WW') ww, to_char(dt,'IW') iw
      2   from (select to_date('15122005','DDMMYYYY')+(rownum-1) dt
      3*           from all_objects where rownum <11)
    SQL>/
    
    DT          WW IW
    ----------- -- --
    15-DEC-2005 50 50
    16-DEC-2005 50 50
    17-DEC-2005 51 50
    18-DEC-2005 51 50
    19-DEC-2005 51 51
    20-DEC-2005 51 51
    21-DEC-2005 51 51
    22-DEC-2005 51 51
    23-DEC-2005 51 51
    24-DEC-2005 52 51
    
    10 rows selected.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Thank you , how can I appliy this to update data in a ms access table?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how can I appliy this to update data in a ms access table?
    Post this in ACCESS forum & not Oracle forum.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Isn't there an executesql command or something like that in Access...
    Execute the sql command from the native sql ...

    HTH
    Gregg

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    In Access you do something like:
    Code:
    UPDATE MyTable
       SET col1 = datepart('ww',datevalue(mid(col2,3,2)
                                  +'/'+left(col2,2)+'/'+right(col2,4)));

    PS: If col2 is DATE type then just replace the "datevalue" function with col2.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Thank you very much

Posting Permissions

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