Report on Month beginning on the 20th

[XCider]

Hi folks,

I have a database with all employees of the departement entering there hours they worked per day and per project.
Now my Boss wants a report of every month, I already realized this with a crosstab.
But now he told me that he wants a report where the month is starting on the 20th of the Month before untill the 19th of the reported month. For example January means from 20th December till 19th January.
The idea was to create a new column where I enter the counting month ( 20/12 - 19/01 => 1; 20/01 - 19/02 => 2;.....)
Now here is my Problem: How can I fill this column automatically?
I tried it with a query to an other table where I entered StartDate, EndDate and RelatedMonth, but I can't get it working.

Can you help me with this solution or show my an other way how to realize it?

Thx,
Martin

 

[namliam]

you should "simply" enter the reporting month (1 thru 12) and enter the reporting year (if not allways current year)

The you can use, assuming you enter 2005 and 1:
Dateserail(YourYear, Month, 19) for the 19-jan-2005 date
Dateserial(YourYear, Month -1, 20) for the 20-dec-2004 date

Regards

 

[XCider]

Thank you for your fast answer.

My Problem is the months are packed in a whole year, so where can I change the date there?

My Code for the crosstab:

TRANSFORM Sum(Horas.HRS_QTD) AS [The Value]
SELECT Horas.HRS_USU_LGN, Horas.HRS_PRJ_COD, Projetos.PRJ_DSC, Projetos.PRJ_BusinessDom, Projetos.PRJ_Owner, Projetos.PRJ_CoTool, Sum(Horas.HRS_QTD) AS [Total Of HRS_QTD]
FROM Projetos, Horas
WHERE (((Year([Horas].[HRS_DIA]))=[forms]![frmReport]![cboWhichYearA]) AND ((Horas.HRS_PRJ_COD)=[Projetos].[PRJ_COD]))
GROUP BY Horas.HRS_USU_LGN, Horas.HRS_PRJ_COD, Projetos.PRJ_DSC, Projetos.PRJ_BusinessDom, Projetos.PRJ_Owner, Projetos.PRJ_CoTool
PIVOT Format([HRS_DIA],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


It seems to be very difficult for me. Can you give me little bit more advice?

Martin

 

[Lord Zog]

One way or another, you're going to need to have the user specify a month. For the sake of illustration, I'm going to assume that it's another entry item on the form where they're specifying the year. Also, to simplify the example, I've dropped the full references to the form fields, to just the names of the fields.

So, substituting [cboWhichYearA] for [forms]![frmReport]![cboWhichYearA] and [cboWhichMonthA] for an assumed [forms]![frmReport]![cboWhichMonthA], consider a WHERE clause like this:

WHERE
  (
    [Horas].[HRS_DIA]

    Between

      DateSerial( [cboWhichYearA] - IIf([cboWhichMonthA]=1, 1, 0),
                  IIf([cboWhichMonthA]=1, 12, [cboWhichMonthA]),
                  20 )
    AND
      DateSerial( [cboWhichYearA], [cboWhichMonthA], 19 )
  )

  AND

  ( Horas.HRS_PRJ_COD = [Projetos].[PRJ_COD] )

 

[john471]

Calculate - Don't Store Results.

The Reporting Month is calculatable based on the HRS_DIA field. It is rarely (some would say never) good practice to store the result of a repeatable calculation in a database table, when the base data is there, you should write a query that performs the calculation and displays the result.

Assuming HRS_DIA is a date/time field in your table "HORAS", where the date the work was performed is stored, the following SQL will calculate (and display) a ReportingMonth (including year). You could easily incorporate such a calculation into your structure.

SQL :-
SELECT HORAS.HRS_DIA, IIf(DatePart("d",[HRS_DIA])<20,Format([HRS_DIA],"yyyymm"),Format(DateAdd("m",1,[HRS_DIA]),"yyyymm")) AS ReportMonth
FROM HORAS;

Which basically says if the day of the month is < 20 then the reporting month is the same as that of the date, otherwise it is one month later than the month of the date.

This is illustrated in the attached (AC97Ver) database with 1000 sample dates. qryTestHarness is the above SQL, and qryTestDisplay shows the resulting start and end points of each reporting month, given the sample data in HORAS table.

HTH

Regards

John.

N.B. - NULL value for HRS_DIA has not been catered for. Ensure this field is set to "required" such that it can't be null, as it makes no sense to perform work on a "null" date.

 

[namliam]

My Problem is the months are packed in a whole year, so where can I change the date there?

I do not understand what you mean by "packed in a whole year" ??

Regards

 

[XCider]

Thank you for your answers. I will try to realize Johns version, because there I can take my crosstab and only have to change the related date.

namliam: By "packed in a whole year" I meant that my report shows the complete year from Jan - Dec. So I would like to keep it like this and don't want to split it into the single months.

THX a lot

Martin

 

[namliam]

Ah well yes OK then you go with Johns version, exept i would do it in a function instead of using an IIf.

It is easier to maintain/update and is easier to copy/reuse elsewhere....

Regards & GL

 

[Jon K]

As the crosstab query needs to PIVOT on a date field, you can easily change John's expression into a date field using the CDate() function:-.

CDATE(IIf(DatePart("d",[HRS_DIA])<20, Format([HRS_DIA],"yyyy/mm/"), Format(DateAdd("m",1,[HRS_DIA]),"yyyy/mm/")) & "1") AS ReportMonth


Since having months of 20th to 19th is equivalent to adding "1 month less 19 days" to the original dates, alternatively you can use the DateAdd() function to arrive at the related dates:-

DateAdd("m",1,DateAdd("d",-19,[HRS_DIA])) AS RelatedDate


Either one will do for the crosstab query.
.

 

[XCider]

Hey thanks a lot guys,

with your solution Jon K it is very easy and it is working anyway. I think for the other solutions I need more experience to realize them.

Thank you again,

Martin