Tenure Summary Report Question

[srmezick]

I am attempting to create a report that will count the number of employees within a certain range of employment. Over 90 days, 61 to 90 days, 31 to 60 days, and 0 to 30 days. I will also have to report them by shift and plant wide...



I know that using Dcount I should be able to create a function that will make this calculation but so far I havent had any luck.

What I would like is for DCount to count the number of Employee IDs (Column name "ID") in the table "Employee Information" which have a hire date (column "Hire Date") that falls before today's date minus 91, and then another function for between today's date minus 90 and today's date minus 61 ... and so on. If someone wouldnt mind helping it I would greatly appreciate it.

Here is the function that I am currently trying to make work...

=dcount([ID], "Employee Information Table", "[Hire Date] = before '# date()-90 #'")


if you can tell me what's wrong with it that would also help me in the future I think.

[jzwp11]

What I would do is to create a query that categorizes each employee based on the 4 categories you mention, something like this:

query name: qryCategories
SELECT [Employee Information Table].ID, [Employee Information Table].hiredate, IIF(hiredate<dateadd("d", -90, date()), "> 90", IIF(hiredate >=dateadd("d",-30,date()),"<30",IIF(hiredate<dateadd("d",-30,date()) and hiredate>=dateadd("d",-60,date()),"31-60","61-90"))) AS HireDateCategory, datediff("d",date(),hiredate) AS DifferenceInDays
FROM [Employee Information Table];


Then I would create a query from the above that does the counting:

SELECT qryCategories.HireDateCategory, Count(qryCategories.HireDateCategory) AS CountOfHireDateCategory
FROM qryCategories
GROUP BY qryCategories.HireDateCategory;

[srmezick]

Thanks so much for your reply. It wasnt exactly how I was hoping to make this happen, but it will definatly get the job done. I was already half way there having already created the queries for each category, now I just need to create the crosstab queries. =)

Thanks Again,

Stephanie

[jzwp11]

You're welcome and good luck with the crosstab queries. I do not have much experience with crosstab queries, so I cannot offer help there.