bkthom
asked on
Grand total rolled up to get monthly amount.
On my report I am using subreports and subqueries. But I have two fields outside of those "sub-objects". I am working with Project Mgrs and trying to find the number of months (over six months MAX) that they are delinquent.
What I am getting instead is the sum of each Project for each Project Manager that is delinquent. So for example, PM Jim has five months of data and twenty projects. If none of his projects were delinquent then he would get a "0" if even one project were delinquent he would get a "1". Then I would like to sum up the "Number of Months" a Project Manager has been delinquent with even ONE account. And in my example the maximum PM Jim could have is five months delinquent and the minimum is zero months. He could be delinquent all months but the final (fifth month) so he would have four "1"'s or "4". Instead I am getting numbers like "84" or "90" when I should be getting 4 instead.
These are outside of my subqueries/subreports.
Can anyone help??
Thanks,
Brett
What I am getting instead is the sum of each Project for each Project Manager that is delinquent. So for example, PM Jim has five months of data and twenty projects. If none of his projects were delinquent then he would get a "0" if even one project were delinquent he would get a "1". Then I would like to sum up the "Number of Months" a Project Manager has been delinquent with even ONE account. And in my example the maximum PM Jim could have is five months delinquent and the minimum is zero months. He could be delinquent all months but the final (fifth month) so he would have four "1"'s or "4". Instead I am getting numbers like "84" or "90" when I should be getting 4 instead.
These are outside of my subqueries/subreports.
Can anyone help??
Thanks,
Brett
@Jared_S,
Hey Jared, I hope you saw my last note to you in the OP's previous question. I'm going to have a go at this since this has to do with the same report as before and I'm already familiar with the background.
@bkthom
Since you are basing the number of months delinquent as over the past 6 months rather the past 3, we cannot make use of your existing query to do a DLookup for this information.
So create another query called qry_ProjMgrs_DelinqenciesP ast6Mo or whatever you like with this SQL code:
Hey Jared, I hope you saw my last note to you in the OP's previous question. I'm going to have a go at this since this has to do with the same report as before and I'm already familiar with the background.
@bkthom
Since you are basing the number of months delinquent as over the past 6 months rather the past 3, we cannot make use of your existing query to do a DLookup for this information.
So create another query called qry_ProjMgrs_DelinqenciesP
SELECT Division, [Project Manager], Sum(IIf([MonthDelqStatus]>0,1,0)) AS 6MonthDelqStatus
FROM tbl_ProjManager_HISTORY
WHERE ((([Data Date])>DateAdd("m",-6,Date())))
GROUP BY Division, [Project Manager]
Then in your subreport, add a textbox beside the ProjectManager with this expression:=DLookUp("[6MonthDelqStatus]","qry_ProjMgrs_DelinqenciesPast6Mo","[Project Manager]='" & [Project Manager] & "' And [Division]='" & [Division] & "'")
Please do - thanks for the note!
ASKER
IrogSinta and Jared_S,
Both of you are amazing. This question has taken on a life of its own!
The SQL looks good. But I just got out of a very long meeting and do not have time to work on the report right now so I will try it in the morning (about 11 hrs from now). I am sure that it will work great. IrogSinta - you are absolutely amazing. I don't know how you can step into someone's complexity of an issue and resolve it. I get lost in my own work. I guess that is what makes you an Expert.
I am sorry that I did not mention the six month issue. Currentl;y, I only have two months of data and on the report, the end-user will only see the three months but in those two fields "MonthDelqStatus" and "%DelqMonths" (or something along those lines) , those fields will eventually pull from six months worth of data. Sorry to change the report logic like that on you. Whoops, I really have to go.
But, I do apologize, I wanted to write something quickly so you did not feel like I was not getting back to you. I REALLY do appreciate all of the hard work you have put into this IrogSinta, YOU ARE AMAZING. I will talk to you in the morning.
Again, MANY, MANY thanks in advance.
Regards,
Brett
Both of you are amazing. This question has taken on a life of its own!
The SQL looks good. But I just got out of a very long meeting and do not have time to work on the report right now so I will try it in the morning (about 11 hrs from now). I am sure that it will work great. IrogSinta - you are absolutely amazing. I don't know how you can step into someone's complexity of an issue and resolve it. I get lost in my own work. I guess that is what makes you an Expert.
I am sorry that I did not mention the six month issue. Currentl;y, I only have two months of data and on the report, the end-user will only see the three months but in those two fields "MonthDelqStatus" and "%DelqMonths" (or something along those lines) , those fields will eventually pull from six months worth of data. Sorry to change the report logic like that on you. Whoops, I really have to go.
But, I do apologize, I wanted to write something quickly so you did not feel like I was not getting back to you. I REALLY do appreciate all of the hard work you have put into this IrogSinta, YOU ARE AMAZING. I will talk to you in the morning.
Again, MANY, MANY thanks in advance.
Regards,
Brett
ASKER
IrogSinta -
To get back to you on your SQL, this is the same type of result that I have been getting - just a sum of delinquent projects. Please go back to my original question.
The end-user does not care about the number of projects delinquent. I am wondering if there a way to know out of (for example) six months, how many months has this particular ProgMgr been delinquent. It skews the results in favor of those ProjMgrs with less projects since of course the higher the number of project one has the higher the probability of default on one of those projects.
So, for the MonthDelqStatus field I need a way to CALCULATE the SUM of the NUMBER OF MONTHS by using a toggle "0" and "1" or WHATEVER to determine a month when a PM is not delinquent => "MonthDelqStatus = 0" AND a month when a PM is delinquent "MonthDelqStatus = 1" regardless of how many projects the PM is working on. So if one project is delinquent or fifty projects are delinquent the PM will still just get a "1" for that given month
For the other field "PctMonthDelq" it is given the number of months a PM is delinquent (in my current case = 2) but eventually it will be six months. For this field it is the percent of time (months) a PM has how many months/total months has a particular PM been delinquent (for any project they are working on). When I have six months of data, of course the highest number of months would be six and 100% of PctMonthDelq.
I hope that I am not being too simple and repetitive but instead that this clarifies what I am trying to do (if it can be done) and let me know if you have any questions.
Again, MANY THANKS IN ADVANCE FOR ALL OF YOUR ASSISTANCE!
Brett
To get back to you on your SQL, this is the same type of result that I have been getting - just a sum of delinquent projects. Please go back to my original question.
The end-user does not care about the number of projects delinquent. I am wondering if there a way to know out of (for example) six months, how many months has this particular ProgMgr been delinquent. It skews the results in favor of those ProjMgrs with less projects since of course the higher the number of project one has the higher the probability of default on one of those projects.
So, for the MonthDelqStatus field I need a way to CALCULATE the SUM of the NUMBER OF MONTHS by using a toggle "0" and "1" or WHATEVER to determine a month when a PM is not delinquent => "MonthDelqStatus = 0" AND a month when a PM is delinquent "MonthDelqStatus = 1" regardless of how many projects the PM is working on. So if one project is delinquent or fifty projects are delinquent the PM will still just get a "1" for that given month
For the other field "PctMonthDelq" it is given the number of months a PM is delinquent (in my current case = 2) but eventually it will be six months. For this field it is the percent of time (months) a PM has how many months/total months has a particular PM been delinquent (for any project they are working on). When I have six months of data, of course the highest number of months would be six and 100% of PctMonthDelq.
I hope that I am not being too simple and repetitive but instead that this clarifies what I am trying to do (if it can be done) and let me know if you have any questions.
Again, MANY THANKS IN ADVANCE FOR ALL OF YOUR ASSISTANCE!
Brett
So the query I gave is not doing that? What results are you getting?
ASKER
The query I am using is counting the number of instances where a project has [Total Delinquent] > 0 only for the CURRENT month.
(Initial Calculated Field creating MonthDelqStatus on a temp table that gets appended to the main tbl_ProjMgr_HISTORY every month):
>> MonthDelqStatus: IIf(([Total Delinquent]>0),1,0)
Then when I open the main report with the two subreports:
>> Sum(tbl_ProjManager_HISTOR Y.MonthDel qStatus) AS MonthDelqStatus
So it is counting the number of projects from the current month where Total Delinquent > 0.
So, I am getting exactly what those queries are supposed to pull up, for each PM the number of projects delinquent for the current month. What I am trying to get (and this goes back way before you introduced me to subqueries/subreports) is the number of months a PM is delinquent regardless of the number of projects (s)he has.
I hope that clarifies?
Thanks again in advance.
(Initial Calculated Field creating MonthDelqStatus on a temp table that gets appended to the main tbl_ProjMgr_HISTORY every month):
>> MonthDelqStatus: IIf(([Total Delinquent]>0),1,0)
Then when I open the main report with the two subreports:
>> Sum(tbl_ProjManager_HISTOR
So it is counting the number of projects from the current month where Total Delinquent > 0.
So, I am getting exactly what those queries are supposed to pull up, for each PM the number of projects delinquent for the current month. What I am trying to get (and this goes back way before you introduced me to subqueries/subreports) is the number of months a PM is delinquent regardless of the number of projects (s)he has.
I hope that clarifies?
Thanks again in advance.
Sorry, I'm still a bit confused. Did you create the query I asked you to called qry_ProjMgrs_DelinqenciesP ast6Mo? If so, what results are you getting with this query?
ASKER
I used your query and my results from this query range from 0 - 25. I hope that helps?
ASKER
I am running your query at the same level as the Project Manager (the textbox - query is right next to the PM textbox field].
I think what I need to do is something in a query based on the ProjMgr because now the current query is running: IIf([MonthDelqStatus]>0, 1, 0) for each PROJECT and not at a higher grouping level (ProjectMgr).
I need it to run something along the lines of :
Sum(tbl_ProjManager_HISTOR Y.MonthDel qStatus) AS MonthDelqStatus if >0, then it equals one. But I need it for only the ProjectMgr and only showing a "1 or 0" for a given month and then to count the number of months delinquent (out of a total of six months).
I think what I need to do is something in a query based on the ProjMgr because now the current query is running: IIf([MonthDelqStatus]>0, 1, 0) for each PROJECT and not at a higher grouping level (ProjectMgr).
I need it to run something along the lines of :
Sum(tbl_ProjManager_HISTOR
Okay, I just want to be sure that you're using both the query and textbox I gave you in this post:
http://www.experts-exchang e.com/Micr osoft/Deve lopment/MS _Access/Q_ 27868147.h tml#a38407 571
Because the way you explain it, it almost seems like you're talking about something else. If you did indeed follow my last instructions, then I may need to see your latest revision along with your data (change ProjMgr names for privacy).
http://www.experts-exchang
Because the way you explain it, it almost seems like you're talking about something else. If you did indeed follow my last instructions, then I may need to see your latest revision along with your data (change ProjMgr names for privacy).
ASKER
I can send you what you need I just don't want to post it on this forum. You can send me your email at brett.thomason@powereng.co m. Let me know what will help you and I will send that out to you. Thanks in advance. MANY thanks for hanging in there with me. I really do appreciate it.
My email is in my profile.
p.s Hopefully you don't get any spam from spambots for posting your email address.
:-)
p.s Hopefully you don't get any spam from spambots for posting your email address.
:-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IrogSintra,
I only have one word. WOW. That is EXCACTLY what I have been working towards all of this time. It still baffles me how you can take a strangers work, understand it (and more importantly just exactly what the requestor is trying to do, jump into someone else's "code"/work (which I think has to be one of the more if not most difficult things to do, and resolve the issue. Absoultely amazing.
You are truely amazing and again I really do appreciate ALL of the work that you have put into helping me with this report. I REALLY appreciate it.
Thank you, thank you, thank you.
Regards,
Brett Thomason
I only have one word. WOW. That is EXCACTLY what I have been working towards all of this time. It still baffles me how you can take a strangers work, understand it (and more importantly just exactly what the requestor is trying to do, jump into someone else's "code"/work (which I think has to be one of the more if not most difficult things to do, and resolve the issue. Absoultely amazing.
You are truely amazing and again I really do appreciate ALL of the work that you have put into helping me with this report. I REALLY appreciate it.
Thank you, thank you, thank you.
Regards,
Brett Thomason
ASKER
I just sent you an email (now) for the world to see which states my sincere appreciation. I was stuck in so many places in this report (in my thinking) and you were able to jusmp ain and basically rescue me over the course of a week. What dedication, what a gift to be able to jump into to someone else's logic and understand what they are trying to accomplish and then have all of the skills at hand to be able to resolve the issue. Words cannot express my gratitude. So instead of capitalizing thanks about a hundred times I will just send you a sincere thank you for your assistance and your persistance. What a gift and thnks again.
I'm not sure what email you're referring to unless you're referring to your last post. Anyways, thanks for the compliments, and you're quite welcome.
Can you post the sql for your report?