Concatenate problem in crosstab query

[lmoisan]

I hope someone out there can help me. I've tried every solution I could find
in all the posts but have yet to succeed in achieving my goal. 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. My knowledge of Access and
VB is limited so any solution would be greatly appreciated.

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;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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;

Note: I used single quotes in place of your Chr(34), for clarity. If
Employee is a number remove the single quotes around V.Employee.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTJmjIechKqOuFEgEQJmNQCg502R3i4fD9RiXy81dV2mJhsNnXUAnA32
+55v4PxeK8WPY3S8QxeDy/QT
=KNyk
-----END PGP SIGNATURE-----

 

[lmoisan via AccessMonster.com]

It worked PERFECTLY! Thank you so much for the quick reply. Much appreciated.

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;

Note: I used single quotes in place of your Chr(34), for clarity. If
Employee is a number remove the single quotes around V.Employee.

I hope someone out there can help me. I've tried every solution I could find
in all the posts but have yet to succeed in achieving my goal. I designed a

[quoted text clipped - 23 lines]

GROUP BY tblVitals.Employee
PIVOT tblVitals.Month;