Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Answered: Hw to generate the Reports in Access by weekly basis

    Dear Seniors,

    I have created a table with list of entries based on dates. Now I would like to report in access which should me the weekly data.

    I had applied the filter in queries and it works fine...however I would like to appear the blank values also in my query. For ex I am having the data for WK 15 on 24-Mar-15 and 25-Mar-15. When I generate the report this report is appearing.

    However I would like to view the report which shows the weekly status even though the data is empty. I.e I want to generate the report from 21-Mar-15 to 27-Mar-15 as follows

    21-Mar-15 - Blank Values
    22-Mar-15 - Blank Values
    23-Mar-15 - Blank Values
    24-Mar-15 - 8:00 to 17:00 PM - 8 Hours
    25-Mar-15 - 8:00 to 17:00 PM - 8 Hours
    26-Mar-15 - Blank Values
    27-Mar-15 - Blank Values

    The weekly dates should change based on my week selection.

    Could you please help me to sort out this issue.
    Thanks and Regards
    R. Vadivelan

  2. Best Answer
    Posted by healdem

    "have a table contianing dates
    do a left join on that table to your other data.
    as you are usign a left join then all the dates will be returned and thiose rows which match those dates
    wrapped up in a production or financial calendar (remember the suggestion for one of those )"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a table contianing dates
    do a left join on that table to your other data.
    as you are usign a left join then all the dates will be returned and thiose rows which match those dates
    wrapped up in a production or financial calendar (remember the suggestion for one of those )
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Oct 2013
    Posts
    163
    Dear Healdem,

    I am having a calendar with Week Start date and Week End date. Is it possible to generate the above report with help of the above table or I need to create a another table with all the dates.
    Thanks and Regards
    R. Vadivelan

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you create another table containing what is essentailly the same or similar information?
    all you do then is create confusion in the users muind as to whether thay have updated all the date values, are the date values in the two tables coherent and in step.
    if the current table doesn't provode the structure you need then consider redesigning it, or have a sub table off that containing the pertinent data
    use a ledft join on product table calendartable condition = prodcuttabel condition

    the left join extracts all rows fromt eh left referred table (in the above example the calendar table) and any rows that match form the other tabel.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Oct 2013
    Posts
    163
    Dear Healdem,

    As suggested by you, I have created 2 tables as follows.

    Table1 :
    StartDate Day Week_No
    04-Apr-15 Monday WK17
    05-Apr-15 Tuesday WK17
    06-Apr-15 Wednesday WK17
    07-Apr-15 Thursday WK17
    08-Apr-15 Friday WK17
    09-Apr-15 Saturday WK17
    10-Apr-15 Sunday WK17

    It contains all the dates in a Project for every week

    Table2:
    OTDate SeqNo Week_No Reason
    09-Apr-15 0054 WK17 IFR Works
    10-Apr-15 0054 WK17 IFR Works

    It contains only the data on which Overtime was performed.

    Now I had created a query by Left Join Table1 with Table2 by Linking StartDate & OTDate. I tried by linking the Week_No also.

    I would like the query to return the result as follows

    04-Apr-15 Monday WK17
    05-Apr-15 Tuesday WK17
    06-Apr-15 Wednesday WK17
    07-Apr-15 Thursday WK17
    08-Apr-15 Friday WK17
    09-Apr-15 Saturday WK17 IFR Works 0054
    10-Apr-15 Sunday WK17 IFR Works 0054

    However my query returns only the date on which OT was performed, However my requirement is to display all the dates in that particular dates as follows

    09-Apr-15 Saturday WK17 IFR Works 0054
    10-Apr-15 Sunday WK17 IFR Works 0054

    Kindly help me to sort out this issue. Also I would like to display the query result in a single report. But when I selected the report source as Query then each line is displayed separately in 2 pages.
    Thanks and Regards
    R. Vadivelan

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And your query is......
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Oct 2013
    Posts
    163
    Dear Healdem,

    SELECT Table1.StartDate, Table1.Week_No, Table2.Reason, Table2.Seq, Table2.OTHours
    FROM Table1 LEFT JOIN Table2 ON Table1.StartDate = Table2.OTDate
    WHERE (((Table2.Seq) Is Null Or (Table2.Seq)=[Enter Seq No]));

    I got the result as required and thanks for your help. However I have 2 queries to close out this issue.

    1) I want to add the Sequential Number in Report header. I tried but it shows Blank values

    2) Is it possible to display the Parameter values as one of the field in Query...(i.e) When I run the query it is asking for the Seq No, When I enter the Seq No, then the same should be appear in field for query results. I hope it was clear, please let me know if any further details required.

    I had enclosed my database for your reference.

    TEP_PAAFRegister.zip
    Last edited by velu130486; 04-06-15 at 13:59. Reason: Updated with Further data
    Thanks and Regards
    R. Vadivelan

Posting Permissions

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