Hi all,
Please advice me on the following issue:
I have a table with activities by month, something like this:
ID Month Year Type Description
--------------------------------------------------
01 Jan 2005 Campign Summer discount
02 Jan 2005 Adverts CAD/CAM Magazine
03 Jan 2005 Adverts Machinery
04 Feb 2005 Seminar Hotel Beurs
and so on....
I've created a cross tab query, that should display all activity types (row heading), all months (colmn heading) by a given year.
The SQL code look like this:
Code:
TRANSFORM Max(T_GTMActivities.ActivityDescription) AS MaxOfActivityDescription
SELECT T_GTMActivities.ActivityType
FROM T_GTMActivities
WHERE (((T_GTMActivities.ActivityYear)=[Forms]![F_Reseller_GTMActivitiesPlanningSelection]![cboSalesYear]) AND ((T_GTMActivities.Reseller)=[Forms]![F_Reseller_GTMActivitiesPlanningSelection]![cboReseller]))
GROUP BY T_GTMActivities.ActivityType
PIVOT T_GTMActivities.ActivityMonth In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
As you can see in my table example above, there are 2 adverts in Jan. But my query only shows one of them. Not both. If did some experiments with the options Max, Min, First, Last, Count but couldn't find the solution.
I want the output like this
Type Jan Feb Mar etc.
-------------------------------------------------
Campign Summer discount
Adverts CAD/CAM Magazine
Adverts Machinery
Seminar Hotel Beurs
Could anyone help?