Only show records where count > 1

[haydenal]

Pardon me if this is a simple question.

I have a report that shows each occurrence for an address and I'm doing a count([address]) to get the total number of occurrences for each address. How can I filter the report so that it only shows the addresses and occurrences where the count > 1 (I don't care about addresses with single occurrences)?

 

[MSAccessRookie]

I am going to assume you are basing your report on a standard query with both COUNT and GROUP BY Statements. Adding HAVING COUNT(*) > 1 after the GROUP BY statement should give you what you want

 

[haydenal]

Again, pardon my ignorance.....

This is the query I'm using:

SELECT [Repeat Locations].INCIDENT, [Repeat Locations].DEPTCLAS, [Repeat Locations].ADDRESS, CDate([Repeat Locations]![OFMONTH] & "/" & [Repeat Locations]![OFDAY] & "/" & (2000+[Repeat Locations]![OFYEAR])) AS [DATE], [Repeat Locations].OFTIME, [Repeat Locations].OFDOW, [Repeat Locations].OFMONTH, [Repeat Locations].OFDAY, [Repeat Locations].OFYEAR
FROM [Repeat Locations]
WHERE (((CDate([Repeat Locations]![OFMONTH] & "/" & [Repeat Locations]![OFDAY] & "/" & (2000+[Repeat Locations]![OFYEAR]))) Between #12/15/2008# And #12/31/2008#));

I'm pretty new to this, I would need a COUNT and GROUP BY after the WHERE?

 

[MSAccessRookie]

Again, pardon my ignorance.....

This is the query I'm using:

SELECT [Repeat Locations].INCIDENT, [Repeat Locations].DEPTCLAS, [Repeat Locations].ADDRESS, CDate([Repeat Locations]![OFMONTH] & "/" & [Repeat Locations]![OFDAY] & "/" & (2000+[Repeat Locations]![OFYEAR])) AS [DATE], [Repeat Locations].OFTIME, [Repeat Locations].OFDOW, [Repeat Locations].OFMONTH, [Repeat Locations].OFDAY, [Repeat Locations].OFYEAR
FROM [Repeat Locations]
WHERE (((CDate([Repeat Locations]![OFMONTH] & "/" & [Repeat Locations]![OFDAY] & "/" & (2000+[Repeat Locations]![OFYEAR]))) Between #12/15/2008# And #12/31/2008#));

I'm pretty new to this, I would need a COUNT and GROUP BY after the WHERE?

Since it looks like the Query was created in Design View, I will try to take you through that method.

[LIST=1]
[*]Bring up the Query in Design View.
[*]On the Query Design  Toolbar, choose the Totals Icon.  Clicking this Icon will add a new row labeled [B]TOTAL:[/B].  Notice that the default value for each column is [B]GROUP BY[/B].
[*]Identify the column(s) that need to be counted, and change the [B]GROUP BY[/B] to [B]COUNT[/B].
[*]Switch to [B]SQL Mode[/B] and add a [B]HAVING[/B] Statement after the [B]GROUP BY[/B] Statement.
[/LIST]

Not that this is untested code, but it shopuld get you pointed in the right direction.

 

[namliam]

Or just -as the last step- add >1 to the criteria of the field(s) that are counted

 

[MSAccessRookie]

Or just -as the last step- add >1 to the criteria of the field(s) that are counted

I was told that the Criteria set the WHERE Clause, causing the test is done before the GROUP BY. I was also told that the WHERE Clause could take much longer that a HAVING Clause in larger sets of data, since there wouild be more records to process.

 

[namliam]

I was told ...I was also told

You were told wrong... on both counts...

Any criteria done on a field/column that is pertaining to sum/avg/count/anyother group by thing like that NEEDS to be in the Having.
You cannot do Count(*) > 1 in the where, invalid syntax...

I suggest you try it yourself and find out!

 

[haydenal]

Thank you, that gives me a lot to play around with.

What I'm not understanding with the count is when I do it I get a count of "1" for every record but I was imagining getting a total number of occurrences. For example, imagine my table has 3 records. Record 1 has an address of 123 Main Street, Record 2 also has a value of 123 Main Street and Record 3 has a value of 456 Main Street. When I use Count I am returned a "1" for each record. What I was expecting was a count of "2" for each of the 123 Main Streets and a count of "1" for the 456 Main Street and then applying the Having Count > 1 from there. Am I overlooking something?

 

[Atomic Shrimp]

Did you turn it into a totalling/grouping query as per the end bit of post #4?

If so, and you're still not getting your records summarised, it usually means you're still including fields that are different for each record - for example, they may both say "123 Main Street" in one of the fields, but if in another field, one says 'Mrs Johnson' and the other says 'Mr Smith', the two records will not be consolidated if that field is included in the query.

 

[haydenal]

Yes, I have a field [INCIDENT] which is a unique id.

Is there a way to get the count without doing 2 separate queries and joining them?

Or, getting back to the report, if I have a text box in the ADDESSS Header of my report with a control source "=Count([ADDRESS])" is there a way to say if that count = 1 (the address only exists once in the query) don't display that address on the report?

 

[Atomic Shrimp]

Can you not just leave the incident field out of the query that does the grouping and counting?

 

[haydenal]

Well, actually, I have several fields (including the incident field) that are unique. I'm intending my report to show multiple incidents per address, what the incident number was, type, date etc. The report also displays the total number of incidents for each address via "=Count([Address])" (which I'm doing with a text box). The report is right where I want it, with the exception that it's including the addresses with count = 1 which really is not what I want on the "multiple incident report."

I set up another query that contained the address field twice one field grouped by and the other counted and then did a third query linking the other two queries by address and pulling the fields I needed including the counted field. This did the trick.