My report has =Count(*) in the group header and I want to filter the results.

Report Example:

IDnumber Group Header

Total of Reports Last Name First Name Idnumber
2 Smith Joe 100024
4 Wright Sally 446464

I only want to show the records of reports with the total of 4 or more.

Try this in the query used as source for the report ---
SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
Reports]
FROM YourTable
WHERE Count([Idnumber]) >= 4
GROUP ON [Last Name], [First Name], [Idnumber];
--
KARL DEWEY
Build a little - Test a little


"Sonya" wrote:

> My report has =Count(*) in the group header and I want to filter the results.
>
> Report Example:
>
> IDnumber Group Header
>
> Total of Reports Last Name First Name Idnumber
> 2 Smith Joe 100024
> 4 Wright Sally 446464
>
> I only want to show the records of reports with the total of 4 or more.

I am a Access self learner so not very good at doing code. Here is the
current code for my query. Where would I put the below infromation?

SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
[Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
[Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
[Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
1], [Minor Disciplinary Report].Ccumodsactionsother
FROM [Minor Disciplinary Report]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;


"KARL DEWEY" wrote:

> Try this in the query used as source for the report ---
> SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> Reports]
> FROM YourTable
> WHERE Count([Idnumber]) >= 4
> GROUP ON [Last Name], [First Name], [Idnumber];
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Sonya" wrote:
>
> > My report has =Count(*) in the group header and I want to filter the results.
> >
> > Report Example:
> >
> > IDnumber Group Header
> >
> > Total of Reports Last Name First Name Idnumber
> > 2 Smith Joe 100024
> > 4 Wright Sally 446464
> >
> > I only want to show the records of reports with the total of 4 or more.

----UNTESTED ----
Save this query as [Minor Disciplinary Report Count] --
SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
FROM [Minor Disciplinary Report]
WHERE Count([Idnumber]) >= 4
GROUP ON [Idnumber];

Use this query that joins your table with counting query ---
SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
[Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
[Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
[Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
Report Count].[Total of Reports]
FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
Report].[Idnumber]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;

Be sure to remove any hard returns inserted by the copying, pasting, and
posting.

--
KARL DEWEY
Build a little - Test a little


"Sonya" wrote:

> I am a Access self learner so not very good at doing code. Here is the
> current code for my query. Where would I put the below infromation?
>
> SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> 1], [Minor Disciplinary Report].Ccumodsactionsother
> FROM [Minor Disciplinary Report]
> WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> Report].Dateofreport;
>
>
> "KARL DEWEY" wrote:
>
> > Try this in the query used as source for the report ---
> > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > Reports]
> > FROM YourTable
> > WHERE Count([Idnumber]) >= 4
> > GROUP ON [Last Name], [First Name], [Idnumber];
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "Sonya" wrote:
> >
> > > My report has =Count(*) in the group header and I want to filter the results.
> > >
> > > Report Example:
> > >
> > > IDnumber Group Header
> > >
> > > Total of Reports Last Name First Name Idnumber
> > > 2 Smith Joe 100024
> > > 4 Wright Sally 446464
> > >
> > > I only want to show the records of reports with the total of 4 or more.

Ok, I think I did what you said. I created a new query and named in Minor
Disc Report Count and add the information to the sql. I got an error message
when trying to run the query "Syntax error in Group by clause". Did I do
something wrong?

"KARL DEWEY" wrote:

> ----UNTESTED ----
> Save this query as [Minor Disciplinary Report Count] --
> SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> FROM [Minor Disciplinary Report]
> WHERE Count([Idnumber]) >= 4
> GROUP ON [Idnumber];
>
> Use this query that joins your table with counting query ---
> SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> 1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
> Report Count].[Total of Reports]
> FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
> Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
> Report].[Idnumber]
> WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> Report].Dateofreport;
>
> Be sure to remove any hard returns inserted by the copying, pasting, and
> posting.
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Sonya" wrote:
>
> > I am a Access self learner so not very good at doing code. Here is the
> > current code for my query. Where would I put the below infromation?
> >
> > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > 1], [Minor Disciplinary Report].Ccumodsactionsother
> > FROM [Minor Disciplinary Report]
> > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > Report].Dateofreport;
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > Try this in the query used as source for the report ---
> > > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > > Reports]
> > > FROM YourTable
> > > WHERE Count([Idnumber]) >= 4
> > > GROUP ON [Last Name], [First Name], [Idnumber];
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "Sonya" wrote:
> > >
> > > > My report has =Count(*) in the group header and I want to filter the results.
> > > >
> > > > Report Example:
> > > >
> > > > IDnumber Group Header
> > > >
> > > > Total of Reports Last Name First Name Idnumber
> > > > 2 Smith Joe 100024
> > > > 4 Wright Sally 446464
> > > >
> > > > I only want to show the records of reports with the total of 4 or more.

