Link to home
Start Free TrialLog in
Avatar of bkthom
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
Avatar of Jared_S
Jared_S

Have you checked your grouping?
Can you post the sql for your report?
Avatar of IrogSinta
@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_DelinqenciesPast6Mo or whatever you like with this SQL code:
 
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]

Open in new window

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] & "'")

Open in new window

Please do - thanks for the note!
Avatar of bkthom

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
Avatar of bkthom

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
So the query I gave is not doing that? What results are you getting?
Avatar of bkthom

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_HISTORY.MonthDelqStatus) 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.
Sorry, I'm still a bit confused. Did you create the query I asked you to called qry_ProjMgrs_DelinqenciesPast6Mo? If so, what results are you getting with this query?
Avatar of bkthom

ASKER

I used your query and my results from this query range from 0 - 25.  I hope that helps?
Avatar of bkthom

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_HISTORY.MonthDelqStatus) 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).
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-exchange.com/Microsoft/Development/MS_Access/Q_27868147.html#a38407571

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).
Avatar of bkthom

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.com.  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.
:-)
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bkthom

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
Avatar of bkthom

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.