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.