I changed the Group on to Group by then I get the error message "Cannot have
aggregate function in WHERE clause (Count([Idnumber])>=4)

"Sonya" wrote:

> Ok, I think I did what you said. I created a new query and named in Minor
> Disc Report Count and add the information to the sql. I got an error message
> when trying to run the query "Syntax error in Group by clause". Did I do
> something wrong?
>
> "KARL DEWEY" wrote:
>
> > ----UNTESTED ----
> > Save this query as [Minor Disciplinary Report Count] --
> > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > FROM [Minor Disciplinary Report]
> > WHERE Count([Idnumber]) >= 4
> > GROUP ON [Idnumber];
> >
> > Use this query that joins your table with counting query ---
> > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > 1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
> > Report Count].[Total of Reports]
> > FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
> > Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
> > Report].[Idnumber]
> > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > Report].Dateofreport;
> >
> > Be sure to remove any hard returns inserted by the copying, pasting, and
> > posting.
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "Sonya" wrote:
> >
> > > I am a Access self learner so not very good at doing code. Here is the
> > > current code for my query. Where would I put the below infromation?
> > >
> > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > 1], [Minor Disciplinary Report].Ccumodsactionsother
> > > FROM [Minor Disciplinary Report]
> > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > Report].Dateofreport;
> > >
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > Try this in the query used as source for the report ---
> > > > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > > > Reports]
> > > > FROM YourTable
> > > > WHERE Count([Idnumber]) >= 4
> > > > GROUP ON [Last Name], [First Name], [Idnumber];
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "Sonya" wrote:
> > > >
> > > > > My report has =Count(*) in the group header and I want to filter the results.
> > > > >
> > > > > Report Example:
> > > > >
> > > > > IDnumber Group Header
> > > > >
> > > > > Total of Reports Last Name First Name Idnumber
> > > > > 2 Smith Joe 100024
> > > > > 4 Wright Sally 446464
> > > > >
> > > > > I only want to show the records of reports with the total of 4 or more.

Try it this way ---
SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
FROM [Minor Disciplinary Report]
GROUP ON [Idnumber]
HAVING Count([Idnumber]) >=4;

--
KARL DEWEY
Build a little - Test a little


"Sonya" wrote:

> I changed the Group on to Group by then I get the error message "Cannot have
> aggregate function in WHERE clause (Count([Idnumber])>=4)
>
> "Sonya" wrote:
>
> > Ok, I think I did what you said. I created a new query and named in Minor
> > Disc Report Count and add the information to the sql. I got an error message
> > when trying to run the query "Syntax error in Group by clause". Did I do
> > something wrong?
> >
> > "KARL DEWEY" wrote:
> >
> > > ----UNTESTED ----
> > > Save this query as [Minor Disciplinary Report Count] --
> > > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > > FROM [Minor Disciplinary Report]
> > > WHERE Count([Idnumber]) >= 4
> > > GROUP ON [Idnumber];
> > >
> > > Use this query that joins your table with counting query ---
> > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > 1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
> > > Report Count].[Total of Reports]
> > > FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
> > > Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
> > > Report].[Idnumber]
> > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > Report].Dateofreport;
> > >
> > > Be sure to remove any hard returns inserted by the copying, pasting, and
> > > posting.
> > >
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "Sonya" wrote:
> > >
> > > > I am a Access self learner so not very good at doing code. Here is the
> > > > current code for my query. Where would I put the below infromation?
> > > >
> > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > 1], [Minor Disciplinary Report].Ccumodsactionsother
> > > > FROM [Minor Disciplinary Report]
> > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > Report].Dateofreport;
> > > >
> > > >
> > > > "KARL DEWEY" wrote:
> > > >
> > > > > Try this in the query used as source for the report ---
> > > > > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > > > > Reports]
> > > > > FROM YourTable
> > > > > WHERE Count([Idnumber]) >= 4
> > > > > GROUP ON [Last Name], [First Name], [Idnumber];
> > > > > --
> > > > > KARL DEWEY
> > > > > Build a little - Test a little
> > > > >
> > > > >
> > > > > "Sonya" wrote:
> > > > >
> > > > > > My report has =Count(*) in the group header and I want to filter the results.
> > > > > >
> > > > > > Report Example:
> > > > > >
> > > > > > IDnumber Group Header
> > > > > >
> > > > > > Total of Reports Last Name First Name Idnumber
> > > > > > 2 Smith Joe 100024
> > > > > > 4 Wright Sally 446464
> > > > > >
> > > > > > I only want to show the records of reports with the total of 4 or more.

