Group by Employee and Project Date (was Access Help)

[mefreeman21]

I have just recently started to learn how to use Access and have been moving along pretty smoothly so far, but I can't find a way around this problem.
I have a list that shows employees and associated projects along with dates of when the project was started. Some employees have zero and some have multiple projects. I need to show the number of projects that are more than 90 days old per employee.
I have created a query to list which projects are older than 90 days, and I planned to use a count function on a subreport to show how many were overdue per person but I can only get it to output the number of overdue projects for the whole group. Does anyone have any suggestions?
Thanks

[Jim Doherty]

I have just recently started to learn how to use Access and have been moving along pretty smoothly so far, but I can't find a way around this problem.
I have a list that shows employees and associated projects along with dates of when the project was started. Some employees have zero and some have multiple projects. I need to show the number of projects that are more than 90 days old per employee.
I have created a query to list which projects are older than 90 days, and I planned to use a count function on a subreport to show how many were overdue per person but I can only get it to output the number of overdue projects for the whole group. Does anyone have any suggestions?
Thanks

If you post the tablename fieldnames and your query syntax thus far we should be able to help unravel it

[missinglinq]

Please remember to provide a meaningful Title for any threads you start! This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions. It is difficult for the experts to answer questions when there is not enough details provided to understand the problem. I have re-titled your post for this reason.

Please take the time to read the Posting Guidelines, paying particular attention to "Give a clear title to your question" as well as "Give as much detail as possible."


Thank you and Welcome to TheScripts!!

Linq ;0)>

[mefreeman21]

I have a query titled, qryEMRAs, with the following fields: name, department, empl_number, emra (emra is a project number), and emra_date_created. I want to be able to output the number of EMRAs that each employee has, the number of EMRAs that are older than 90 days, and the oldest EMRA that is under each employee in a subreport.

For example if:
Name........emra.....................emra_date_cre ated
John..........1-2400-7-5036........7/23/2007
John..........1-2821-7-5004........3/28/2007
John..........6-2560-7-5004........6/17/2007
Then I want the following to be in a report
Employee....Number of EMRAs....EMRAs Overdue....Oldest in Que
John........................3..................... ........1.........................153

I currently have a report titled, subrptEMRAs, that outputs the number of projects by using =Count([empl_number]) in a text box, and the oldest project under each employee by using =Max(Date()-[emra_date_created]) in a text box. qryEMRAs is the Record Source for this report.
I can't get the number that are overdue in the report.

I appreciate your help. Sorry about my lousy first post. I hope this is a little more clear.

[missinglinq]

Much improved, mefreeman21, as are your chances for help from the members here,now that your problem has been restated! I do very little of the type of reporting you're attempting here, so I won't jump in and muddy up the waters, but will leave you to some of our members who do such!

Good luck!

Linq ;0)>

[Jim Doherty]

I have a query titled, qryEMRAs, with the following fields: name, department, empl_number, emra (emra is a project number), and emra_date_created. I want to be able to output the number of EMRAs that each employee has, the number of EMRAs that are older than 90 days, and the oldest EMRA that is under each employee in a subreport.

For example if:
Name........emra.....................emra_date_cre ated
John..........1-2400-7-5036........7/23/2007
John..........1-2821-7-5004........3/28/2007
John..........6-2560-7-5004........6/17/2007
Then I want the following to be in a report
Employee....Number of EMRAs....EMRAs Overdue....Oldest in Que
John........................3..................... ........1.........................153

I currently have a report titled, subrptEMRAs, that outputs the number of projects by using =Count([empl_number]) in a text box, and the oldest project under each employee by using =Max(Date()-[emra_date_created]) in a text box. qryEMRAs is the Record Source for this report.
I can't get the number that are overdue in the report.

I appreciate your help. Sorry about my lousy first post. I hope this is a little more clear.

OK ...two main parts here that assumes you have a table called tblEMRA

This is the first bit of sql... paste it into the SQL window save explicitly as a query called qryEMRAs. This gives a defined list of employees and uses the DATEDIFF function to give a column outlining the number of days existing between the emra_date_created date and todays date. This query you can use for something else I guess, thats why I haven't subqueried it as part of one huge block of SQL

SELECT tblEMRA.Name, tblEMRA.department, tblEMRA.empl_number, tblEMRA.emra, DateDiff("d",[emra_date_created],Date()) AS DaysSinceStart
FROM tblEMRA;

Second part

Now paste this as a separate query and save it to any name you like.
It references the first query (qryEMRAs) explicitly so thats why the naming of the first was necessary. It gives you the output you required (which of course you can parametise later if you wish, should the ceiling for the number of days change. You can base your report on this query. It only counts on all employees that have had an EMRA created in the 90 day leading up to today in other words not a count on every employee whether or not they have had an emra created subtle difference. I sort of assumed you were only interested in those that HAD emras created. Thats why its helpful to post as much info as you can in order to work the logic .

SELECT emras.name, (Select Count(emra) from qryEMRAS where [name]=Emras.[Name]) AS EMRAS, (Select Count(name) from tblEMra where [name]=Emras.[Name] and Datediff("d",emra_date_created,Date())>90) AS [EMRAs Overdue], Max(emras.DaysSinceStart) AS OldestInQue
FROM [SELECT tblEMRA.name, tblEMRA.department, tblEMRA.empl_number, tblEMRA.emra, DateDiff("d",[emra_date_created],Date()) AS DaysSinceStart
FROM tblEMRA
WHERE (((DateDiff("d",[emra_date_created],Date()))>90))]. AS emras
GROUP BY emras.name;

Hope this helps you

Regards

Jim

[mefreeman21]

Thank-you so much Jim! That worked great!

Thanks for teaching me a fair bit about SQL also.

Much appreciated.
-mefreeman21

[Jim Doherty]

Thank-you so much Jim! That worked great!

Thanks for teaching me a fair bit about SQL also.

Much appreciated.
-mefreeman21

You're welcome I'm glad it helped you out :)