Query Problem - Two or More Records

[Guest]

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

 

[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.

 

[Guest]

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

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.

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

 

[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

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

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.

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