Do not display record if grouping count equals X

  • Thread starter Thread starter david.isaacks
  • Start date Start date
D

david.isaacks

I have a list of records which I group by [FullName].
If the records associted with the same [FullName] equals 4 or greater
then I do not want that record to show on the report.

I have tried not showing them on format with If/Then, but have had no
luck. Is this possible?

Thanks,
David
 
Can you create a totals query that groups by FullName and Counts FullName.
Add this query to your report's record source and set the criteria under
Count Of FullName to
<4
 
Yes I did that, but I want to be able to not show the record based on
three different values.

If Status is Normal <4
If Status is New <8
If Status is At Risk <8

I can get it to work with only one, but not all three.

David

Duane said:
Can you create a totals query that groups by FullName and Counts FullName.
Add this query to your report's record source and set the criteria under
Count Of FullName to
<4


--
Duane Hookom
MS Access MVP

I have a list of records which I group by [FullName].
If the records associted with the same [FullName] equals 4 or greater
then I do not want that record to show on the report.

I have tried not showing them on format with If/Then, but have had no
luck. Is this possible?

Thanks,
David
Click to expand...
Click to expand...
 
Where did the status field come from? Is this stored some where or does a
user select it on a form?

Please provide complete specifications. Some sample records sometimes helps.
Also table structures...

--
Duane Hookom
MS Access MVP

Yes I did that, but I want to be able to not show the record based on
three different values.

If Status is Normal <4
If Status is New <8
If Status is At Risk <8

I can get it to work with only one, but not all three.

David

Duane said:
Can you create a totals query that groups by FullName and Counts
FullName.
Add this query to your report's record source and set the criteria under
Count Of FullName to
<4


--
Duane Hookom
MS Access MVP

I have a list of records which I group by [FullName].
If the records associted with the same [FullName] equals 4 or greater
then I do not want that record to show on the report.

I have tried not showing them on format with If/Then, but have had no
luck. Is this possible?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
I think I got it to work.

SELECT [LastName] & ", " & [FirstName] AS [Full Name],
Contacts.StatusJuly, Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Count(Calls.Completed) AS CountOfCompleted, Contacts.ContactTypeID,
Contacts.[Assigned Monitor]
FROM Contacts LEFT JOIN Calls ON Contacts.ContactID=Calls.ContactID
GROUP BY [LastName] & ", " & [FirstName], Contacts.StatusJuly,
Contacts.ContactTypeID, Contacts.[Assigned Monitor]
HAVING (((Count(Calls.Completed))<4 And [StatusJuly]="Normal")) OR
(((Count(Calls.Completed))<8 And [StatusJuly]="New")) OR
(((Count(Calls.Completed))<8 And [StatusJuly]="At Risk"));

StatusJuly is a field in the query.

David

Duane said:
Where did the status field come from? Is this stored some where or does a
user select it on a form?

Please provide complete specifications. Some sample records sometimes helps.
Also table structures...

--
Duane Hookom
MS Access MVP

Yes I did that, but I want to be able to not show the record based on
three different values.

If Status is Normal <4
If Status is New <8
If Status is At Risk <8

I can get it to work with only one, but not all three.

David

Duane said:
Can you create a totals query that groups by FullName and Counts
FullName.
Add this query to your report's record source and set the criteria under
Count Of FullName to
<4


--
Duane Hookom
MS Access MVP

I have a list of records which I group by [FullName].
If the records associted with the same [FullName] equals 4 or greater
then I do not want that record to show on the report.

I have tried not showing them on format with If/Then, but have had no
luck. Is this possible?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Glad to hear you have this working. I would prefer to not "hard-code" values
like this into expressions. I would hate to have to maintain this expression
if the numbers 4 and 8 change (or should I say "when the numbers change").

--
Duane Hookom
MS Access MVP

I think I got it to work.

SELECT [LastName] & ", " & [FirstName] AS [Full Name],
Contacts.StatusJuly, Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Count(Calls.Completed) AS CountOfCompleted, Contacts.ContactTypeID,
Contacts.[Assigned Monitor]
FROM Contacts LEFT JOIN Calls ON Contacts.ContactID=Calls.ContactID
GROUP BY [LastName] & ", " & [FirstName], Contacts.StatusJuly,
Contacts.ContactTypeID, Contacts.[Assigned Monitor]
HAVING (((Count(Calls.Completed))<4 And [StatusJuly]="Normal")) OR
(((Count(Calls.Completed))<8 And [StatusJuly]="New")) OR
(((Count(Calls.Completed))<8 And [StatusJuly]="At Risk"));

StatusJuly is a field in the query.

David

Duane said:
Where did the status field come from? Is this stored some where or does a
user select it on a form?

Please provide complete specifications. Some sample records sometimes
helps.
Also table structures...

--
Duane Hookom
MS Access MVP

Yes I did that, but I want to be able to not show the record based on
three different values.

If Status is Normal <4
If Status is New <8
If Status is At Risk <8

I can get it to work with only one, but not all three.

David

Duane Hookom wrote:
Can you create a totals query that groups by FullName and Counts
FullName.
Add this query to your report's record source and set the criteria
under
Count Of FullName to
<4


--
Duane Hookom
MS Access MVP

I have a list of records which I group by [FullName].
If the records associted with the same [FullName] equals 4 or
greater
then I do not want that record to show on the report.

I have tried not showing them on format with If/Then, but have had
no
luck. Is this possible?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top