Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    22

    Unhappy Unanswered: Grouping in reports for specific period

    Hi friends ..
    I have database with lot of records also having date field [completiondate] and required to be grouped and make report for specific range 1-oct-yyyy to 31-march-yyyy and 1-apr-yyyy to 30-sep-yyyy ie half yearly wise. pl help me how to do. thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Either use an iif to consolidate dates into financial periods or better yet have a calendar table that maps true dates to financial dates. Eg:-
    Table financial_years
    Fyear integer
    Fperiod integer
    Starts_on date

    Prinary key would be a composite of fyrear and fperiod
    Index on starts_on

    Join to the calendar table to retrieve the fyear and period
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    22
    Quote Originally Posted by healdem View Post
    Either use an iif to consolidate dates into financial periods or better yet have a calendar table that maps true dates to financial dates. Eg:-
    Table financial_years
    Fyear integer
    Fperiod integer
    Starts_on date

    Prinary key would be a composite of fyrear and fperiod
    Index on starts_on

    Join to the calendar table to retrieve the fyear and period
    Sir, thanks for suggestion but Iam not understanding, I want only how to group records half yearly wise (specific range april-oct and oct-march of the year) on field [completiondate] and show summary of qty in reports.
    my table - wrktble having fields ! docno ! desc ! type ! qty ! completiondate ! Remark !,
    I want to know how to generate report with above specific range grouping and summary on qty. Pl help..

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Either use an iif eg
    Iif (month(mydatecolumn) between 4 and 9,"p1","p2") as period
    Or define a calendar table and JOIN to that table to find the period

    Of the two the calendar table is,id argue the better option
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2013
    Posts
    22
    Quote Originally Posted by healdem View Post
    Either use an iif eg
    Iif (month(mydatecolumn) between 4 and 9,"p1","p2") as period
    Or define a calendar table and JOIN to that table to find the period

    Of the two the calendar table is,id argue the better option
    Thanks for suggestion and I got the idea and made two columns in query and joined and it worked but how to get previous year ie each specific period "oct-14 to Mar-15" and "Apr-15 to Sept-15" for a particular date and for every year, example for date say 1-jan-2015 it should show range as "oct-14 to Mar-15", and for date say 20-may-2015 it should show range as "Apr-15 to Sept-15", i have tried various combination but could not succeeded, may be I will try more and lets see if i get desired result... mean time u can also suggest me.. Thanks lot..

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by putur View Post
    Thanks for suggestion and I got the idea and made two columns in query and joined and it worked but how to get previous year ie each specific period "oct-14 to Mar-15" and "Apr-15 to Sept-15" for a particular date and for every year, exampl0e for date say 1-jan-2015 it should show range as "oct-14 to Mar-15", and for date say 20-may-2015 it should show range as "Apr-15 to Sept-15", i have tried various combination but could not succeeded, may be I will try more and lets see if i get desired result... mean time u can also suggest me.. Thanks lot..
    as said before create a calendar table that maps a real world date into a financial / production / planning date.

    of add the year(mycolumn) to the query that identifies what period it is based on the IIF above

    BUT it does mean you have to include the iif in both the select AND if you use grouping the GROUP by or even where clauses.

    use a calendar... it simplifies the query
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2013
    Posts
    22
    Quote Originally Posted by healdem View Post
    as said before create a calendar table that maps a real world date into a financial / production / planning date.

    of add the year(mycolumn) to the query that identifies what period it is based on the IIF above

    BUT it does mean you have to include the iif in both the select AND if you use grouping the GROUP by or even where clauses.

    use a calendar... it simplifies the query
    Thanks dear, I got it through your nice suggestion.
    I made following columns in query after lot of try..

    monthgr: Format([completedon],"yyyy")
    p1: IIf(Month([completedon]) Between 4 And 9,"1-Apr-" & [monthgr],IIf(Month([completedon]) Between 1 And 3,"1-oct-" & [monthgr]-1,"1-oct-" & [monthgr]))
    p2: IIf(Month([completedon]) Between 4 And 9,"30-Sept-" & [monthgr],IIf(Month([completedon]) Between 1 And 3,"31-Mar-" & [monthgr],"31-Mar-" & [monthgr]+1))
    monthperiod: [p1] & " to " & [p2]

    In reports grouping done on [monthperiod] and i got the qty summary by specific period..

    Happy to achieve what I expected.. Thanks for your help..

    Just small sorting problem exits ie April-15 comes before oct-14, ie. text sorting happens.. I want in ascending order from old period to latest.

    Hoping for some suggestion..

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So can we see the actual SQL. Thats the SQL view not selected bits from tge GUI query designer.

    The reason you are struggging with the sort order is because you are making it harder for yourself by using an iif statement NOT a JOINing to a calendar table.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2013
    Posts
    22
    Quote Originally Posted by healdem View Post
    So can we see the actual SQL. Thats the SQL view not selected bits from tge GUI query designer.

    The reason you are struggging with the sort order is because you are making it harder for yourself by using an iif statement NOT a JOINing to a calendar table.
    wrk-period.zip

    Pl see the file where I am wrong

  10. #10
    Join Date
    Oct 2013
    Posts
    22
    Sir, Dont understand how to do calendar table or how to join ... i am not expert in access, but knows little bit.. hope u will give some idea how to do.. thanks..

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I thought I already had....

    persoanlly I don't have time or the inclination to provide a fully working, debugged example
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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