Query Problem - Two or More Records

R

Rick_C

I am trying to run a query where I can determine when an employee gets more
than 2 complaints within a certain date range.

The fields in my query are:

IncidentDate
EmployeeNumber

In the INCIDENTDATE column, I set the criteria as:

Between [Forms]!frmEmployeeComplaintDialogBox]![BeginningDate] And
[Forms]!frmEmployeeComplaintDialogBox]![EndingDate]

In the EMPLOYEENUMBER column, I set the criteria as:

In (SELECT[EmployeeNumber] FROM [tblECDEmployeeInvolved] WHERE
[EmployeeNumber] > 2 GROUP BY [EmployeeNumber] HAVING Count (*) > 2)

I am not getting a correct response. I am showing employees that have two or
more complaints but not just within the specified date range.

Any help would be greatly appreciated.

Thank you in advance for your response.

Rick
 
J

John Spencer

Try changing the sub-query in the criteria to

In (SELECT [EmployeeNumber]
FROM [tblECDEmployeeInvolved]
WHERE [IncidentDate] Between
[Forms]!frmEmployeeComplaintDialogBox]![BeginningDate]
And [Forms]!frmEmployeeComplaintDialogBox]![EndingDate]
GROUP BY [EmployeeNumber] HAVING Count (*) > 2)

This returns records where the employee had MORE than 2 incidents in the
specified time frame.

Your sub-query was returning any employee that had an employee number
greater than 2 and had more than 2 incidents in the entire table.
 
R

Rick_C

John,

I am still getting the same problem.

I did neglect to say in my first posting that the INCIDENTDATE is in a
different table.

INCIDENTDATE is in the tblINCIDENTINFORMATION table

EMPLOYEENUMBER is in the tblECDEMPLOYEEINVOLVED table

I tried using your suggested statement in both the INCIDENTDATE criteria and
the EMPLOYEENUMBER criteria.

Thanks for your help...

Rick




John Spencer said:
Try changing the sub-query in the criteria to

In (SELECT [EmployeeNumber]
FROM [tblECDEmployeeInvolved]
WHERE [IncidentDate] Between
[Forms]!frmEmployeeComplaintDialogBox]![BeginningDate]
And [Forms]!frmEmployeeComplaintDialogBox]![EndingDate]
GROUP BY [EmployeeNumber] HAVING Count (*) > 2)

This returns records where the employee had MORE than 2 incidents in the
specified time frame.

Your sub-query was returning any employee that had an employee number
greater than 2 and had more than 2 incidents in the entire table.


Rick_C said:
I am trying to run a query where I can determine when an employee gets more
than 2 complaints within a certain date range.

The fields in my query are:

IncidentDate
EmployeeNumber

In the INCIDENTDATE column, I set the criteria as:

Between [Forms]!frmEmployeeComplaintDialogBox]![BeginningDate] And
[Forms]!frmEmployeeComplaintDialogBox]![EndingDate]

In the EMPLOYEENUMBER column, I set the criteria as:

In (SELECT[EmployeeNumber] FROM [tblECDEmployeeInvolved] WHERE
[EmployeeNumber] > 2 GROUP BY [EmployeeNumber] HAVING Count (*) > 2)

I am not getting a correct response. I am showing employees that have two
or
more complaints but not just within the specified date range.

Any help would be greatly appreciated.

Thank you in advance for your response.

Rick
Click to expand...
Click to expand...
 
J

John Spencer

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


Rick_C said:
John,

I am still getting the same problem.

I did neglect to say in my first posting that the INCIDENTDATE is in a
different table.

INCIDENTDATE is in the tblINCIDENTINFORMATION table

EMPLOYEENUMBER is in the tblECDEMPLOYEEINVOLVED table

I tried using your suggested statement in both the INCIDENTDATE criteria
and
the EMPLOYEENUMBER criteria.

Thanks for your help...

Rick




John Spencer said:
Try changing the sub-query in the criteria to

In (SELECT [EmployeeNumber]
FROM [tblECDEmployeeInvolved]
WHERE [IncidentDate] Between
[Forms]!frmEmployeeComplaintDialogBox]![BeginningDate]
And [Forms]!frmEmployeeComplaintDialogBox]![EndingDate]
GROUP BY [EmployeeNumber] HAVING Count (*) > 2)

This returns records where the employee had MORE than 2 incidents in the
specified time frame.

Your sub-query was returning any employee that had an employee number
greater than 2 and had more than 2 incidents in the entire table.


Rick_C said:
I am trying to run a query where I can determine when an employee gets
more
than 2 complaints within a certain date range.

The fields in my query are:

IncidentDate
EmployeeNumber

In the INCIDENTDATE column, I set the criteria as:

Between [Forms]!frmEmployeeComplaintDialogBox]![BeginningDate] And
[Forms]!frmEmployeeComplaintDialogBox]![EndingDate]

In the EMPLOYEENUMBER column, I set the criteria as:

In (SELECT[EmployeeNumber] FROM [tblECDEmployeeInvolved] WHERE
[EmployeeNumber] > 2 GROUP BY [EmployeeNumber] HAVING Count (*) > 2)

I am not getting a correct response. I am showing employees that have
two
or
more complaints but not just within the specified date range.

Any help would be greatly appreciated.

Thank you in advance for your response.

Rick
Click to expand...
Click to expand...
Click to expand...
 
Top