Hello, I have a matrix with year and month as column headers.
I'm sure you know the problem that when I don't have data to one of the month, I have a "jump" in my matrix (from April its jump to June).

Is there a trick in RS to handle this except by changing my sql query and union it with all the month?

Thanks.

Honestly, the easiest and smartest way to do this is to add a table to your DB that has one record for every year and month.

Something like this:

TABLE: allMonths
    monthYear DATETIME

and then have entries in it such as
    1/1/2008
    2/1/2008
    3/1/2008
    ...
    12/1/2008
    1/1/2009
    ..
    ...
    12/1/2029

And then you do

SELECT AM.monthYear, ...fields from other table[s]...
FROM allMonths AS AM LEFT JOIN yourTable AS T
ON ( Year(AM.monthYear) = Year(T.someDateField)
     AND Month(AM.monthYear) = Month(T.someDateField)
...

I show this in a "Calendar of Events" where I create one record per *day* in order to show all dates in a given range. Here, on my demos page:
http://www.ClearviewDesign.com/Newbie

That's for ASP code, but the principle works the same for PHP, JSP, ASP.NET, etc.

Hi,
Thanks for the reply.

I was afraid that this will be the answer.

Thats the way I know but I hoped that there is a better way in RS to do that with out adding lots of waste rows.

Thank,Roy.

It's not *that* hard to do it without the extra table, but it's (a) faster, (b) simpler coding, and (c) less error prone to do it this way.

But you *could* do something like this:

<%
For yr = 2007 To 2010 ' or whatever
   Response.Write "<tr>" ' one row per year
   For mn = 1 To 12
       ' one cell per month
       Response.Write "<td>" & MonthName(mn) & " " & yr & "<br/>"
       curDate = #1/1/1900# ' dummy date, in case at EOF
       If Not RS.EOF Then curDate = RS("someDateField")
       Do While Year(curDate) = yr AND Month(curDate) = mn
           ... show data from this record ...<br/>
           RS.MoveNext ' see if another record for this same month
           If RS.EOF Then Exit Do
       Loop
       Response.Write "</td>"
   Next
   Response.Write "</tr>"
Next
%>

Something along those lines.