get the total employee per position by entering the start and end date

[jeimen]

hi! can you help me please in my problem..
how will i make a report that show the total count of employee per position
by entering the start date and end date per assign project..

 

[Jerry Whittle]

At the site below you can find a database with examples of how to create a
parameter query using a form. Once you get the query working, you can create
a report from that query.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=24

 

[jeimen via AccessMonster.com]

im having a problem with this. i have table like this.
Project Table-
ProjectNo
Project NAme
Nature of business
Services
Principal of the project
principal address
Position of Employee
Place of Work
FromDate
ToDate
EmpID

my problem is i want a month report that show the total count of employee per
position by principal. getting the info of month in the From and To Date. Can
you help me with this... thanks in Advance.

 

[John Spencer]

Perhaps the following will give you an idea. This is for the month of
January 2009

SELECT [Position of Employee], Count([Position of Employee]) as
CountPosition
FROM Project
WHERE FromDate <= #2009-01-31# and ToDate >= #2009-01-01#
GROUP BY [Position of Employee]

If you want multiple months, the query is more complex and will need an
additional table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[jeimen via AccessMonster.com]

where should i put that code you give?,,..
and what additional table should i add in my database to get that multiple
months.
pls help me...

 

[John Spencer]

What I posted was a query. YOu would paste that into the SQL view of a
query. Adjust the Table and Field names to match your table and field names.

The additional table would be a table of Dates covering the range of
dates you have in your project table. Then you would have to use a more
complex query joining your projects table to the Dates table.

SELECT Format(Dates.TheDate,"yyyy-mm") as TheMonth
,[Position of Employee]
, Count([Position of Employee]) as CountPosition
FROM Project INNER JOIN Dates
ON Dates.TheDate >= Project.FromDate
AND Dates.TheDate <= Project.ToDate
GROUP BY Format(Dates.TheDate,"yyyy-mm")
,[Position of Employee]

You can filter that by adding a where clause against Dates.TheDate. For
instance the following would get the information for the months of 2008.

SELECT Format(Dates.TheDate,"yyyy-mm") as TheMonth
,[Position of Employee]
, Count([Position of Employee]) as CountPosition
FROM Project INNER JOIN Dates
ON Dates.TheDate >= Project.FromDate
AND Dates.TheDate <= Project.ToDate
WHERE Dates.TheDate Between #2008-01-01# and #2008-12-31#
GROUP BY Format(Dates.TheDate,"yyyy-mm")
,[Position of Employee]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[jeimen via AccessMonster.com]

sorry but i cant follow you procedure... can you specify the easy way...