Dealing with progressive dates 1

[puforee]

I have a DB that presents monthly data based on the last 13 months. I need queries to separate the data into months based on Month(Now()). I will show you what I need with one example but I will need it for 13 queries. I cannot use a totals query to group all the information by month...trust me my output must be different.

So, Example: Month(Now()) = 6 and Year(Now()) = 2016. I need to return data for:
June 2016 back to through June 2015.

I have tried subtracting months but that does now work, like for December 2015. Month = 6 Year = 2016 and if I do Month(Now())-7 I get nothing. I know there is data for Dec 2015.

Remember I will have to do this 12 times. The 13th is the current month...easy.

I would like to do this in queries but if VB would be better I could use it...but how is still the question.

Final result of all the queries would be data for:
June 2016
May 2016
April 2016
March 2016
Feb 2016
Jan 2016
Dec 2015
Nov 2015
Oct 2015
Sep 2015
Aug 2015
Jul 2015
June 2015

And when I run the queries in July I would get July 2016 back to July 2015.

Any ideas that might help me out.

Thank you in advance,

 

[SkipVought]

Hi,

if I do Month(Now())-7 I get nothing

Please post the SQL code that got "nothing."

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MajP]

Public Function EndDate(Offset As Integer) As Date
  EndDate = DateSerial(Year(Now), Month(Now) + 1, -1)
  EndDate = DateAdd("M", Offset, EndDate)
End Function
Public Function StartDate(Offset As Integer) As Date
  StartDate = DateSerial(Year(Now), Month(Now), 1)
  StartDate = DateAdd("M", Offset, StartDate)
End Function

...Between StartDate(-1) and EndDate(-1)

for each query change the offset from 0 to -12

 

[MajP]

You cannot do month(now)-7, because what happens in July, June, May.... You cannot have a negative month. Dateadd is pretty powerful in that is rolls the date into the next year.

 

[MajP]

if it is not clear this creates the between sql string like
between 3/1/2016 and 3/31/2016

 

[puforee]

MajP. Thanks. I will work on this over the holidays and let you know how it turns out.
Very Grateful.

 

[puforee]

MajP. I have a question about your code. Do I store this code as a public function and then call it from my query? If I do, how do I pass the offset value to the function from the different queries. Could you show me an example how the call would look in the query design view please?

Thanks again.

 

[MajP]

1. Put the code in a standard code module not a form's module
2. Use the code in queries something like
This month

select * ... from [SomeTable] where [SomeDateField] between StartDate(0) and EndDate(0)

next query for previous month

select * ... from [SomeTable] where [SomeDateField] between StartDate(-1) and EndDate(-1)

.
.
.
next query for 12 months ago

select ... from [SomeTable] where [SomeDateField] between StartDate(-12) and EndDate(-12)

3. You can pass in any offset so for next month

select ... from [SomeTable] where [SomeDateField] between StartDate(1) and EndDate(1)

 

[puforee]

MajP. I have inserted the Function and the calls in my first query. I have varied the () from 0 to -12 and it works just fine. I go back to work on Tues. Jan 3. I will fully integrate the code then.

Thanks very much. You deserve the star.

 

[puforee]

MajP. As I progress with my DB it is providing these outputs thanks to your code:
13 queries returning data for -1 to -13 months. Current month is not complete when the data is reviewed so I excluded the o offset. This is great. I drive 13 charts with this..one for each month. Some are blank because no data was collected during a specific month. This is OK.

I also provide total data for the same time frame to be used in a single stacked chart showing all 13 months across the bottom of the chart. I have on small issue with this.
Let's say one of the 13 months returns Zero data. My stacked chart now only shows 12 months (or less depending of the lack of data). I can accept this but it would be better showing all months and no data for the months that don't return data.

Would there be a way to cause my 13 month query to return at least the month even though no data may be collected.

I can supply query information if you want me to. I have 3 queries that gather all the data and place it in one table. That table is read by my Excel Chart. Here is the final query.

 & Chr(10) & "(" & [SumOfsumOfCount] & ")" AS [Monthly Count], [Final Close Out Non Reg Metrics Build].AvgOfMT_Days AS MT_Days, [Final Close Out Non Reg Metrics Build].AvgOfAMTDays AS [MT_Alloted Days], [Final Close Out Non Reg Metrics Build].AvgOfRecords_Days AS Records_Days, [Final Close Out Non Reg Metrics Build].AvgOfARecDays AS Records_Alloted_Days, [Final Close Out Non Reg Metrics Build].AvgOfShip_Days AS Ship_Days, [Final Close Out Non Reg Metrics Build].AvgOfAShipDays AS Ship_Alloted_Days INTO [Close_Out_Metrics Non Reg]]

Hopefully you can help once again.

 

[MajP]

