Count(*) function

[FJM]

I am using the Count(*) function in a report to show me how many times any particular part appears during a selected period.
Quite a few entries only give the result as 1 and I would like to restrict what appears on the report to only show where the Count(*) total for each part in question is greater than 2 (or any other number I choose).
Can anyone help with the solution ?

[JoeM]

If you do your counts in query (using the Aggregate Function), simply add "> 2" (without the quotes) in the Criteria section of that Aggregate field.

[FJM]

That was one of the first ways I tried ( I think - I tried the Sigma option on the Group by in the query), but as it was not successful, I used the wizard to construct the report which then automatically added the Count(*) function.
Any thoughts ?

[JoeM]

Let's see if we can get your query to work out. Take another crack at it, then change your query to SQL View and post the SQL code here.
And explain what it is returning that isn't quite right, and we'll see if we can help you straighten it out.

[FJM]

SELECT NCRFORM.NCRNO, NCRFORM.Date, NCRFORM.CPEPARTNO, NCRFORM.QTYFROM NCRFORM
GROUP BY NCRFORM.NCRNO, NCRFORM.Date, NCRFORM.CPEPARTNO, NCRFORM.QTY
HAVING (((NCRFORM.Date) Between [Forms]![Non Conf Reports (Switchboard page)]![Combo91] And [Forms]![Non Conf Reports (Switchboard page)]![Combo93]) AND ((NCRFORM.CPEPARTNO)>"2"));

The report I run asks for 2 dates (Combo91 and Combo93).It then lists out all of the instances of CPEPARTNO (and sub orders by NCRNO) and shows a Count for the number of times the CPEPARTNO has appeared. There are many items where the count is low at 1 or 2 and I wanted to eliminate these from the report.
Hope this makes sense

[JoeM]

A few issues. I think you are returning too many fields. If you group on all of these fields, you are going to have a separate entry for each date, instead of a total for that date range (unless that is what you want?).

Also, you want to check on the Aggregate (Count) is greater than 2, not the field before it is aggregated.

See if this returns what you want:

SELECT 
	NCRFORM.CPEPARTNO, 
	COUNT(NCRFORM.CPEPARTNO)
FROM 
	NCRFORM
WHERE
	NCRFORM.Date Between [Forms]![Non Conf Reports (Switchboard page)]![Combo91] And [Forms]![Non Conf Reports (Switchboard page)]![Combo93]
GROUP BY 
	NCRFORM.CPEPARTNO
HAVING 
	COUNT(NCRFORM.CPEPARTNO)>2;

[FJM]

That works good for the numbers, but ideally, I would like to keep the NCRNO field as well as this tells me where each of the entries comes from. Is that possible ? .

[JoeM]

That depends on your relationship between CPEPARTNO and NCRNO. If it is a one-to-one relationship, adding it in like this should be fine:

SELECT 
        NCRFORM.NCRNO,	
        NCRFORM.CPEPARTNO, 
	COUNT(NCRFORM.CPEPARTNO)
FROM 
	NCRFORM
WHERE
	NCRFORM.Date Between [Forms]![Non Conf Reports (Switchboard page)]![Combo91] And [Forms]![Non Conf Reports (Switchboard page)]![Combo93]
GROUP BY 
        NCRFORM.NCRNO,	
        NCRFORM.CPEPARTNO
HAVING 
	COUNT(NCRFORM.CPEPARTNO)>2;

If it is not a one-to-one relationship, it may break down your counts into more subcatergories that you do not want.
If that is the case, how do you decide which value of NCRNO you want to return, since there can be multiple NCRNO values for each CPEPARTNO value?

[FJM]

I think this is the root of the problem. The relationship is a one-many. The value returned for the total of each CPEPARTNO comes from the individual times that it appears in NCRNO.
To put it in english, what I am trying to achieve is this.
I have a Quality database, part of which lists what is known as an NCR (a Non Conformance Report. This is the NCRNO).
Each NCR is then made up of a Partnumber (This is the CPEPARTNO).
Over the course of any period, I want to find out if any particular part keeps on repeating to see if I have a problem. I am not bothered if the total number of NCR's against any part is either 1 or 2 but I would be interested in numbers higher than that.
I want to then run a report which shows the part number, the total number of times it appears (i.e the Count figure) and then so I can further investigate, what the individual NCR numbers are which make up the total.

[June7]

You want to know if the CPEPARTNO is associated with multiple NCR? So you want to count the number of NCR for each CPEPARTNO?

Consider a report instead of aggregate query. Use report Grouping & Sorting with aggregate calc in footer. Report allows display of detail info as well as summary calcs.

[FJM]

I understand that the group can give me a total - I have used this in other reports. The problem I am having is that in order to restrict the query to totals of 3 or more using the code you showed me , the query then doesn't include the field NCRNO which means I cannot group on it - or am I missing something ?

[JoeM]

What happens when you use the code I posted in post #8? Does it do what you want?

If not, it might be really helpful to see a small example of your data, along with what you would like your expected output to look like.

[Mahendra1000]

A few issues. I think you are returning too many fields. If you group on all of these fields, you are going to have a separate entry for each date, instead of a total for that date range (unless that is what you want?).

Also, you want to check on the Aggregate (Count) is greater than 2, not the field before it is aggregated.

See if this returns what you want:

SELECT 
    NCRFORM.CPEPARTNO, 
    COUNT(NCRFORM.CPEPARTNO)
FROM 
    NCRFORM
WHERE
    NCRFORM.Date Between [Forms]![Non Conf Reports (Switchboard page)]![Combo91] And [Forms]![Non Conf Reports (Switchboard page)]![Combo93]
GROUP BY 
    NCRFORM.CPEPARTNO
HAVING 
    COUNT(NCRFORM.CPEPARTNO)>2;

I will get back to you on this soon..

[JoeM]

I think that it should be like this

It really doesn't matter which field you choose to count, as long as you choose a populated field (blanks/nulls could cause issues).
The Count is just returning the number of records returned by the Grouping, so you can put any populated field in there. I often choose the same field you are Grouping on.

[Mahendra1000]

Thanks for letting me know on this.