Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Creating table on the fly that creates a field for each month based a date range

I am looking for the best approach to create a process where each field is a specific month within a floating date range.  As the range changes a new table is generated to display the months within that new range.

ie.  2/1/11 - 2/1/2016 - I would have a field that would be for each month/yyyyy that falls within that range.  Since my data will only display the next 60 months from the current month/yyyyy I need for these fields to be renamed accordingly.  Without creating a table that has this field name hard coded and has to be changed monthly.
Avatar of dave4dl

I recommend naming all your fields with generic names like "Offset1Month", "Offset2Months",...
Avatar of Karen Schaefer


that would mean I would have to have a table containing more that 60 fields - 1 for each month during the 60 month periods that will progress thru the years.

There has to be an easier way via code.

Hmm, from your post I thought that is what you wanted to do.

Please restate your question and be very clear about what you are trying to do.  When you say something like "I am looking for the best approach to create a process where each field is a specific month within a floating date range", it is pretty meaningless to those of us trying to help you.  First off, you are talking about "each field" but never explain what these fields are in or what they are for.  Secondly, "field" is a very generic term which could mean columns in a table or textboxes on a form or web page.
Are you summing for each month in a date range that differs regularly.  If so, I think you best bet is a cross tab query:

TRANSFORM Sum(fldValue)
SELECT fldName, Sum(fldValue) SumForName FROM tblData
WHERE fldDate BETWEEN sDate and eDate
GROUP BY fldName
PIVOT Format(fldDate,"yyyy-mm");

This will give you a recordset with a name, a sum for that name, and a sum for each month in the range provided by sDate and eDate.
Ok, here goes.  I need to indicate active employees, then forecast 60 month out if they are still active.  then display each 1 in each field(month) that they are active.  So I can display by color coding a spreadsheet that looks like a sideway bar chart without using a actual bar chart or possible something that would resemble a MS Project like display, but again in a spreadsheet.  So My thought was to place a 1 for each month and then conditional format the cells where cell/field = 1 , format as Red.  this way if the Employee has a break in their employment the event will be clearly indicated.

Hope this clarifies things.
Sounds like using Excel would be your best bet.

Record all your data to excel (or pull it from a database if that is where your data is stored) and use conditional formatting to color each cell the way you want it to be colored
here is a tutorial on conditional formatting:
Conditional formatting only applies to forms and reports.  With a constantly changing set of fields, you are going to have to 'modify' the report for each new set of 60 months.  You may want to use something like X to indicate present and nothing to indicate away.  A yymm field name is easy to generate in a cross tab.  The snippet below shows how that would look for one year.  Because this field name form takes 5 characters per field, with the preceding name you will need something like 320 characters across the page.  Legal, with a size 8 Arial font might be able to do the trick.  Remember, the Access report generator can only use paper up to 22 inches wide.

1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112
  X    X    X              X    X    X              X    X

Open in new window

Here is what I have so far and I am not getting any records returned - What am I missing?

TRANSFORM Sum(tblEmp_ForecastStatusing.EmpFC_RecID) AS SumOfEmpFC_RecID
SELECT tblEmp_ForecastStatusing.BEMS, tblEmp_ForecastStatusing.ForeCastMgr, tblEmp_ForecastStatusing.SubOrgNo, Sum(tblEmp_ForecastStatusing.EmpFC_RecID) AS [Total Of EmpFC_RecID]
FROM tblEmp_ForecastStatusing
WHERE (((Format([ForecastEndDate],"Short Date")) Between Date() And DateAdd("m",60,Date())))
GROUP BY tblEmp_ForecastStatusing.BEMS, tblEmp_ForecastStatusing.ForeCastMgr, tblEmp_ForecastStatusing.SubOrgNo
PIVOT Format([ForecastEndDate],"Short Date");

Open in new window

I also need to change from Sum or count of the RecID to if the employee's Forecast Enddate is  Between Date() And DateAdd("m",60,Date()))) then return either "X" or a 1 for each month. so what do I need to do to the query or should I create a function to check for this?


I tried your suggestion, however, it does not create a column heading for each month between current date and 60 months into the furture.  

TRANSFORM Sum(fldValue)
SELECT fldName, Sum(fldValue) SumForName FROM tblData
WHERE fldDate BETWEEN sDate and eDate
GROUP BY fldName
PIVOT Format(fldDate,"yyyy-mm");

