Charting Employment

[Phoenyxsgirl]

Ok, so here I need to create a chart that will show how many people have been employed over the last few months in the company. I would also like to be able to pull up the information in a report for how many were employed yesterday versus today.

I have tblEmployee with the EmpID as primary and also includes their FirstName, LastName, Orientation, EndPayroll.



I have tblPlacement with the EmpID linked to tblEmployee. This table also includes StartDate, EndDate, and Job#.

I need a chart that shows from Orientation to EndPayroll dates of all participants, just counting how many were employed each day.

I need a chart that shows from StartDate to EndDate for each participant as well, counting how many were working each day.

I also need a report that shows yesterdays employed and today's employed and calculates the difference or change. It will also calculate the total Orientations and EndPayrolls to show the total # of employees that have ever been with the company.

I think the main thing that I am having a hard time figuring out is how to calculate the totals based on a certain date. To tell it I want to include anyone who has an Orientation or StartDate before DATE and EndPayroll or EndDate after DATE ...

Help, please?

[ssanfu]

I think the main thing that I am having a hard time figuring out is how to calculate the totals based on a certain date. To tell it I want to include anyone who has an Orientation or StartDate before DATE and EndPayroll or EndDate after DATE ...

This is untested, but it should be right:

..WHERE (Orientation < Date() or StartDate < Date()) AND (EndPayroll >= Date() or EndDate >= Date())

[Phoenyxsgirl]

So a query where there was a CountofEmpID, WorkDate (I need to find out how to autopopulate this with a series of dates from the very beginning like 6/22/11), and then in the SQL enter the WHERE statement so that it refers to the WorkDate field?

[ssanfu]

So a query where there was a CountofEmpID, WorkDate (I need to find out how to autopopulate this with a series of dates from the very beginning like 6/22/11), and then in the SQL enter the WHERE statement so that it refers to the WorkDate field?

Without seeing your data, structure and knowing more about what you are trying to do, you could start with that query.

Another way would be to use two queries. The first would limit the records to between the dates (no totals). Then use a second query to count the records. (This is also called a query with a sub query).

I don't have A2K7, so I can't look at the mdb, but I have had to do queries this way to get counts.

[Phoenyxsgirl]

Ok, so here I need to create a chart that will show how many people have been employed over the last few months in the company. I would also like to be able to pull up the information in a report for how many were employed yesterday versus today.

I have tblEmployee with the EmpID as primary and also includes their FirstName, LastName, Orientation, EndPayroll.

I have tblPlacement with the EmpID linked to tblEmployee. This table also includes StartDate, EndDate, and Job#.

I need a chart that shows from Orientation to EndPayroll dates of all participants, just counting how many were employed each day.

I need a chart that shows from StartDate to EndDate for each participant as well, counting how many were working each day.

I also need a report that shows yesterdays employed and today's employed and calculates the difference or change. It will also calculate the total Orientations and EndPayrolls to show the total # of employees that have ever been with the company.

I think the main thing that I am having a hard time figuring out is how to calculate the totals based on a certain date. To tell it I want to include anyone who has an Orientation or StartDate before DATE and EndPayroll or EndDate after DATE ...

Help, please?

What else do you need to know about the data structure or goal? I have no trouble getting quotes for today. I just need it to pull for each day in the past 6 months to show each day a count of EmpID's.