Query Problem - Two or More Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick_C - 31 Mar 2006 14:10 GMT

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

Reply to this Message

John Spencer - 31 Mar 2006 14:34 GMT

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.
[quoted text clipped - 23 lines]
>
> Rick

Reply to this Message

Rick_C - 31 Mar 2006 16:40 GMT

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
>
[quoted text clipped - 38 lines]
> >
> > Rick

Reply to this Message

John Spencer - 31 Mar 2006 17:59 GMT

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,
>
[quoted text clipped - 59 lines]
>> >
>> > Rick

Reply to this Message