Im not sure if these snapshots will help
Im not sure if these snapshots will help
Create a new query, go to sql view, and paste in the SQL code I provided in post #15.
What did I do wrong?
https://fcssfl-my.sharepoint.com/:u:/g/personal/dsnyder_fcssecurity_us/EXbW9iaeCCJJp7G6snNLVI0BrhjvLpZUHlgfDl-1ql3ovA?e=FtZICn
Still no luck...
What am I doing wrong
You did nothing wrong --it's my mistake. I should have group by EmpID.
Try:
SELECT EmpID
,Count(EmpID) as NumWriteups
FROM [tble_Administrative Actions]
WHERE [Date] Between Date and Date - 90
Group by EmpID
Having Count(EmpID) >=3;
I'll check back in the morning.
I would have thought you would have to group by EmpID, not it's Count?
Welshgasman,
Yes, you're correct. I adjusted the sql in post #21.
I can't tell you enough how much I appreciate all the support.
Ok, now the query worked great as you designed it, now I am not sure how to get the report to come out they way I need. I need the report to list the writeups (see screenshot).
Please tell us in plain English what has to be reported and in what format and sequence.
Also, the query is selecting those Employees who have had 3 or more writeups in the last 90 days
Also, please post the SQL used for query1.
Format:
What needs Reported:
A report that lists each write up (Admin Action Record) for any employee [EMPID] that has more than 3 in a 90 day period.
The report should include:
[Date] [FirstName] [LastName] [Category] [Offense]
Is this what your looking for?
Sort of
Is it more than 3 OR 3 or more?
Is the report by Employee Last name and then Date of write up ascending sequence?
Reports have their own sorting and grouping so you'll have to set that up in Report design.
Good luck with your project.
Its More than three
I actually have the report built and was just having issues with the query that the reports calls on. (See Attached)
So it's all working now?
For Reports you may want to review Grouping and sorting.
You can find youtube videos on MS ACCESS REPORT GROUPING SORTING to see some examples and instructions.
All is working now, no words can express my gratitude for the efforts you gave on this.
Thank you