Query Help counting months between two dates 1

[DomFino]

Hello everyone.

I have a list of employees in a table called tblReport. In this table I have four fields.
Id autonumber
Name text
StartDate date/time
TermDate date/time

I have a second table called tblBillableMonth which has two fields
Id autonumber
DateID date/time

The DateID contains a list of dates from 1/2000 to 2/2006
01/2000
02/2000
03/2000

02/2006

What I am trying to do (very unsuccessfully) is to count
the number of employees by DateID so that I can show the
number of billable employees we have each month.

I created the following query (qryBillabel) to extract the
data I need. Unfortunately it is skipping the very first
month for each employees date range. For example, if
employee Joe Smith has a Start Date of 2/3/2000 and a Term
Date of 3/6/2003 the query creates a row for 3/2000,
4/2000, etc. but not 2/2000.

I have change the tblBillableMonth date fields to a format
of mm/yyyy, thinking this would do the trick. However I
get the same result. That is the first month is not
counted.
Query Name = qryBillabel

SELECT tblBillableMonth.DateID AS MonthToMark, tblReport.StartDate, tblReport.TermDate, tblReport.NAME
FROM tblBillableMonth, tblReport
WHERE (((tblBillableMonth.DateID) Between [StartDate] And [TermDate]));

The ultimate goal is to take the query above and then run a crosstab query to display the results.

Query Name = qryBillabel_Crosstab

TRANSFORM Count(qryBillabel.NAME) AS CountOfNAME
SELECT qryBillabel.MonthToMark, Count(qryBillabel.NAME) AS [Total Of NAME]
FROM qryBillabel
GROUP BY qryBillabel.MonthToMark
PIVOT Format([MonthToMark],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I can sure use some query help on this one or suggestions
on a better way to do what I am trying to accomplish.
Thanks
Dom

 

[lynchg]

I would get rid of tblBillableMonth. I assume that you are somehow making a choice from that table either as a parameter in a query or from a combo box on a form.

Instead of that I would just enter a date for the month you are interested in, or select a date in that month from a calendar control. You can still use qryBillable with the MonthToMark value, but use the datepart function like this:

Format(DatePart(m,[MonthToMark]),"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[DomFino]

Lynchg,
Thanks for the reply.
I need the tblBillableMonth so that I can ultimately perform the cross tab query (qryBillabel_Crosstab).

Using the DatePart function you spoke about made sence so I broke the StartDate and TermDate into four seperate fields.

SELECT tblBillableMonth.DateID AS MonthToMark, DatePart("m",[StartDate]) AS StartDateMonth, DatePart("yyyy",[StartDate]) AS StartDateYear, tblReport.StartDate, DatePart("m",[TermDate]) AS TermDateMonth, DatePart("yyyy",[TermDate]) AS TermDateYear, tblReport.TermDate, tblReport.NAME
FROM tblBillableMonth, tblReport
WHERE (((tblBillableMonth.DateID) Between [StartDate] And [TermDate]));

However, I am not sure how to code the WHERE statement so that it can extract the m and yyyy for the StartDate and EndDate. It would be nice if there were a DatePart for m/yyyy.

Any suggestions on the correct WHERE stetement will be much appriciated.
Thanks,
Dom

 

[PHV]

Perhaps something like this ?
WHERE Format(tblBillableMonth.DateID, 'yyyymm') Between Format([StartDate], 'yyyymm') And Format([TermDate], 'yyyymm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PHV,
That is the ticket. I applied your suggested code and it give me the correct results. Thank you.

I have one more question on this project. If the TermDate is blank, the employee is still billable. Therefore, I need to say something like this:

IIF TermDate is null or blank, TermDate equals todays date(formatted as yyyymm), else use the TermDate.

Current Code

ELECT tblBillableMonth.DateID AS MonthToMark, tblReport.StartDate, tblReport.TermDate, tblReport.NAME
FROM tblBillableMonth, tblReport
WHERE (((Format([tblBillableMonth].[DateID],'yyyymm')) Between Format([StartDate],'yyyymm') And Format([TermDate],'yyyymm')));

Do I need to create another field to do the IIF stated above and then compare the StartDate to the newly defined TermDate?
Thanks
Dom

 

[PHV]

And Format(Nz([TermDate],Date()), 'yyyymm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PHV,
I applied your suggested code and the blank dates still show as blank. Also, Access addes the <>False to the end of the query. Not sure why.

SELECT tblBillableMonth.DateID AS MonthToMark, tblReport.StartDate, tblReport.TermDate, tblReport.NAME
FROM tblBillableMonth, tblReport
WHERE (((Format([tblBillableMonth].[DateID],'yyyymm')) Between Format([StartDate],'yyyymm') And Format([TermDate],'yyyymm')) AND ((Format(Nz([TermDate],Date()),'yyyymm'))<>False));

 

[PHV]

WHERE Format([tblBillableMonth].[DateID],'yyyymm') Between Format([StartDate],'yyyymm') And Format(Nz([TermDate],Date()),'yyyymm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

Phv,
Thank you so much. That last change made the difference. It works perfectly and I learned more about date format than I knew befor. Thanks again.
Dom