To ensure you get data you make be able to do some kind of outer join.
Make a table tblMonths. Add two fields MonthName and MonthValue
1 January
2 February
...
12 December
Include a left outer join of tblMonths to your table or query where tblMonths.monthValue = months(YourDateField)

 

[puforee]

OK MajP. I have created the tblMonths as you suggested. Showing MonthValue as a number from 1 to 12 and MonthValues as short text (Jan, Feb, et.) I hope this is correct.

I am sure you will probably shake your head at the complexity of my code below...if I was better I could probably do it a lot easier..but it is what it is for now .

I am having a little difficulty. So, here are my sequences of 3 queries to come out with the final results for my chart.:

1st:

 AS [Count], MT_Time_Regulatory.[Related Entity Name], MT_Time_Regulatory.[Related Entity ID], MT_Time_Regulatory.[Related Entity Date], MT_Time_Regulatory.MT_StartDt, MT_Time_Regulatory.MT_EndDate, MT_Time_Regulatory.MT_CompStatus, MT_Time_Regulatory.MT_CompleteDt, RegSTD_Time_Standards.STDStartDate, RegSTD_Time_Standards.STDDueDate, RegSTD_Time_Standards.STDCompletionStatus, RegSTD_Time_Standards.STDCompletionDate, MT_Time_Regulatory.[Course End Dt], RegSTD_Time_RecordsReceived.RecRecStartDate, RegSTD_Time_RecordsReceived.RecRecDueDate, RegSTD_Time_RecordsReceived.RecRecCompletionStatus, RegSTD_Time_RecordsReceived.RecRecCompletionDate, RegSTD_Time_Records_Complete.RecComStartDate, RegSTD_Time_Records_Complete.RecComDueDate, RegSTD_Time_Records_Complete.RecComCompletionStatus, RegSTD_Time_Records_Complete.RecComCompleteDate, RegSTD_Time_Shipping.ShipStartDate, RegSTD_Time_Shipping.ShipDueDate, RegSTD_Time_Shipping.ShipCompletionStatus, RegSTD_Time_Shipping.ShipCompletionDate, IIf(IsNull([MT_CompleteDt]),Null,IIf(Val(WorkingDays2([Course End Dt],[MT_CompleteDT]))<1,1,Val(WorkingDays2([Course End Dt],[MT_CompleteDT])))) AS MT_Days, IIf(IsNull([STDCompletionDate]),Null,IIf(Val(WorkingDays2([MT_CompleteDT],[STDCompletionDate]))<1,1,Val(WorkingDays2([MT_CompleteDT],[STDCompletionDate])))) AS STD_Days, IIf(IsNull([STDCompletionDate]),Null,IIf(IsNull([RecComCompleteDate]),Null,IIf(Val(WorkingDays2([STDCompletionDate],[RecComCompleteDate]))<1,1,Val(WorkingDays2([STDCompletionDate],[RecComCompleteDate]))))) AS Records_Days, IIf(IsNull([RecComCompleteDate]),Null,(IIf(IsNull([ShipCompletionDate]),Null,IIf(Val(WorkingDays2([RecComCompleteDate],[ShipCompletionDate]))<1,1,Val(WorkingDays2([RecComCompleteDate],[ShipCompletionDate])))))) AS Ship_Days, [ShipCompletionDate]-[MT_CompleteDt] AS [TotalTimeStd-Rec-Ship], [ShipCompletionDate]-[MT_Time_Regulatory]![Course End Dt] AS TotalTimeSinceClassComplete, Year([MT_Time_Regulatory]![Course End Dt]) AS Expr1, Month([MT_Time_Regulatory]![Course End Dt]) AS Expr2, MonthName([Expr2],3) & " - " & Year([Course End Dt]) AS [Month], WorkingDays2([Course End Dt],[MT_EndDate]) AS AMTDays, WorkingDays2([STDStartDate],[STDDueDate]) AS ASTDDays, WorkingDays2([RecRecStartDate],[RecComDueDate]) AS ARecDays, WorkingDays2([RecRecStartDate],[RecRecDueDate]) AS ARecRecDays, WorkingDays2([RecComStartDate],[RecComDueDate]) AS ARecToShipDays, WorkingDays2([ShipStartDate],[ShipDueDate]) AS AShipDays]

As you can see I use the Month Ofset function developed in this converstion to select months -1 to -13 using your code from above.

2nd is run on the results of the 1st.

.Month, Avg([Combined Totals qry].MT_Days) AS AvgOfMT_Days, Avg([Combined Totals qry].AMTDays) AS AvgOfAMTDays, Avg([Combined Totals qry].STD_Days) AS AvgOfSTD_Days, Avg([Combined Totals qry].ASTDDays) AS AvgOfASTDDays, Avg([Combined Totals qry].Records_Days) AS AvgOfRecords_Days, Avg([Combined Totals qry].ARecDays) AS AvgOfARecDays, Avg([Combined Totals qry].Ship_Days) AS AvgOfShip_Days, Avg([Combined Totals qry].AShipDays) AS AvgOfAShipDays, Sum([Combined Totals qry].SumOfCount) AS SumOfSumOfCount]