I had to change Group on w/ Group by. But it works; am so excited : ). One
last thing now how do I add that filter into my current report. Because I
need to use the current one as it counts by group and also filters out any
record over 60 days. Thanks so much for your help.

"KARL DEWEY" wrote:

> Try it this way ---
> SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> FROM [Minor Disciplinary Report]
> GROUP ON [Idnumber]
> HAVING Count([Idnumber]) >=4;
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Sonya" wrote:
>
> > I changed the Group on to Group by then I get the error message "Cannot have
> > aggregate function in WHERE clause (Count([Idnumber])>=4)
> >
> > "Sonya" wrote:
> >
> > > Ok, I think I did what you said. I created a new query and named in Minor
> > > Disc Report Count and add the information to the sql. I got an error message
> > > when trying to run the query "Syntax error in Group by clause". Did I do
> > > something wrong?
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > ----UNTESTED ----
> > > > Save this query as [Minor Disciplinary Report Count] --
> > > > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > > > FROM [Minor Disciplinary Report]
> > > > WHERE Count([Idnumber]) >= 4
> > > > GROUP ON [Idnumber];
> > > >
> > > > Use this query that joins your table with counting query ---
> > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > 1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
> > > > Report Count].[Total of Reports]
> > > > FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
> > > > Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
> > > > Report].[Idnumber]
> > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > Report].Dateofreport;
> > > >
> > > > Be sure to remove any hard returns inserted by the copying, pasting, and
> > > > posting.
> > > >
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "Sonya" wrote:
> > > >
> > > > > I am a Access self learner so not very good at doing code. Here is the
> > > > > current code for my query. Where would I put the below infromation?
> > > > >
> > > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > > 1], [Minor Disciplinary Report].Ccumodsactionsother
> > > > > FROM [Minor Disciplinary Report]
> > > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > > Report].Dateofreport;
> > > > >
> > > > >
> > > > > "KARL DEWEY" wrote:
> > > > >
> > > > > > Try this in the query used as source for the report ---
> > > > > > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > > > > > Reports]
> > > > > > FROM YourTable
> > > > > > WHERE Count([Idnumber]) >= 4
> > > > > > GROUP ON [Last Name], [First Name], [Idnumber];
> > > > > > --
> > > > > > KARL DEWEY
> > > > > > Build a little - Test a little
> > > > > >
> > > > > >
> > > > > > "Sonya" wrote:
> > > > > >
> > > > > > > My report has =Count(*) in the group header and I want to filter the results.
> > > > > > >
> > > > > > > Report Example:
> > > > > > >
> > > > > > > IDnumber Group Header
> > > > > > >
> > > > > > > Total of Reports Last Name First Name Idnumber
> > > > > > > 2 Smith Joe 100024
> > > > > > > 4 Wright Sally 446464
> > > > > > >
> > > > > > > I only want to show the records of reports with the total of 4 or more.

