[AccessD] How to create a 2 year multimonth report

Susan,

    I have not seen any reply to your post so far. If the problem is yet to be resolved, the following course of action is suggested -

    Let Q_Cat be the source query (or table) having the following columns -
        MonthYear (Text) - showing 'Jan 03' etc
        Item (Text) - Typical entries 'Room' & 'Meals'
        Booked (Number) - This field will have the value of Rooms or Meals booked.

    Based upon the above, create a crosstab query. In design view of this query, specify as follows -
        MonthYear - Group By - Column Heading
        Item - Group By - Row Heading
        Booked - Sum - Value

    In properties dialog box of this query, against Column Headings, enter the desired sequence of columns (e.g. "Jan 03", "Jan 04", "Feb 03", "Feb 04" and so on). This will get you the required output.

    The SQL for this query will look like -
TRANSFORM Sum(Q_Cat.Booked) AS SumOfBooked
SELECT Q_Cat.Item
FROM Q_Cat
GROUP BY Q_Cat.Item
PIVOT Q_Cat.MonthYear In ("Jan 03","Jan 04","Feb 03","Feb 04","Mar 03","Mar 04");

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Klos, Susan 
  To: 'AccessD at databaseadvisors.com' 
  Sent: Friday, April 30, 2004 17:08
  Subject: [AccessD] How to create a 2 year multimonth report


  I have to create a summary report which has to include this year's data and last year's data.  It is to look something like this:

  Item      Jan 04   Jan 03   Feb 04  Feb 03  Mar 04  Mar 03
  Meals      200      198        210   209      185       190
  Rooms    30         28           34       34         29         31 

  I can do the summary for 04 and 03 but I can't figure out how to get the months together like above. 

  Susan Klos
  Senior Database Analyst
  Evaluation and Reporting
  Florida Department of Education
  850-245-0708 
  sc 205-0708