Adding another date code amount

[Thompyt]

I am attempting to add in the amount of deferred CRs to a certain board Date (CMB-01, CMB-02.....) The count I already have:





Was done with the following code:

SELECT [CMB Dates].CMB, Count([Change Request].CR_NO) AS CR_Count
FROM [CMB Dates], [Change Request]
WHERE ((([Change Request].CR_No)<>0) AND (([Change Request].Sub_No)=0) AND (([CMB Dates].Start_Date)<=([Date_Input])) AND (([CMB Dates].Stop_Date)>=([Date_Input]-1)))
GROUP BY [CMB Dates].CMB;

I derived the deferred data with this code:

SELECT [Change Request].CR_No, [Change Request].Date_ID AS Identified, [Change Request].Date_Closed AS Closed, IIf(DateDiff("ww",[Date_ID],[Date_Closed])>1,(DateDiff("ww",[Date_ID],[Date_Closed]))-1,0) AS [Times Deferred]
FROM [Change Request]
WHERE ((([Change Request].Sub_No)=0))
GROUP BY [Change Request].CR_No, [Change Request].Date_ID, [Change Request].Date_Closed
HAVING ((([Change Request].CR_No)<>0) AND ((IIf(DateDiff("ww",[Date_ID],[Date_Closed])>1,(DateDiff("ww",[Date_ID],[Date_Closed]))-1,0))<>0))
ORDER BY [Change Request].CR_No;

Achieving the data in the times deferred.



What I wanted to do was to sum the [times deferred] grouped into CMB Dates Table Start_Date and Stop_Date range and not by individual CR.

CMB Dates Table

[June7]

If you don't want to group by CR then remove that field from the query.

[Thompyt]

Thanks June7, Thats easy. What I wanted to do was to add up all the deferred inside the CMB Date range. between [CMB Dates].Start_Date] and [CMB Dates].[Stop_Date]

Some of the CRs would fall into several CMB ranges. If Times deferred = 4 then it most likely fell into 5 CMBs (or 5 weeks back from the date it was closed) CR43 would be in 3 CMBs

This one is a pretty convoluted code I think.

[June7]

Sorry, don't understand that.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Thompyt]

June7 See attached.
15.2 Combined_Backup.zip

[June7]

I don't understand this data. What is it for? What is a 'board' date? What is 'deferred'? What is the rule for calculating 'deferred'? What is a CR_No and why is it duplicated in Change Request? Same for Sub_No. How are these values created? What is Date_ID for?

Why does the db want to run an update action when opening?

[Thompyt]

This is for a configuration management board.

Deferred = a Change request that has not been deleyed for additional information. It is not approved to close out or to move forward in the process.
Deferred calculation is where a CR takes more than 1 week. Thus if it takes 3 weeks it is in deferred status until the final week where it is either approved, withdrawn or denied. When it is closed and it was 4 weeks later than it was deferred for 3 weeks.

I was using this to calculate: Times Deferred: IIf(DateDiff("ww",[Date_ID],[Date_Closed])>1,(DateDiff("ww",[Date_ID],[Date_Closed]))-1,0)

CR_No is the main number for a Change Request
Sub_No is a sub number under CR_No. This is where a change request is submitted with several underlying requirements in the main one. The values are created by manual input by the user. (I didn't see any easier way to do this since it changes the means of tracking numbers upon th eusers whim.)

It updates the CR_No and Sub_No that equal 0 to keep the ccount correct. All it is doing is changing that Change request date_closed to todays date, and the other by updating the Closed_Date where the vote is Like "Defer*) helping on the deferred count.

Date_ID is the date where the requirement for the Change Request is identified.