none
Report - Summary of data query - perhaps a crosstab? RRS feed

  • Question

  • Hi,

    I have these data fields:

    DateOpened

    DateClosed

    in a table called Case.

    I want to created a query / report that has the following output but am having issues working out how to go about it. (Ongoing is opened in a previous month and not closed in the month).

    Any help about how I may go about achieving this objective would be appreciated.

    I am thinking crosstabs, union queries, but am out of my league on this one.

    Regards

    John G



    JG





    Saturday, September 29, 2018 12:23 AM

Answers

  • Create a table named Dates with a single field TheMonth and populate it with the first day of each month within the timeframe you want to report on. For example:

    (I used TheMonth instead of Month since Month is a reserved word - it is a VBA function).

    Then create a query like this:

    SELECT Dates.TheMonth, (SELECT Count(*) FROM Case WHERE DateOpened-Day(DateOpened)+1=TheMonth) AS Opened, (SELECT Count(*) FROM Case WHERE DateOpened<TheMonth AND DateClosed>=DateAdd("m",1,TheMonth)) AS Ongoing, (SELECT Count(*) FROM Case WHERE DateClosed-Day(DateClosed)+1=TheMonth) AS Closed
    FROM Dates;


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, September 29, 2018 9:25 AM

All replies

  • Create a table named Dates with a single field TheMonth and populate it with the first day of each month within the timeframe you want to report on. For example:

    (I used TheMonth instead of Month since Month is a reserved word - it is a VBA function).

    Then create a query like this:

    SELECT Dates.TheMonth, (SELECT Count(*) FROM Case WHERE DateOpened-Day(DateOpened)+1=TheMonth) AS Opened, (SELECT Count(*) FROM Case WHERE DateOpened<TheMonth AND DateClosed>=DateAdd("m",1,TheMonth)) AS Ongoing, (SELECT Count(*) FROM Case WHERE DateClosed-Day(DateClosed)+1=TheMonth) AS Closed
    FROM Dates;


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, September 29, 2018 9:25 AM
  • I am thinking crosstabs, union queries, but am out of my league on this one.

    Hi John,

    I a couple of applications where I have a similar situation I use a slightly different approach, using some kind of redundancy. It saves the recalculation of most of the past months resulting in always the same values.

    The table Month_tbl is extended with the fields Opened, Ongoing, Closed (all numeric) and Changed (boolean, default = True).

    Before running the report the table must be updated, record by record, when Changed = True. After update the record Changed is set to False. Update is done in the way that Hans proposed. It is then easy to display the selected months from Month_tbl in your report (or form).

    Whenever a Case is opened or a Case is closed in a certain month, the Changed field for that month in Month_tbl is set to True.

    Imb.


    • Edited by Imb-hb Saturday, September 29, 2018 5:48 PM typo
    Saturday, September 29, 2018 5:47 PM
  • Beautiful solution Hans. I do understand the SQL.

    The WHERE clauses are genius!

    DateOpened-Day(DateOpened)+1=TheMonth

    DateOpened<TheMonth AND DateClosed>=DateAdd("m",1,TheMonth))

    DateClosed-Day(DateClosed)+1=TheMonth)

    So clever.

    Many thanks. You have not only answered my question but opened doors to new knowledge and future possibilities. Thanks for your clear response and your time.

    John G


    JG

    Sunday, September 30, 2018 1:50 AM
  • Thanks IMB-HB. Han's solution mets my needs and I can't quite conceive a scenario where the additional process you describe would be required. Thanks for your reply.

    Regards

    John


    JG

    Sunday, September 30, 2018 1:52 AM
  • I can't quite conceive a scenario where the additional process you describe would be required.

    Hi John,

    If you want to compare the months of this year to the months of last year to analyse seasonal influences, or to make trend analysises over certain periods, it would be timeconsuming the recalculated every value over and over again from the "raw" data. But in that case you need a mechanism to update a stored calculated value in case something has changed, as is normal in the "current" timeperiod.

    It works for all kind of performance indicators. I use it especially in financial applications where you want to compare the business years with each other.

    Imb.

    Sunday, September 30, 2018 11:46 AM