TRANSFORM Sum(tblEmp_ForecastStatusing.ForecastEndDate) AS SumOfForecastEndDate
SELECT tblEmp_ForecastStatusing.BEMS, Sum(tblEmp_ForecastStatusing.ForecastEndDate) AS FDate
FROM tblEmp_ForecastStatusing
WHERE (((tblEmp_ForecastStatusing.ForecastEndDate) Between Date() And DateAdd("m",60,Date())))
GROUP BY tblEmp_ForecastStatusing.BEMS
PIVOT Format(ForecastEndDate,"yyyy-mm");

Open in new window

My current results -

Problem 1 the Dates do not fall in correct order and the data in the Date fields returns a Serial Date, I would before to return a 1 or X instead,  What do I need to do to my query to get it to display correctly?

K Book1.xls
Also my current qry only returns months for each date where data exists and not a column for each month for the next 60 months.  

What is the proper syntax to see if the ForecastEndDate is

iif(ForecastEndDate,Between Date() And DateAdd("m",60,Date())=True,

Format(ForecastEndDate,"MMM YYYY")

For the column Headings of the Months over the next 60 days.

I had to go out for a few hours.  

TRANSFORM IIf(Count(tblEmp_ForecastStatusing.ForecastEndDate)>0,"X"," ")
SELECT tblEmp_ForecastStatusing.BEMS
FROM tblEmp_ForecastStatusing
WHERE (((tblEmp_ForecastStatusing.ForecastEndDate) Between Date() And DateAdd("m",60,Date())))
GROUP BY tblEmp_ForecastStatusing.BEMS
PIVOT Format(ForecastEndDate,"yymm") IN (1102, 1103, 1104,...1601;

This should give you a recordset with Fields BEMS 1102 1103 1104 ...1601

and an X in the appropriate yymm field.  Note the IN clause in the PIVOT is required as you have mandated a yymm field even though there may be no data for that yymm.  If you are relatively certain there is at least one BEMS for each of the yymm fields, then the IN() clause should not be needed.  I suggest you try it first without the IN() clause.
is there a function that might be able to pass the months for the next 60 months for the IN statement, instead of typing 1 - 60 into the IN statement?

Still mulling that one.  It's not a matter of typing 1-60 in the IN() statement - its typing 1102, 1103, ... 1601, and higher every time you go to the next level.
What about a function that would create a list of the months/years for each month within the date range - do you know of any?

Avatar of GRayL
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We must have come up with the same solution at the same time - I found this sample elsewhere on the web I also came across a function that might another option - however, I have not had a chance to test it out.

Found it on Access Forum.  See next comment for funciton

Thanks for you input and assistances.

Function BuildCrossTab(strCrosstabName, intNumMonthsToUse As Integer, intStartMonth As Integer, intStartYear As Integer)
    Dim strSQL As String
    Dim strForMonths As String
    Dim i As Integer
    Dim qdf As DAO.QueryDef
    Dim dte As Date
    ' sets the querydef to the existing crosstab query
    Set qdf = CurrentDb.QueryDefs(strCrosstabName)
    ' makes sure i is at 0
    i = 0
    ' sets the intial date
    dte = DateSerial(intStartYear, intStartMonth, 1)
    ' iterates through the number of months you have passed to the function
    Do Until i = intNumMonthsToUse
        strForMonths = strForMonths & Chr(39) & Format(dte, "mmm yyyy") & Chr(39) & ","
        i = i + 1
        ' changes the date to the next month
        dte = DateAdd("m", 1, dte)
   ' removes the last comma
    strForMonths = Left(strForMonths, Len(strForMonths) - 1)
    ' adds the necessary surrounding bits
    strForMonths = "In (" & strForMonths & ")"
   ' You put your query here like this and modify it as shown  to include the month string
    strSQL = "TRANSFORM Count(Orders.OrderID) AS CountOfOrderID " & _
             "SELECT Orders.ShipName, Count(Orders.OrderID) AS [Total Of OrderID] " & _
             "FROM Orders " & _
             "GROUP BY Orders.ShipName " & _
             "PIVOT Format([OrderDate],'mmm yyyy') " & strForMonths & ";"
    ' changes the existing query's underlying SQL to the new one with the new months
    qdf.SQL = strSQL
End Function

Open in new window

Thanks, glad to help.  As a rule, I prefer to stay away from code in an Access forum as much as possible.  It's useful to have a small table Nums around.  I have one with 0 to 9 and with it I can generate any range under 10000 in a second or so, generate all the ends of month dates between start and end, and so on.