Use this query that joins your table with counting query ---
SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
[Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
[Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
[Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
1], [Minor Disciplinary eport].Ccumodsactionsother, [Minor Disc Report
Count].[Total of Reports]
FROM [Minor Disciplinary Report] INNER JOIN [Minor Disc Report Count] ON
[Minor Disc Report Count].[Idnumber] = [Minor Disciplinary Report].[Idnumber]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;

--
KARL DEWEY
Build a little - Test a little


"Sonya" wrote:

> I had to change Group on w/ Group by. But it works; am so excited : ). One
> last thing now how do I add that filter into my current report. Because I
> need to use the current one as it counts by group and also filters out any
> record over 60 days. Thanks so much for your help.
>
> "KARL DEWEY" wrote:
>
> > Try it this way ---
> > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > FROM [Minor Disciplinary Report]
> > GROUP ON [Idnumber]
> > HAVING Count([Idnumber]) >=4;
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "Sonya" wrote:
> >
> > > I changed the Group on to Group by then I get the error message "Cannot have
> > > aggregate function in WHERE clause (Count([Idnumber])>=4)
> > >
> > > "Sonya" wrote:
> > >
> > > > Ok, I think I did what you said. I created a new query and named in Minor
> > > > Disc Report Count and add the information to the sql. I got an error message
> > > > when trying to run the query "Syntax error in Group by clause". Did I do
> > > > something wrong?
> > > >
> > > > "KARL DEWEY" wrote:
> > > >
> > > > > ----UNTESTED ----
> > > > > Save this query as [Minor Disciplinary Report Count] --
> > > > > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > > > > FROM [Minor Disciplinary Report]
> > > > > WHERE Count([Idnumber]) >= 4
> > > > > GROUP ON [Idnumber];
> > > > >
> > > > > Use this query that joins your table with counting query ---
> > > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > > 1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
> > > > > Report Count].[Total of Reports]
> > > > > FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
> > > > > Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
> > > > > Report].[Idnumber]
> > > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > > Report].Dateofreport;
> > > > >
> > > > > Be sure to remove any hard returns inserted by the copying, pasting, and
> > > > > posting.
> > > > >
> > > > > --
> > > > > KARL DEWEY
> > > > > Build a little - Test a little
> > > > >
> > > > >
> > > > > "Sonya" wrote:
> > > > >
> > > > > > I am a Access self learner so not very good at doing code. Here is the
> > > > > > current code for my query. Where would I put the below infromation?
> > > > > >
> > > > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > > > 1], [Minor Disciplinary Report].Ccumodsactionsother
> > > > > > FROM [Minor Disciplinary Report]
> > > > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > > > Report].Dateofreport;
> > > > > >
> > > > > >
> > > > > > "KARL DEWEY" wrote:
> > > > > >
> > > > > > > Try this in the query used as source for the report ---
> > > > > > > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > > > > > > Reports]
> > > > > > > FROM YourTable
> > > > > > > WHERE Count([Idnumber]) >= 4
> > > > > > > GROUP ON [Last Name], [First Name], [Idnumber];
> > > > > > > --
> > > > > > > KARL DEWEY
> > > > > > > Build a little - Test a little
> > > > > > >
> > > > > > >
> > > > > > > "Sonya" wrote:
> > > > > > >
> > > > > > > > My report has =Count(*) in the group header and I want to filter the results.
> > > > > > > >
> > > > > > > > Report Example:
> > > > > > > >
> > > > > > > > IDnumber Group Header
> > > > > > > >
> > > > > > > > Total of Reports Last Name First Name Idnumber
> > > > > > > > 2 Smith Joe 100024
> > > > > > > > 4 Wright Sally 446464
> > > > > > > >
> > > > > > > > I only want to show the records of reports with the total of 4 or more.

I added the count field from my Minor Disciplinary Report Count Query to my
already created Query, but when I try to open my report I get the below error
msg.

"The specified field "[Minor Disciplinary Report].[Last]" could refer to
more than one table listed in the FROM clause of your SQL."

What am I doing wrong?

"KARL DEWEY" wrote:

