Activity schedule

edubbelaar · November 24th, 2004, 09:55 AM

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?

Mihai B · November 24th, 2004, 10:30 AM

It appears to me that you calculated the aggregate expression MaxOfActivityDescr. Shouldn't it be Count? And you should count the primary key (ID) or else you would count the number of activity types.

Mihai

edubbelaar · November 24th, 2004, 11:11 AM

Thanks for your answer, I did some changings and I have some solution:

Code:

TRANSFORM Last(Q_GTMActivities.ActivityDescription) AS LastOfActivityDescription
SELECT Q_GTMActivities.ActivityType
FROM Q_GTMActivities
WHERE (((Q_GTMActivities.Reseller)="Company 1") AND ((Q_GTMActivities.ActivityYear)="2005"))
GROUP BY Q_GTMActivities.ActivityType, Q_GTMActivities.Reseller, Q_GTMActivities.ActivityYear, Q_GTMActivities.ActivityDescription
ORDER BY Q_GTMActivities.ActivityType
PIVOT Q_GTMActivities.ActivityMonth In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

The activity types are now repeated for all descriptions. I now want all activitytypes/month duplicated in one field...?!

Mihai B · November 24th, 2004, 11:21 AM

I imagine you have to cut out the repeated ActivitiyDescription Group By, since they are shown as value, but I can't understand exactly what you want.