3rd, and final is a table build query using the results of the 2nd query.

 & Chr(10) & "(" & [SumOfsumOfCount] & ")" AS [Monthly Count], [Final Close Out Metrics Build].AvgOfMT_Days AS MT_Days, [Final Close Out Metrics Build].AvgOfAMTDays AS [MT_Alloted Days], [Final Close Out Metrics Build].AvgOfSTD_Days AS STD_Days, [Final Close Out Metrics Build].AvgOfASTDDays AS STD_Alloted_Days, [Final Close Out Metrics Build].AvgOfRecords_Days AS Records_Days, [Final Close Out Metrics Build].AvgOfARecDays AS Records_Alloted_Days, [Final Close Out Metrics Build].AvgOfShip_Days AS Ship_Days, [Final Close Out Metrics Build].AvgOfAShipDays AS Ship_Alloted_Days INTO Close_Out_Metrics]

Based on these items. Which query should I attach the left outer join to tblMonthValue. My key field is the "Course End Dt" field in 1st above. It is the field that has the Month offset function attached. This field does not show in 2nd or 3rd code above. Also you talked about a Left Outer join. Using the query builder I can create Inner and Outer joins but where does the "left" come into play?

Hopefully you will be able to decipher what I have done. Thanks ahead of time.

 

[puforee]

MajP. OK, I played and got it to partially work. Here is the query (I remove many of the fields for simplicity)"

.Count, [Test All Months Combined qry].[Course End Dt], [Test All Months Combined qry].Mon, [Test All Months Combined qry].Yr]

This works but...It returns a list that is correct and shows the missing month...in this case Jan. But the month is not in the correct spot and does not include the correct year. Some how we have to compute the missing month and year. It is possible, in July 2017 as an example, that we will be drawing data from June 2016 through June 2017. A month or more could return no data but we will want the month/s to show anyway...with their corresponding year.
So, here is what I got shortened for ease of display and you can see the Jan with no year is in the incorrect spot. I have noted where it should be.
Month Year
Jan
Dec 2015
I need the Jan from above to fit in this space.
Feb 2016
Mar 2016
.
.
..
.
.
Dec 2016

Do I have to modify the tblMonth to show sequential months and years for 5 or 6 years starting with 2016,as a start. Or is there some way to calculate this so I don't have to keep extending the tblMonth data.

Thanks for sticking with me on this matter.

 

[MajP]

I would modify my months table for this to work with multiple years at once.
I would have a single date field instead.

tblMonths
dtmMonth (date field)

Do the same and populate all the months and years out as far as you think you will ever go. If it was me I would write cod to populate the table so I do not need to type in the values
1/1/2016
2/1/2016
3/1/2016
....
12/1/2020

Same idea but I want to left outer join where
Month(dtmMonth) = months(YourDateField) AND Year(dtmMonth) = Year(YourDateField)

If you do not want to type in all of those dates. You can go to excel and type a couple of values. Then drag the column and it will create the series. Then import the data into Access. Like

1/1/2014
2/1/2014
3/1/2014
4/1/2014
5/1/2014
6/1/2014
7/1/2014
8/1/2014
9/1/2014
10/1/2014
11/1/2014
12/1/2014
1/1/2015
2/1/2015
3/1/2015
4/1/2015
5/1/2015
6/1/2015
7/1/2015
8/1/2015
9/1/2015
10/1/2015
11/1/2015
12/1/2015
1/1/2016
2/1/2016
3/1/2016
4/1/2016
5/1/2016
6/1/2016
7/1/2016
8/1/2016
9/1/2016
10/1/2016
11/1/2016
12/1/2016
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
6/1/2017
7/1/2017
8/1/2017
9/1/2017
10/1/2017
11/1/2017
12/1/2017
1/1/2018
2/1/2018
3/1/2018
4/1/2018
5/1/2018
6/1/2018
7/1/2018
8/1/2018
9/1/2018
10/1/2018
11/1/2018
12/1/2018
1/1/2019
2/1/2019
3/1/2019
4/1/2019
5/1/2019
6/1/2019
7/1/2019
8/1/2019
9/1/2019
10/1/2019

 

[puforee]

I am now getting scared. I thought of this last night and implemented it today. All is good. Then I looked at your post...wow..we were thinking the same way. Maybe I an learning a little bit. I did have to do some query changing for the first query in yesterdays post. Then I found some bad data. But everything worked out. Now I have to do it one more time. There is another set of queries that does the same type thing for a different set of data. It should be a breeze now.

I want to thank you again for sticking with me. Much appreciated.

 

[MajP]

No problem. Good luck.