Modifying a query to return only instances where there is more than 3 records (1 Viewer)

[fat controller]

I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.

Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.

I am thinking along the lines of DCount? but have no idea whether I am right, or how it would be phrased in the query?

 

[plog]

Do you want to look at data at the record level or do you just want names of those with 3 incidents?

Can you post sample starting data from your table (include table and field names) and then what results you would like returned from this query based on that sample starting data?

Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 6/7/2011, 19
Larry, 5/4/2004, 90
Sally, 7/8/2009, 2

 

[fat controller]

I want it at record level. Table as below:

LogCardDiscrepancy

ID (autonumber), EmployeeNumber, Surname, Forename, Date_of_Report, Log_Card_Date, Trip_Record (yes/no), VDC (yes/no), Report_By, Notes (memo)

2, 7009790, Brown, Billy, 01/09/2014, No, Yes, John Smith, {text}
3, 7009790, Brown, Billy, 02/09/2014, Yes, Yes, Adam White, {text}
4, 5250770, Allen, George, 02/09/2014, Yes, No, Adam White, {text}
5, 7009790, Brown, Billy, 03/09/2014, Yes, Yes, John Smith, {text}
6, 1909922, Jones, Emma, 03/09/2014, Yes, Yes, John Smith, {text}
7, 7009790, Brown, Billy, 04/09/2014, No, Yes, Adam White {text}
8, 1909922, Jones, Emma, 04/09/2014, No, Yes, Adam White {text}
9, 1909922, Jones, Emma, 05/09/2014, Yes, No, John Smith {text}

So, if a report was ran from 01/09/2014 to 07/09/2014, the result would be to show all records for Emma Jones and for Billy Brown as they both have three (or more) instances of being reported.

 

[plog]

That will require a subquery to determine those that meet the criteria:

SELECT EmployeeNumber
FROM LogCardDiscrepancy
WHERE [Date_of_Report]>=#[Enter Beginning Date]# AND [Date_of_Report]<=#[Enter Ending Date]#
HAVING COUNT(EmployeeNumber) >3

That will give you all EmployeeNumbers who meet the criteria. Then to return all the records, make another query with it and LogCardDiscrepancy, link by EmployeeNumber and you will have your data.

 

[fat controller]

Thanks

Would that be a Union Query then? Or am I barking up the wrong tree? I have generally used the wizard to date, so am not fantastic with queries.

 

[Brianwarnock]

No a simple select query in whicch You join this query to the original table on employee number.

Brian

 

[fat controller]

Apologies for being really (REALLY) thick with this, but I am still struggling with this.

I have a query which shows the detail of all fields in all records between two selected dates, which has the following in the criteria under the date field:

Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![!txtEndDate]

If I am understanding this correctly, I need to have a second query which shows the detail of each record for the date and employee number fields only?

And in the criteria of that query, I need to put 'Count >2' under the employee number field?

How do I then join them together?

Thanks for sticking with me on this

 

[plog]

Did you create the query I posted?

If so, save it with the name 'subQuery'. Then start a new query, bring in LogCardDiscrepancy and subQuery. Link them via their EmployeeNumber fields, pull in all fields you want from LogCarddiscrepancy and you will have your data.

 

[Brianwarnock]

The "this query" in my post referred to plog's query, so he and I are saying the same thing, he is a little bit clearer.

Brian

 

[fat controller]

Did you create the query I posted?

If so, save it with the name 'subQuery'. Then start a new query, bring in LogCardDiscrepancy and subQuery. Link them via their EmployeeNumber fields, pull in all fields you want from LogCarddiscrepancy and you will have your data.

Yes, I have. although I modified it slightly to cope with the form that the user will use to enter dates, however in doing so I have borked it I think:

The user selects the start date and end date from a form - the form is called DateSelect, the fields are txtStartDate and txtEndDate, so I changed the code to

SELECT EmployeeNumber
FROM LogCardDiscrepancy
WHERE [LogCardDate]>=#[Forms]![DateSelect]![txtStartDate]# AND [LogCardDate]<=#[Forms]![DateSelect]![txtEndDate]#
HAVING COUNT(EmployeeNumber) >2;

This produced an error (shown in the image attached)

I will go now and put it back as you originally had it.

 

[fat controller]

Changing it back threw up a similar error (Syntax error), so I thought I would try:

SELECT EmployeeNumber
FROM LogCardDiscrepancy
WHERE [LogCardDate]>=[Forms]![DateSelect]![txtStartDate] AND [LogCardDate]<=[Forms]![DateSelect]![txtEndDate]
HAVING COUNT(EmployeeNumber) >2;

That threw up a different error, as per the attached screen snip

 

[fat controller]

I changed the view from SQL to Design View and then made the date criteria the same as it is in the main query; when I go back to SQL view, it has changed the code to;

SELECT LogCardDiscrepancy.EmployeeNumber
FROM LogCardDiscrepancy
WHERE (((LogCardDiscrepancy.LogCardDate) Between [forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]) AND ((Count([EmployeeNumber]))>2));

However, its still not happy and I now have a different error message (attached).

 

[plog]

My original code was missing a GROUP BY clause. Here's what it should have been:

SELECT EmployeeNumber
FROM LogCardDiscrepancy
GROUP BY EmployeeNumber
WHERE [Date_of_Report]>=#[Enter Beginning Date]# AND [Date_of_Report]<=#[Enter Ending Date]#
HAVING COUNT(EmployeeNumber) >3

 

[Brianwarnock]

Look at Plog's post, the having clause is essential

Where criteria are applied before aggregation, Having criteria are applied after, in this case after the count. Switch to SQL view and code as per Plog's pos but with your form etc and between clause..

Sorry got to go liverpool's match has restarted

Brian

I see plog has replied , I wondered about the Group By

 

[fat controller]

It still isn't happy - indeed, it won't even let me save the query now. Error message as attached.

SELECT EmployeeNumber
FROM LogCardDiscrepancy
GROUP BY EmployeeNumber
WHERE [LogCardDate]>=#[Enter Beginning Date]# AND [LogCardDate]<=#[Enter Ending Date]#
HAVING COUNT(EmployeeNumber) >3

I have changed the Date_of_Report over to [LogCardDate] simply because that is the date we will be using, but other than that, the code is untouched.

 

[fat controller]

I decided to pinch the WHERE line from another query that works, so it now reads

SELECT EmployeeNumber
FROM LogCardDiscrepancy
GROUP BY EmployeeNumber
WHERE (((LogCardDiscrepancy.LogCardDate) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]))
HAVING COUNT(EmployeeNumber) >3

However, I still get the same error message (Missing Operator) that I had before (attached to previous post)

I am feeling uber-thick now :banghead:

 

[Brianwarnock]

The Where clause comes before the Group by

 

[fat controller]

Yay! Top banana!

OK, it is now playing nicely and I have created a new query and opened both the main query and the sub query as tables

Now, do I just add all the fields from the main one with the exception of EmployeeNumber, adding it from the sub-query?

 

[Brianwarnock]

In a new query join this one to the table on employeenumber and select all fields required from table

Brian

 

[fat controller]

I am almost there with it, however it is chucking up a Enter Parameter box asking for Employee Number (with a space) - way back when this project started, that was how the field was named (and as I have gone on I have learned the error of my ways and changed stuff as I have gone along ) - so, I think that this is some sort of legacy from the report itself.

I will have a footer about with it tomorrow, and will let you know how I go

Thanks for all your help both of you, very much appreciated