Count and Filter Result

[Sonya]

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.

 

[KARL DEWEY]

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];

 

[Sonya]

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;

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

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.

 

[KARL DEWEY]

----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

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;

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

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.

 

[Sonya]

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?

----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

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;

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


:

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.

 

[Sonya]

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

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?

----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

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;


:

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


:

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.

 

[KARL DEWEY]

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

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

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?

----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


:

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;


:

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


:

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.

 

[Sonya]

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.

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

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

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?

:

----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


:

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;


:

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


:

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.

 

[KARL DEWEY]

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

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.

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

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[Sonya]

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?

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

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.

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[KARL DEWEY]

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little

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?

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

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[Sonya]

Count Query: Minor Disciplinary Report Count
Query source for report: Total of Reports in 60 days (below)

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], [Minor Disciplinary Report Count]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little

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?

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


:

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[KARL DEWEY]

I do not see where the error might be comming from.
Run the counting query alone to check there.
Run the second query to check.

You did not join [Minor Disciplinary Report] and [Minor Disciplinary Report
Count]
in the FROM as I said.
--
KARL DEWEY
Build a little - Test a little

Count Query: Minor Disciplinary Report Count
Query source for report: Total of Reports in 60 days (below)

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], [Minor Disciplinary Report Count]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little

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?

:

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


:

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[Sonya]

Separately the queries work great. I wasn't sure what you meant by join, but
after your last e-mail I seen where you put Inner Join in the equation.
Below is my new sql stuff and now I am getting an the error: Syntax error in
JOIN operation. SQL is so confusing...

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] INNER JOIN [Minor Disc 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;

I do not see where the error might be comming from.
Run the counting query alone to check there.
Run the second query to check.

You did not join [Minor Disciplinary Report] and [Minor Disciplinary Report
Count]
in the FROM as I said.
--
KARL DEWEY
Build a little - Test a little

Count Query: Minor Disciplinary Report Count
Query source for report: Total of Reports in 60 days (below)

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], [Minor Disciplinary Report Count]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

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?

:

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


:

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[KARL DEWEY]

Make sure there is no hard return in the middle of the table name as below --
........ = [Minor Disciplinary
Report].[Idnumber]

It happens in copying, pasting, and posting sometimes.

--
KARL DEWEY
Build a little - Test a little

Separately the queries work great. I wasn't sure what you meant by join, but
after your last e-mail I seen where you put Inner Join in the equation.
Below is my new sql stuff and now I am getting an the error: Syntax error in
JOIN operation. SQL is so confusing...

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] INNER JOIN [Minor Disc 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;

I do not see where the error might be comming from.
Run the counting query alone to check there.
Run the second query to check.

You did not join [Minor Disciplinary Report] and [Minor Disciplinary Report
Count]
in the FROM as I said.
--
KARL DEWEY
Build a little - Test a little

Count Query: Minor Disciplinary Report Count
Query source for report: Total of Reports in 60 days (below)

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], [Minor Disciplinary Report Count]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;


:

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

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?

:

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


:

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[Sonya]

I think I got the query to work. No longer getting an error message there.
But when I run my report, I get this error message "The specified field
"[Minor Disciplinary Report].[Last]" could refer to more than one table in
the FROM clause of your SQL statement.

Sql:

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] 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;

Make sure there is no hard return in the middle of the table name as below --
....... = [Minor Disciplinary
Report].[Idnumber]

It happens in copying, pasting, and posting sometimes.

--
KARL DEWEY
Build a little - Test a little

Separately the queries work great. I wasn't sure what you meant by join, but
after your last e-mail I seen where you put Inner Join in the equation.
Below is my new sql stuff and now I am getting an the error: Syntax error in
JOIN operation. SQL is so confusing...

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] INNER JOIN [Minor Disc 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;

I do not see where the error might be comming from.
Run the counting query alone to check there.
Run the second query to check.

You did not join [Minor Disciplinary Report] and [Minor Disciplinary Report
Count]
in the FROM as I said.
--
KARL DEWEY
Build a little - Test a little


:

Count Query: Minor Disciplinary Report Count
Query source for report: Total of Reports in 60 days (below)

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], [Minor Disciplinary Report Count]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;


:

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

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?

:

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


:

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.

 

[KARL DEWEY]

I would guess that in your report there is an object that is using [Minor
Disciplinary Report].[Last] and it is confused.
All I can suggest is to check your report objects sources.
--
KARL DEWEY
Build a little - Test a little

I think I got the query to work. No longer getting an error message there.
But when I run my report, I get this error message "The specified field
"[Minor Disciplinary Report].[Last]" could refer to more than one table in
the FROM clause of your SQL statement.

Sql:

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] 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;

Make sure there is no hard return in the middle of the table name as below --
....... = [Minor Disciplinary
Report].[Idnumber]

It happens in copying, pasting, and posting sometimes.

--
KARL DEWEY
Build a little - Test a little

Separately the queries work great. I wasn't sure what you meant by join, but
after your last e-mail I seen where you put Inner Join in the equation.
Below is my new sql stuff and now I am getting an the error: Syntax error in
JOIN operation. SQL is so confusing...

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] INNER JOIN [Minor Disc 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;

:

I do not see where the error might be comming from.
Run the counting query alone to check there.
Run the second query to check.

You did not join [Minor Disciplinary Report] and [Minor Disciplinary Report
Count]
in the FROM as I said.
--
KARL DEWEY
Build a little - Test a little


:

Count Query: Minor Disciplinary Report Count
Query source for report: Total of Reports in 60 days (below)

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], [Minor Disciplinary Report Count]
WHERE ((([Minor Disciplinary Report].Reviewdecision) Not Like "*dismissed*")
AND ((60)>=DateDiff("d",[Dateofreport],Now())))
ORDER BY [Minor Disciplinary Report].Last, [Minor Disciplinary
Report].Dateofreport;


:

I can not see what might be wrong if you do not post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

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?

:

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


:

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.

:

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


:

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

:

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?

:

----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


:

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;


:

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


:

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.