Late Requirements in Report (1 Viewer)

[Valentine]

I am trying to make a report that doesn't use 5 different subreports to calculate how late a requirement is. I want to display on time, within 30 days, between 30-90, between 90 and 1 year, and over a year. I know i can make a report with a subreport for each time frame with a different query each but I was wondering if there is some way to do this without all that hassle. I do not need to store the information it can dissapear as soon as the numbers are on the report.

 

[Gasman]

Create a query that also calculates which band it should be in, and group on that band?

 

[Valentine]

hmmm I don't think I'm explaining this correctly, I am still in the brainstorming stage to figure out how to accomplish this task. I have a requirement that goes through 10 stages. I am trying to make a report that shows if the requirement is late to pass each stage.

 

[Valentine]

I can make a table with each stage as a number and run a query to get all my requirments and the dates on them to get if they are late or not then do a dcount total into the table but I dont want to have to have 5 different subreports for each phase of lateness the requirement is.

 

[jdraw]

Show us a mock up of what you want.
Do you understand Gasman's advice?

A description of how stage fits in to the overall requirement would be helpful for readers.

 

[plog]

In general you can move the WHERE to the SELECT with an IIF. Here's a simplified example:

SELECT CustomerID FROM YourTable WHERE [YourDate]>=(Date() +30)

You can move that to the IIF like so:

SELECT CustomerID, Iif([YourDate]>=(Date() +30), True, False) AS Late30Days FROM YourTable

So now, all records in YourTable pass through--even the ones not 30 days, they just show Fals for Late30Days field. From there you can add more calculated fields in that same query (e.g. [Late60Days], [Late90Days], etc.) and have all your records in that one query.

 

[Valentine]

Stage	On time	0-30 days	31-90 days	91-365 days	over a year
1	number of requirements at this time in this stage
2
3
4
5
6
7
8
9

So I want this to be shown, I have over 1000 requirements going through various stages of completion. I have a date field that the requirement is needed by so that is my lateness factor. I am basically making a slide for a meeting. This slide i am making is an update to one already being made yet the date information is not apart of the form's record set so I cannot use that query. I just tried doing a subform on the main form and it showed a table with my stages algned horizontally instead of vertically so taht i can portray the lateness next to them.

 

[jdraw]

I want to display on time, within 30 days, between 30-90, between 90 and 1 year, and over a year.

?? Perhaps just me but I'm not seeing a RequirementNeedByDate and comparing that to what Date?
Can you give us a few examples of Requirements, how they fit into Stages and what Date(s) are being compared to determine the category/slot?
Are you expecting Counts or ProjectIds or ....

 

[Valentine]

just counts in each.
a requirement gets created with a need by date
this requirement goes through approval
then on to 4 stages of funding
4 stages of development
if the need by date has passed at any time through this process it is considered late

 

[Valentine]

I think i figured out a way to make this work but not sure if its possible....
I have a SQL for the query, can i make 5 different variables that equal the timeframes I am looking for and run the SQL + variable1, record data run SQL + variable2 record data, so on and so on inside the same command button?

 

[jdraw]

Agree. This is from a mock up

SELECT Req.ReqID, Req.stage, Req.ReqDate, [ReqDate] -Date() AS Det
,count(iif (Det>0,1,null)) as OnTime
,count(iif (Det < 0 and det>=-30,1,null)) as Within30
,count(iif (Det <-31 and det>=-60,1,null)) as Late30to60
,count(iif (Det <-60 and det>=-90,1,null)) as Late60to90
,count(iif (Det <-90 and det>=-365,1,null)) as Late90to1year
,count(iif (Det <-365,1,null)) as MoreThanAYear
FROM Req
GROUP BY REQid,STAGE,REQDATE

Query59 Query59

ReqID	stage	ReqDate	Det	OnTime	Within30	Late30to60	Late60to90	Late90to1year	MoreThanAYear
1​	1​	11-Jan-22​	5​	1​	0​	0​	0​	0​	0​
1​	2​	12-Jan-22​	6​	1​	0​	0​	0​	0​	0​
1​	3​	14-Jan-22​	8​	1​	0​	0​	0​	0​	0​
2​	3​	05-Dec-21​	-32​	0​	0​	1​	0​	0​	0​
2​	4​	31-Dec-21​	-6​	0​	1​	0​	0​	0​	0​
2​	5​	05-Jan-22​	-1​	0​	1​	0​	0​	0​	0​
2​	8​	07-Jan-20​	-730​	0​	0​	0​	0​	0​	1​
2​	9​	15-Jan-22​	9​	1​	0​	0​	0​	0​	0​
2​	10​	30-Jan-22​	24​	1​	0​	0​	0​	0​	0​
3​	3​	10-Oct-21​	-88​	0​	0​	0​	1​	0​	0​
3​	4​	15-Nov-22​	313​	1​	0​	0​	0​	0​	0​
3​	5​	28-Dec-21​	-9​	0​	1​	0​	0​	0​	0​
3​	6​	08-Jan-22​	2​	1​	0​	0​	0​	0​	0​
3​	7​	18-Jan-22​	12​	1​	0​	0​	0​	0​	0​

 

[Pat Hartman]

Gasman gave you the answer. Please try to work with that. Create a querydef that includes the ID of the record and calculates the age. Save it. Then create a new query that selects the original one and use a switch or IIF to categorize the calculated age. In the report, add a group for the category field and that will group all the records together by their aging category.