Rolling 3 month and 12 month Calculation

[REGeekker]

I need an Access2010 VBA code (64-bit) that will calculate the number of Reworks for a rolling 3month period. I use a DatePicker Form toselect the 3 month reporting period. I tried to do this all with queries butVBA coding maybe the best way to go.

Formula: Rwk = (count of reworks performedon a Program (Pgm)) / (total number of active programs) for a 3 month period.



In my table below, the [RwkCount] (the numerator of the formula) is thetotal count of rework issues found on a Program, e.g., Pgm A there were 3issues documented during the reporting period (Jan, Feb, Mar). There can be more rework counts than programs.I use field [RwkDate], which is not shown, to count the number of Reworks Datesand then summarized the count as [RwkCount].

PgmID	PgmName	PgmGrp	Active	Inactive	RwkCount
1	A	1	1/22/2015		3
2	B	2	2/2/2015		5
3	C	3	2/3/2015	4/30/2015	14
4	D	3	2/4/2015		9
5	E	1	2/4/2015		2
6	F	2	3/5/2015	4/5/2015	4
7	G	2	3/6/2015		11
8	H	3	3/7/2015		2
9	I	1	4/8/2015	4/30/2015	0
10	J	2	4/9/2015		6
11	K	1	4/11/2015		10
12	L	2	4/12/2015		3

For the denominatorpart of the formula and for this example, the total numbers of active programs is6. The total rework count is 32 for the 6 active programs.

Calculations Needed:
1. Rework for a 3 month period (e.g., Jan,Feb,Mar )= (32/6) = 5.33 reworks for 3 months.
2. Rework for a 12 month period (active+inactive) =69/12 = 5.75 reworks for a 12 month period

My rolling reportingperiods are Jan, Feb, Mar; then Feb, Mar, Apr; then Mar, Apr, May, etc.

Therefore: Ineed a code that will calculate the formula above for a rolling 3 month and 12month reporting period.

[ssanfu]

Would need to know the table name - which table is the data coming form?

Rework for a 3 month period (e.g., Jan,Feb,Mar )= (32/6) = 5.33 reworks for 3 months.

Where does the 6 come from?

[REGeekker]

The table name is [ProgramStatus].

The 6 is the total number of active programs in a rolling 3 month period: Jan, Feb, Mar.

[REGeekker]

I should mention the second table used is called [ReworkData], which is where the [RwkDate] field is located. As I noted above, the number of date entries for a program is counted.

[ssanfu]

Where does the 6 come from?

I should have asked "How do you calculate the total number of active programs in a rolling 3 month period"?
Tell me the steps you go thru.


I don't know your business rules,
I don't know your table structure,
I don't have your dB.
So the attached dB is to demonstrate the code involved.
NOTE: this is only one way to write the code.

I used one UDF - by changing the date and the number of months, you can return the value for 3 months of 12 months.
By having a text control on a form for the start date and a text box for the number of months (or a combo box/list box/option group), the function will return a value.

Trace thru the code from the button click....

[Thompyt]

Geeker,
Where you have a date for the tracker you could put in a between statement in your date field criteria in a query.

Yearly Between DateSerial(Year(Date()),Month(Date())-12,1) And Now()
Tri-monthly Between DateSerial(Year(Date()),Month(Date())-3,1) And Now()

Or simply something like Between Now()-365 and Now() / Between Now()-90 and Now() that would give you a rolling daily update though

[REGeekker]

ssanfu,

Thank you so much for the code. In order to see if it will work on my actual data, can I change the code everywhere it's says Rolling (which is pointing to the Rolling table) to the name of my query table in my database? My query table is qryPgmStatus.

[ssanfu]

can I change the code everywhere it's says Rolling

Should work. Have to watch the field names also - make sure they are correct.

[REGeekker]

ssanfu,

I could rename my query from qryPgmStatus to Rolling or qryRolling if this would be easier. Would the code be impacted?

[ssanfu]

I could rename my query from qryPgmStatus to Rolling or qryRolling if this would be easier.

One name is as good as the other. As far as whether the code would be impacted, I don't know because I don't know what fields qryPgmStatus has.
I used what you provided.

Would the code be impacted?

I'm sure there will need to be changes in the code because I hard coded "the number of active programs in a rolling 3 month period". I had no idea where that number came from.

Try it. Step through the code. What happens or doesn't happen?


You asked:

Therefore: I need a code that will calculate the formula above for a rolling 3 month and 12month reporting period.

The code I provided is an example of how to do the calculations in VBA. Without more details, I don't know how to provide more/better help.

[REGeekker]

ssanfu,

See PgmID numbers 1 through 8 in table above. Programs A-H are Active between 1/1/2015 - 3/31/2015 so the total number of Active programs should have been 8 not 6. And, Program B through L are active between 2/1/2015-4/30/2015 so a total of 11 Programs active during the 3 month reporting period. I will scrubb my database an provide it to you.