Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: How to Prepare a Weekly Schedule

    Dear Seniors,

    I am new to access database and I would like to ask your help on this case.

    I have a table with more than 3000 Documents and Planned Date of Issue.

    Doc No IFR IFA IFD
    PID 30-10-13 30-12-13 25-Jan-14
    PFD 20-10-13 25-12-13 30-Jan-14
    UFD 25-10-13 30-12-13 27-Jan-14

    Now I want to create a Weekly Plan for these documents to be issued.

    Could you please help me to solve these Issue.

    I have also created a Table for my calender Cut off date.

    Thanks and regards
    R. Vadivelan

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Could you please help me to solve these Issue.
    ...certainly...

    but you'll need to provide some information
    ifr, ifa & ifd stand for what?
    you have a date table, any chance of letting us know what that table is what it contains
    what are you table names

    your column names
    how do you know what defines a week

    what you want is almost certainly going to use a join (that JOINS your documents table to your dates table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I have is this function

    Code:
    Function WEEKEND(dat) As Date
    If IsNull(dat) Then Exit Function
    dat = DateSerial(Year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then
    WEEKEND = (dat - dat Mod 7 + 7) + 1
    Else
    WEEKEND = dat + 1
    End If
    End Function
    place it in a Module

    then in a query

    weekend:weekend([datefeildName])

    it will return the saturday date of the datefeildName

    with the guys here they help write the SQL version

    CREATE FUNCTION [dbo].[weekend] (@we datetime)
    RETURNS datetime
    AS
    BEGIN
    RETURN DateAdd(dd, (-1 * Coalesce(NullIf(DateDiff(dd, 5, @we) % 7, 0), 7)) + 7, @we)
    END

    now if sat is not you weekend then just add some days to make it right
    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.

  4. #4
    Join Date
    Oct 2013
    Posts
    163
    Dear All,

    Thanks for your immediate reply.

    Basically I am working in a Engineering Project, where we have to submit the deliverable to client based on our schedule. Since we had created a register in Access I would like to create the weekly plan also in access.

    To make it clear herewith I had enclosed the sample database for your reference. I had almost achieved my target using cross tab report, but instead of displaying months in top, I want to display the week nos as per my calendar.

    I also viewed some post I can add week number in the MDR E Table using vlookup from calendar table and I can use the cross tab report for the final result, but I am unable to do the same..

    Could you please help me to solve this issue? My Main motto is to take the weekly plan from this database.

    Thanks and Regards
    R. Vadivelan
    Last edited by velu130486; 10-15-13 at 02:34.

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Dear All,

    Sorry for the wrong attachment. Please refer the corrected one.

    Thanks and Regards
    R. Vadivelan
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so lets assume that you want to extract all documents and find the weeknumber based on internal 7_IFD
    so you need to use a join, although in this case you'd be better off using a where clause, the old style for JOINing tables

    WHERE Mydatecolumn >= calendar.from and mydatecolumn <= calendar.to
    effetcively we are saying I want to find the matching row in calendar whose start date (from) is less that or equal to the specified date AND whoe end date (to) is greater then or equal to our speficied date column

    you need an order (i guess)
    ORDER BY week number, mydatecolumn

    so its going to be sort of:-
    SELECT my, comma, separated, column, list, [Week Num]
    FROM [MDR Rev E_Tbl], Calendar_Tbl
    WHERE Calendar_Tbl.From <= [MDR Rev E_Tbl].[INTERNAL 7_IFD] and Calendar_Tbl.to >= [MDR Rev E_Tbl].[INTERNAL 7_IFD]
    ORDER BY [week num], [INTERNAL 7_IFD];

    having extracted the week number you should then be able to push that query into a pivot column and treat it similarly to your previous month based pivot query

    incindentally if you get to do this sort of thing again consider spending time designing the system correctly. spreadsheets have their uses but they are pretty bad at storing data that is going to be used as a reference. of if this has been designed in Access you need to get away from the spread sheet mentality. spreadsheets are great at analysing data they are lousy and possibly dangerous at storing data

    you probably need to bone up on normalisation
    ferinstance discipline should probably be a table with a FK from your docuemtns table to the PK in the new table. heck you'v e gone part way there by including a two digit code inform of the actual value of discipline
    likewise with ctr l1

    you made it harder for yourself by including spaces in you column & table names.
    you column names are iffy, whenever I see similar looking columns differentiated by a number eg internalx.... Im alawasy suspicious of iffy design. its compounded in your dates table which contains superfluous columns
    eg
    you don't need to pull out the month name and year, they can be derived from a date
    you don't need a from AND to date, you can derive one from the other.
    de facto the end of the previous period is the day before the next period

    if all you are using the calendar table for is a week number you can eliminate the table altogether using a sneaky bit of maths, as myle alluded to above

    MS Access uses number to store a date and or time. although its not recommended you can do maths on date values
    to derive the week number, subtract your first date from the date column then divide by (using integer division)
    SELECT (mydatecolumn - cdate("2013/1/14"))\7 AS Weekno, mydatecolumn
    FROM Table1
    ORDER BY (mydatecolumn - cdate("2013/1/14"))\7, mydatecolumn;

    for ease of code Ive actually subtracted the week before as the start date, but you could use
    SELECT ((mydatecolumn - cdate("2013/1/23"))\7) + 1 AS Weekno, mydatecolumn
    FROM Table1
    ORDER BY ((mydatecolumn - cdate("2013/1/23"))\7)+1, mydatecolumn;
    the advantage of this approach is that you will always get 7 days per week number, as opposed to your current table which has a 5 day and then 7 day weeks per week number
    you don't have to put in every date, you don't have to worry about data errors, you don't need a separate table. if some bright spark extends the contract you don't need to put in additional dates. the downside is that you loose any form of validation of a date. ferinstance you coudl enter a date that doesn't exist, if you are using forms you could validate that date is within the current calendar range

    incidentally you are using a reserved word 'from' in your tables, although you will probably get away with it for this purpose its something to bear in mind for future

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Thanks HTH for your detailed reply.

    Actually I am new user to access and I got this database from my boss, so I am not allowed to make any changes. However I will note down your comments and incorporate the same in future.

    Could you please refer me a website where we can learn about access in more details because I am very eager to learn access and use the same in my projects. Also any manuals or points to remember while creating a database.

    In fact I was asking this query, because my boss has already created a weekly plan based on this database and distributed a PDF copy. It was quite useful to me and I want to create the same in Access with additional conditions.

    Instead of referring from other table is it possible to add a field and calculate the Week Number in the same table. (Week Number means Project Week Number, why I am asking this query is because I used

    DatePart("ww",date) to calculate the week number, however when the year changes from 2013 to 2014 my week number starts from 0. So if you tell me to calculate the week no from the fixed date say 21-Jan-13, it will be useful and my problem will be solved.

    Thanks and Regards
    R. Vadivelan

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by velu130486 View Post
    Thanks HTH for your detailed reply.
    HTH = Hope that Helps


    Quote Originally Posted by velu130486 View Post
    Actually I am new user to access and I got this database from my boss, so I am not allowed to make any changes. However I will note down your comments and incorporate the same in future.

    Could you please refer me a website where we can learn about access in more details because I am very eager to learn access and use the same in my projects. Also any manuals or points to remember while creating a database.
    there is a limit to what can be achieved on this website. there are plkenhty of others out there that will teach you how to use Access. but right now Id suggest getting to grips with database development and that means normalisation & relational theory
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design

    one thign to bear in mind in the realtiuonal db world if your data design is normalised and designed effectively then the answer to virtually any question along the lines of can you do x, or z is yes


    In fact I was asking this query, because my boss has already created a weekly plan based on this database and distributed a PDF copy. It was quite useful to me and I want to create the same in Access with additional conditions.

    Quote Originally Posted by velu130486 View Post
    Thanks HTH for your detailed reply.
    Instead of referring from other table is it possible to add a field and calculate the Week Number in the same table. (Week Number means Project Week Number, why I am asking this query is because I used

    DatePart("ww",date) to calculate the week number, however when the year changes from 2013 to 2014 my week number starts from 0. So if you tell me to calculate the week no from the fixed date say 21-Jan-13, it will be useful and my problem will be solved.
    erm I already have:-

    ((mydatecolumn - cdate("2013/1/23"))\7)+1
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2013
    Posts
    163

    Thumbs up

    Thanks HTH,

    It Works fine and my work is 95% done. Thankyou very much for your formula.

    But I have slight problem in year 2014. (ex) 02-May-14, as per my calendar Project Week is 66, but it dispalays as 67. My Project starts on 21-Jan-13 (Monday). My Week first day is Monday and the ending day is Friday. I need to prepare the schedule with cut off date of every friday.

    Also I would like to put one more query, How to display the Number 36 as "W036", similarly 101 as "W101"...I already changed the format but it not works...

    Thanks in advance
    R. Vadivelan

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by velu130486 View Post
    Thanks HTH
    as said before HTH stands for Hope That Helps


    Quote Originally Posted by velu130486 View Post
    But I have slight problem in year 2014. (ex) 02-May-14, as per my calendar Project Week is 66, but it dispalays as 67. My Project starts on 21-Jan-13 (Monday). My Week first day is Monday and the ending day is Friday. I need to prepare the schedule with cut off date of every friday.
    check your data. if you have allowed a week off, say over Christmas then you will need to revert to another mechanism such as your table.

    The second of may 2014 is a Friday, its 469 days AFTER 21 Jan 2013. 469/7 is 67 ergo its week 67, not week 66

    By definition if your week starts on a Monday, the previous week ends on a Sunday. a working week may be different if you take into account only working days. but you can get the friday of that week easily enough

    Quote Originally Posted by velu130486 View Post
    Also I would like to put one more query, How to display the Number 36 as "W036", similarly 101 as "W101"...I already changed the format but it not works...
    one of the problems of using format is that it makes its own decisons in background. so trying to prefix a "W" infrontof the week number is going to fail
    what you could do is do this in the presentation layer
    set the control source = "W" & WeekNo
    or
    "W" & ((mydatecolumn - cdate("2013/1/23"))\7)+1
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Thanks you HTH.

    It works perfectly and I managed to get my report.

    But if I put W in front then Week No 1 is displayed as W1. This I want to display as W001. How to format the text to Four characters.

    Also in your formula why you are putting

    "W" & ((mydatecolumn - cdate("2013/1/23"))\7)+1, My Project start date is 21-Jan-13, is there any reason for the same?

    Thanks in Advance
    R. Vadivelan

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as said before HTH stands for Hope That Helps it doesnt' refer to me in anyway,shape or form

    But if I put W in front then Week No 1 is displayed as W1. This I want to display as W001. How to format the text to Four characters.
    so format the the number as required.

    "W" & ((mydatecolumn - cdate("2013/1/23"))\7)+1, My Project start date is 21-Jan-13, is there any reason for the same?
    yes

    Id suggest you go through that formula and UNDERSTAND what it is doing. its never wise to take a fragment of code from a public source such as this forum AND NOT UNDERSTAND what it is doing. in this case its perfectly harmless.

    \ tells the runtime to use integer division
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2013
    Posts
    163
    Thanks Healdem,

    Finally I got my report in a format as required by me.

    I had modified the formula using IIF condition and it works perfectly.

    STARTW: IIf([Mydatecolumn]>=2013/1/21,"W" & Format((([Mydatecolumn]-CDate("2013/1/21"))\7)+1,"000"),"")

    I had put the condition to check the date is greater than 21-Jan-13 but I am getting a negative value in one record.

    I checked the date, and it is 03-Jan-13 and I am getting the result as W-001.

    Could you please explain me what is the problem.

    Thanks in Advance
    R. Vadivelan

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I checked the date, and it is 03-Jan-13 and I am getting the result as W-001.
    that's hardly surprising seeing as you specified the first week of the project as being 21 Jan 2013 (hence why you subtract the that date from the date column then do an integer divide by 7 and add one to give the week number

    that is the draw back of this approach, going down the alternative pathway of using a calendar table allows you to frig about all day papering over the cracks caused by dodgy data.

    Could you please explain me what is the problem.
    its your system, its your design why don't you try and understand what is being suggested rather than just plug code into your app?
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    I agree with you. As you said I had used the Dlookup formula to get the Week No from Other Table, It works partially However I am not able to put the condition. Please help me to correct my formula.

    IFDW: DLookUp("[Week]","TblCalendar","[ToDate]<=[7_IFD])

    I had enclosed my file for your reference

    Thanks in Advance
    R. Vadivelan
    Attached Files Attached Files

Posting Permissions

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