> Use this query that joins your table with counting query ---
> SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> 1], [Minor Disciplinary eport].Ccumodsactionsother, [Minor Disc Report
> Count].[Total of Reports]
> FROM [Minor Disciplinary Report] INNER JOIN [Minor Disc Report Count] ON
> [Minor Disc Report Count].[Idnumber] = [Minor Disciplinary Report].[Idnumber]
> WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> Report].Dateofreport;
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Sonya" wrote:
>
> > I had to change Group on w/ Group by. But it works; am so excited : ). One
> > last thing now how do I add that filter into my current report. Because I
> > need to use the current one as it counts by group and also filters out any
> > record over 60 days. Thanks so much for your help.
> >
> > "KARL DEWEY" wrote:
> >
> > > Try it this way ---
> > > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > > FROM [Minor Disciplinary Report]
> > > GROUP ON [Idnumber]
> > > HAVING Count([Idnumber]) >=4;
> > >
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "Sonya" wrote:
> > >
> > > > I changed the Group on to Group by then I get the error message "Cannot have
> > > > aggregate function in WHERE clause (Count([Idnumber])>=4)
> > > >
> > > > "Sonya" wrote:
> > > >
> > > > > Ok, I think I did what you said. I created a new query and named in Minor
> > > > > Disc Report Count and add the information to the sql. I got an error message
> > > > > when trying to run the query "Syntax error in Group by clause". Did I do
> > > > > something wrong?
> > > > >
> > > > > "KARL DEWEY" wrote:
> > > > >
> > > > > > ----UNTESTED ----
> > > > > > Save this query as [Minor Disciplinary Report Count] --
> > > > > > SELECT [Idnumber], Count([Idnumber]) AS [Total of Reports]
> > > > > > FROM [Minor Disciplinary Report]
> > > > > > WHERE Count([Idnumber]) >= 4
> > > > > > GROUP ON [Idnumber];
> > > > > >
> > > > > > Use this query that joins your table with counting query ---
> > > > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > > > 1], [Minor Disciplinary Report].Ccumodsactionsother, [Minor Disciplinary
> > > > > > Report Count].[Total of Reports]
> > > > > > FROM [Minor Disciplinary Report] INNER JOIN [Minor Disciplinary Report
> > > > > > Count] ON [Minor Disciplinary Report Count].[Idnumber] = [Minor Disciplinary
> > > > > > Report].[Idnumber]
> > > > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > > > Report].Dateofreport;
> > > > > >
> > > > > > Be sure to remove any hard returns inserted by the copying, pasting, and
> > > > > > posting.
> > > > > >
> > > > > > --
> > > > > > KARL DEWEY
> > > > > > Build a little - Test a little
> > > > > >
> > > > > >
> > > > > > "Sonya" wrote:
> > > > > >
> > > > > > > I am a Access self learner so not very good at doing code. Here is the
> > > > > > > current code for my query. Where would I put the below infromation?
> > > > > > >
> > > > > > > SELECT [Minor Disciplinary Report].Last, [Minor Disciplinary Report].First,
> > > > > > > [Minor Disciplinary Report].IDnumber, [Minor Disciplinary Report].Unit,
> > > > > > > [Minor Disciplinary Report].Dateofreport, [Minor Disciplinary
> > > > > > > Report].[Gpsanction 1], [Minor Disciplinary Report].[Asddsanctions 1], [Minor
> > > > > > > Disciplinary Report].[Ccusanctions 1], [Minor Disciplinary
> > > > > > > Report].Ccusanctionsother, [Minor Disciplinary Report].Reviewed, [Minor
> > > > > > > Disciplinary Report].Reviewdate, [Minor Disciplinary Report].Reviewdecision,
> > > > > > > [Minor Disciplinary Report].[Gpmodsanction 1], [Minor Disciplinary
> > > > > > > Report].[Asddmodsanctions 1], [Minor Disciplinary Report].[Ccumodsanctions
> > > > > > > 1], [Minor Disciplinary Report].Ccumodsactionsother
> > > > > > > FROM [Minor Disciplinary Report]
> > > > > > > WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
> > > > > > > AND ((60)>=DateDiff("d",[Dateofreport],Now())))
> > > > > > > ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
> > > > > > > Report].Dateofreport;
> > > > > > >
> > > > > > >
> > > > > > > "KARL DEWEY" wrote:
> > > > > > >
> > > > > > > > Try this in the query used as source for the report ---
> > > > > > > > SELECT [Last Name], [First Name], [Idnumber], Count([Idnumber]) AS [Total of
> > > > > > > > Reports]
> > > > > > > > FROM YourTable
> > > > > > > > WHERE Count([Idnumber]) >= 4
> > > > > > > > GROUP ON [Last Name], [First Name], [Idnumber];
> > > > > > > > --
> > > > > > > > KARL DEWEY
> > > > > > > > Build a little - Test a little
> > > > > > > >
> > > > > > > >
> > > > > > > > "Sonya" wrote:
> > > > > > > >
> > > > > > > > > My report has =Count(*) in the group header and I want to filter the results.
> > > > > > > > >
> > > > > > > > > Report Example:
> > > > > > > > >
> > > > > > > > > IDnumber Group Header
> > > > > > > > >
> > > > > > > > > Total of Reports Last Name First Name Idnumber
> > > > > > > > > 2 Smith Joe 100024
> > > > > > > > > 4 Wright Sally 446464
> > > > > > > > >
> > > > > > > > > I only want to show the records of reports with the total of 4 or more.