Solved

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

Posted on 2011-02-11
23
865 Views
Last Modified: 2012-05-11
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.
K
0
Comment
Question by:Karen Schaefer
  • 13
  • 6
  • 4
23 Comments
 
LVL 15

Expert Comment

by:dave4dl
ID: 34875503
I recommend naming all your fields with generic names like "Offset1Month", "Offset2Months",...
0
 

Author Comment

by:Karen Schaefer
ID: 34875521
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.

k
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 34876147
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34883959
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.
0
 

Author Comment

by:Karen Schaefer
ID: 34889124
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.
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 34890609
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
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 34890615
here is a tutorial on conditional formatting: http://www.contextures.com/xlcondFormat01.html
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34890928
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

0
 

Author Comment

by:Karen Schaefer
ID: 34891267
Here is what I have so far and I am not getting any records returned - What am I missing?

K
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

0
 

Author Comment

by:Karen Schaefer
ID: 34891303
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?

K
0
 

Author Comment

by:Karen Schaefer
ID: 34891515
GrayL,

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

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:Karen Schaefer
ID: 34891644
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
0
 

Author Comment

by:Karen Schaefer
ID: 34891710
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.  

K
0
 

Author Comment

by:Karen Schaefer
ID: 34891737
0
 

Author Comment

by:Karen Schaefer
ID: 34891786
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.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 34892291
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.
0
 

Author Comment

by:Karen Schaefer
ID: 34892312
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?

K
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34893391
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.
0
 

Author Comment

by:Karen Schaefer
ID: 34898860
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?

K
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 34909111
It is relatively easy to create such a record set.  You need a table Nums of with the integer field Num containing consecutive integers from 0 to the max number of months you will ever want to see.  

Then run this query:

SELECT  DISTINCT Format(DateSerial(Year(sDate),Month(sDate + Num),1),"yymm") AS YYMM FROM Nums WHERE
DateSerial(Year(sDate),Month(sDate + Num),1) BETWEEN sDate and eDate;


0
 

Author Closing Comment

by:Karen Schaefer
ID: 34909726
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.
0
 

Author Comment

by:Karen Schaefer
ID: 34909727

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)
    Loop
   ' 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
    
    qdf.Close
End Function

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 34910423
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.  
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now