Creating Turnover Report based on Query

[CEV]

Hi, I am working on a turnover database and I am fairly new to Queries and
Reports. I have 3 tables called Employees, Positions, and EmployeePositions.
For starters, I am trying to get a report (that will be run monthly) that
shows the total number of positions that are marked as "Active" for each
department and also show the number of positions that are open. In the query
I have the following fields so far.

Department
Inactive These 2 are from tblPositions
DateStarted
DateEnded
PositionNumber These 3 are from tblEmployeePositions


The DateStarted and DateEnded are the dates that an employee is/was employed
in the PositionNumber. I created a form called frmSelectDateRange for me to
enter in the dates of a given month like 3/1/06 - 3/31/06. Each
PositionNumber may have multiple entries with start and end dates as
employees come and go. I want the query to calculate per department how many
positions have an end date and do not have a start date between the entered
dates on the frmSelectDateRange. If I'm thinking correctly, this should give
me the number of open positions at the end of the month. Am I correct on
this? How do I enter the criteria into the query to do this?

Thank You,

CEV

 

[privatenews]

Hello Chad,

Per your question, you may try the following query for Mar:

select count (distinct (Position number) ) from tblPositions P inner join
tblEmployeePositions EP on P.PositionNumber=EP.PositionNumber
where EP.DateStarted < #3/1/206 and EP.DateEnded < #3/31/06# and
EP.DateEnded > #3/1/06# and P.Department=<number>

This shall provide the open position of the month for the given department

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CEV]

Thank You for the response Peter. I am not very familiar yet with Queries
and I am wondering where exactly do I enter this info?

Thank You,

Chad

 

[privatenews]

Hello Chad,

After you open a query in design view, you could get SQL view by clicking
View->SQL View, and you could enter SQL query directly.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CEV]

I keep getting the error "Syntax error (missing operator)" when typing this
in. I double checked and the table names and field names are exact.

Thanks,

CEV

 

[privatenews]

Hello,

I suggest you first test the following query and then add where condition
one by one:

select count (distinct (Positionnumber) ) from tblPositions P inner join
tblEmployeePositions EP on P.PositionNumber=EP.PositionNumber

Please make sure the column/table name is correct according to your database

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CEV]

I am now getting the error : "The specified field 'Positionnumber' could
refer to more than one table listed in the FROM clause of your SQL
statement." I greatly appreciate your help Peter, but if I am not to figure
this out soon, I think I'm going to have to start looking at some
professional help because the reports they are wanting is more than what
I've ever done before.

Thanks,

Chad

 

[privatenews]

Hello Chad,

Sorry, we shall use P.Positionnumber other than Positionnumber in Distinct
function.

select count (distinct (P.Positionnumber) ) from tblPositions P inner join
tblEmployeePositions EP on P.PositionNumber=EP.PositionNumber

If the issue is urgent, I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.