Totals in crosstab query

[lmoisan via AccessMonster.com]

Hi,

A while back, I submitted the following post and was provided with a solution
that worked. However, I now have an extra problem. I posted it but must have
done something wrong as I did not get any replies. I hope someone can help.

Many thanks in advance.

----------

My initial problem:

I designed a simple database that contains 1 table (tblVitals) with 3 text
fields (Client, Employee, Month). I can have many clients, many employees and
obviously no more than 12 months. I created a crosstab query with "Client" as
the row heading, "Month" as the column heading (therefore always 12 columns)
and "Client" as the value.

The purpose is to run a calendar style report based on the crosstab query
that would give me all clients to be contacted by our employees for every
month. Note that there will never be more than 5 clients per month per
employee.

I used the Concatenate function to list all clients per employee per month.
The function works but not like I would like it to be. It gives me all
clients to be contacted for the month BUT under all employees. I don't seem
to be able to distribute the clients by employee.

Here's the SQL:

TRANSFORM First(Concatenate("SELECT Client FROM [tblVitals] WHERE Month = " &
Chr(34) & [Month] & Chr(34))) AS Cases
SELECT tblVitals.Employee
FROM tblVitals
GROUP BY tblVitals.Employee
PIVOT tblVitals.Month;

The solution provided:

I see problems in your naming convention. The word "Month" is a reserved
word in Access VBA - it is a function: Month(<date>) returns the month
number of the indicated <date>. Therefore, the problem might be the
inability of Access SQL to determine what the item [Month] indicates: the
VBA function, or a column name. For clarity's sake try adding a table alias
to indicate that the [Month] is a column in a
table. Also, place square brackets around Month in the TRANSFORM clause.
You'll also have to include the Employee ID in the SELECT statement in the
TRANSFORM clause if you want to show only clients
associated w/ that employee.

Try this (not tested):

TRANSFORM First(Concatenate(
"SELECT Client
FROM [tblVitals]
WHERE [Month] = '" & V.[Month] & "' AND
Employee = '" & V.Employee & "'")) AS Cases

SELECT V.Employee
FROM tblVitals AS V
GROUP BY V.Employee
PIVOT V.Month;
------------

My new problem:

The users would like to have an extra row heading that would give them the
total number of clients per year per employee. When I use the "Count"
function, I get the total number of months each employee has clients instead
of the total number of clients. The value of the crosstab is a concatenated
text field that lists all clients per month (column) per employee (row). Some
master clients may have different company names (1 record per company name).
I created an alias that comprises all company names. In the concatenated
field, I show only the aliases and in brackets I show the number of companies
under the alias). The count should show the unique alias count for all 12
months. For example, in January, Jane could have 3 aliases listed that
comprise 12 companies overall. I would like the count for January to be 3.
The count as a row heading would add 3 for January, then 2 for Feb if we had
2 unique aliases in Feb and so on. In the end, the row could show 12 (i.e. 12
unique aliases).

The second curve ball is to have the same total but by month...

Here's the current SQL:

TRANSFORM First(Concatenate("SELECT DISTINCT [AliasCalc] FROM
[qryVitalsActive] WHERE [MonthWorked] = '" & [qryVitalsActive].[MonthWorked]
& "' AND [Employee] = '" & [tblValueAllStaff].[Employee] & "'")) AS Cases

SELECT qryVitalsActive.Employee, Count([Cases]) AS CountCases

FROM qryVitalsActive INNER JOIN tblValueAllStaff ON qryVitalsActive.Employee
= tblValueAllStaff.Employee

GROUP BY qryVitalsActive.Employee

PIVOT qryVitalsActive.MonthWorked In ("01Jan","02Feb","03Mar","04Apr","05May",

"06Jun","07Jul","08Aug","09Sep","10Oct","11Nov","12Dec");