crosstab query questions

S

Souris

UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

I use above union query for my crosstab query.
My crosstab query complains it does not recognize tblEmployee.employee_ID.
The union qury works fine. If I take out the join then the crosstab query
works, but it is not the result I am looking for.

Your help is great appreciated,
 
K

KARL DEWEY

You omitted something in your posting.
You say what you posted is a union query but there is only one SELECT
statement.
You say what you posted is a crosstab query but there is no TRANSFORM or
PIVOT part.
 
S

Souris

Thanks for the message,

the whole union query is

SELECT 1 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteRCM as MyValue, "Site RCM" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 2 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SBSSO as MyValue, "SBS SO" as MyCode from
tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 3 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteQME as MyValue, "Site QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 4 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQMER as MyValue, "Corp QMER" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 5 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQME as MyValue, "Corp QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 6 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, QualityTimeHour as MyValue, "Quality
Hour" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 7 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, QualityTimeMinute as MyValue, "Quality
Minute" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 8 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, EscalatedCall as MyValue, "Escalated
Call" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 9 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, TrainingPackets as MyValue, "Training
Packets" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 10 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, IKBOutStanding as MyValue, "IKB
Outstanding" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 11 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRatWork as MyValue, "CSR work" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 12 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRAbsent as MyValue, "CSR Absent" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 13 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, TimeCardSubmit as MyValue, "Time Card
Submit" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 14 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, OTHours as MyValue, "OT Hours" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 15 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 1 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"AHT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 16 as MyID, tblEDAActivityPlanMeetingDetails.ActivityDate as
[Activity Date], tblEmployee.first_name + " " + tblEmployee.Last_Name as
EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 2 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"IKB Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", tblEDAActivityPlanMeetingDetails.ActivityDate, now()) = 0


UNION

SELECT 17 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and
tblEDAActivityPlanMeetingDetails.MeetingElements = 3 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"CSI Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 18 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 4 and datediff("d",
meetingdate, now) = 0) as MyValue,
"ATT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 20 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and tblEDAActivityPlanMeetingDetails.MeetingElements = 6 and datediff("d",
meetingdate, now) = 0) as MyValue,
"SO Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0;



and crosstab query is following


TRANSFORM Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS SumOfMyValue
SELECT [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB ACTIVITY
DETAILS].MyCode, Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS [Total
Of MyValue]
FROM [SP GET CROSS TAB ACTIVITY DETAILS]
GROUP BY [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB
ACTIVITY DETAILS].MyCode
PIVOT [SP GET CROSS TAB ACTIVITY DETAILS].EDA;

Thanks again,



KARL DEWEY said:
You omitted something in your posting.
You say what you posted is a union query but there is only one SELECT
statement.
You say what you posted is a crosstab query but there is no TRANSFORM or
PIVOT part.
--
KARL DEWEY
Build a little - Test a little


Souris said:
UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

I use above union query for my crosstab query.
My crosstab query complains it does not recognize tblEmployee.employee_ID.
The union qury works fine. If I take out the join then the crosstab query
works, but it is not the result I am looking for.

Your help is great appreciated,
Click to expand...
Click to expand...
 
K

KARL DEWEY

I think I see the problem ---
It looks like your SELECT 15 through 20 subquery does not include the table
tblEmployee in the FROM statement. It is used in the WHERE part.
--
KARL DEWEY
Build a little - Test a little


Souris said:
Thanks for the message,

the whole union query is

SELECT 1 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteRCM as MyValue, "Site RCM" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 2 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SBSSO as MyValue, "SBS SO" as MyCode from
tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 3 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteQME as MyValue, "Site QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 4 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQMER as MyValue, "Corp QMER" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 5 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQME as MyValue, "Corp QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 6 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, QualityTimeHour as MyValue, "Quality
Hour" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 7 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, QualityTimeMinute as MyValue, "Quality
Minute" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 8 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, EscalatedCall as MyValue, "Escalated
Call" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 9 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, TrainingPackets as MyValue, "Training
Packets" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 10 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, IKBOutStanding as MyValue, "IKB
Outstanding" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 11 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRatWork as MyValue, "CSR work" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 12 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRAbsent as MyValue, "CSR Absent" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 13 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, TimeCardSubmit as MyValue, "Time Card
Submit" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 14 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, OTHours as MyValue, "OT Hours" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 15 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 1 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"AHT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 16 as MyID, tblEDAActivityPlanMeetingDetails.ActivityDate as
[Activity Date], tblEmployee.first_name + " " + tblEmployee.Last_Name as
EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 2 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"IKB Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", tblEDAActivityPlanMeetingDetails.ActivityDate, now()) = 0


UNION

SELECT 17 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and
tblEDAActivityPlanMeetingDetails.MeetingElements = 3 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"CSI Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 18 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 4 and datediff("d",
meetingdate, now) = 0) as MyValue,
"ATT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 20 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and tblEDAActivityPlanMeetingDetails.MeetingElements = 6 and datediff("d",
meetingdate, now) = 0) as MyValue,
"SO Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0;



and crosstab query is following


TRANSFORM Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS SumOfMyValue
SELECT [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB ACTIVITY
DETAILS].MyCode, Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS [Total
Of MyValue]
FROM [SP GET CROSS TAB ACTIVITY DETAILS]
GROUP BY [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB
ACTIVITY DETAILS].MyCode
PIVOT [SP GET CROSS TAB ACTIVITY DETAILS].EDA;

Thanks again,



KARL DEWEY said:
You omitted something in your posting.
You say what you posted is a union query but there is only one SELECT
statement.
You say what you posted is a crosstab query but there is no TRANSFORM or
PIVOT part.
--
KARL DEWEY
Build a little - Test a little


Souris said:
UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

I use above union query for my crosstab query.
My crosstab query complains it does not recognize tblEmployee.employee_ID.
The union qury works fine. If I take out the join then the crosstab query
works, but it is not the result I am looking for.

Your help is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
S

Souris

Thanks for the message,
Yes, it works when I add the tblEmployee in FROm clause.
The problem is that it does not return the reslut I wnat.

The tblEmployee refer the end of union query tblemployee.

Thanks again,


KARL DEWEY said:
I think I see the problem ---
It looks like your SELECT 15 through 20 subquery does not include the table
tblEmployee in the FROM statement. It is used in the WHERE part.
--
KARL DEWEY
Build a little - Test a little


Souris said:
Thanks for the message,

the whole union query is

SELECT 1 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteRCM as MyValue, "Site RCM" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 2 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SBSSO as MyValue, "SBS SO" as MyCode from
tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 3 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteQME as MyValue, "Site QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 4 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQMER as MyValue, "Corp QMER" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 5 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQME as MyValue, "Corp QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 6 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, QualityTimeHour as MyValue, "Quality
Hour" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 7 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, QualityTimeMinute as MyValue, "Quality
Minute" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 8 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, EscalatedCall as MyValue, "Escalated
Call" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 9 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, TrainingPackets as MyValue, "Training
Packets" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 10 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, IKBOutStanding as MyValue, "IKB
Outstanding" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 11 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRatWork as MyValue, "CSR work" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 12 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRAbsent as MyValue, "CSR Absent" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 13 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, TimeCardSubmit as MyValue, "Time Card
Submit" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 14 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, OTHours as MyValue, "OT Hours" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 15 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 1 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"AHT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 16 as MyID, tblEDAActivityPlanMeetingDetails.ActivityDate as
[Activity Date], tblEmployee.first_name + " " + tblEmployee.Last_Name as
EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 2 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"IKB Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", tblEDAActivityPlanMeetingDetails.ActivityDate, now()) = 0


UNION

SELECT 17 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and
tblEDAActivityPlanMeetingDetails.MeetingElements = 3 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"CSI Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 18 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 4 and datediff("d",
meetingdate, now) = 0) as MyValue,
"ATT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 20 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and tblEDAActivityPlanMeetingDetails.MeetingElements = 6 and datediff("d",
meetingdate, now) = 0) as MyValue,
"SO Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0;



and crosstab query is following


TRANSFORM Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS SumOfMyValue
SELECT [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB ACTIVITY
DETAILS].MyCode, Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS [Total
Of MyValue]
FROM [SP GET CROSS TAB ACTIVITY DETAILS]
GROUP BY [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB
ACTIVITY DETAILS].MyCode
PIVOT [SP GET CROSS TAB ACTIVITY DETAILS].EDA;

Thanks again,



KARL DEWEY said:
You omitted something in your posting.
You say what you posted is a union query but there is only one SELECT
statement.
You say what you posted is a crosstab query but there is no TRANSFORM or
PIVOT part.
--
KARL DEWEY
Build a little - Test a little


:

UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

I use above union query for my crosstab query.
My crosstab query complains it does not recognize tblEmployee.employee_ID.
The union qury works fine. If I take out the join then the crosstab query
works, but it is not the result I am looking for.

Your help is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Yes, it works when I add the tblEmployee in FROm clause. The problem is
that it does not return the reslut I wnat.
I just connected with your other post. You can not check a bad query by
adding more stuff to it. You have to disassemble it to test.

Make sure each of the SELECT parts of the union work by running them
standalone and verifying the results.
--
KARL DEWEY
Build a little - Test a little


Souris said:
Thanks for the message,
Yes, it works when I add the tblEmployee in FROm clause.
The problem is that it does not return the reslut I wnat.

The tblEmployee refer the end of union query tblemployee.

Thanks again,


KARL DEWEY said:
I think I see the problem ---
It looks like your SELECT 15 through 20 subquery does not include the table
tblEmployee in the FROM statement. It is used in the WHERE part.
--
KARL DEWEY
Build a little - Test a little


Souris said:
Thanks for the message,

the whole union query is

SELECT 1 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteRCM as MyValue, "Site RCM" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 2 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SBSSO as MyValue, "SBS SO" as MyCode from
tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 3 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteQME as MyValue, "Site QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 4 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQMER as MyValue, "Corp QMER" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 5 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQME as MyValue, "Corp QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 6 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, QualityTimeHour as MyValue, "Quality
Hour" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 7 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, QualityTimeMinute as MyValue, "Quality
Minute" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 8 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, EscalatedCall as MyValue, "Escalated
Call" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 9 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, TrainingPackets as MyValue, "Training
Packets" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 10 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, IKBOutStanding as MyValue, "IKB
Outstanding" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 11 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRatWork as MyValue, "CSR work" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 12 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRAbsent as MyValue, "CSR Absent" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 13 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, TimeCardSubmit as MyValue, "Time Card
Submit" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 14 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, OTHours as MyValue, "OT Hours" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 15 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 1 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"AHT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 16 as MyID, tblEDAActivityPlanMeetingDetails.ActivityDate as
[Activity Date], tblEmployee.first_name + " " + tblEmployee.Last_Name as
EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 2 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"IKB Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", tblEDAActivityPlanMeetingDetails.ActivityDate, now()) = 0


UNION

SELECT 17 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and
tblEDAActivityPlanMeetingDetails.MeetingElements = 3 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"CSI Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 18 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 4 and datediff("d",
meetingdate, now) = 0) as MyValue,
"ATT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 20 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and tblEDAActivityPlanMeetingDetails.MeetingElements = 6 and datediff("d",
meetingdate, now) = 0) as MyValue,
"SO Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0;



and crosstab query is following


TRANSFORM Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS SumOfMyValue
SELECT [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB ACTIVITY
DETAILS].MyCode, Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS [Total
Of MyValue]
FROM [SP GET CROSS TAB ACTIVITY DETAILS]
GROUP BY [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB
ACTIVITY DETAILS].MyCode
PIVOT [SP GET CROSS TAB ACTIVITY DETAILS].EDA;

Thanks again,



:

You omitted something in your posting.
You say what you posted is a union query but there is only one SELECT
statement.
You say what you posted is a crosstab query but there is no TRANSFORM or
PIVOT part.
--
KARL DEWEY
Build a little - Test a little


:

UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

I use above union query for my crosstab query.
My crosstab query complains it does not recognize tblEmployee.employee_ID.
The union qury works fine. If I take out the join then the crosstab query
works, but it is not the result I am looking for.

Your help is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
S

Souris

Thnaks again,

The union query works.
The union query retruns the result i want using tblEmployee.employee_ID.

The crosstab query complaiand that it does not recognize it.

for no, I create a local temp table to store data from my union query and
use the temp local table for my crosstab query whic works.

Thanks again,





KARL DEWEY said:
that it does not return the reslut I wnat.
I just connected with your other post. You can not check a bad query by
adding more stuff to it. You have to disassemble it to test.

Make sure each of the SELECT parts of the union work by running them
standalone and verifying the results.
--
KARL DEWEY
Build a little - Test a little


Souris said:
Thanks for the message,
Yes, it works when I add the tblEmployee in FROm clause.
The problem is that it does not return the reslut I wnat.

The tblEmployee refer the end of union query tblemployee.

Thanks again,


KARL DEWEY said:
I think I see the problem ---
It looks like your SELECT 15 through 20 subquery does not include the table
tblEmployee in the FROM statement. It is used in the WHERE part.
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the message,

the whole union query is

SELECT 1 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteRCM as MyValue, "Site RCM" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 2 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SBSSO as MyValue, "SBS SO" as MyCode from
tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 3 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, SiteQME as MyValue, "Site QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 4 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQMER as MyValue, "Corp QMER" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 5 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, CorpQME as MyValue, "Corp QME" as MyCode
from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 6 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, QualityTimeHour as MyValue, "Quality
Hour" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 7 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, QualityTimeMinute as MyValue, "Quality
Minute" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 8 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, EscalatedCall as MyValue, "Escalated
Call" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 9 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name +
" " + tblEmployee.Last_Name as EDA, TrainingPackets as MyValue, "Training
Packets" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 10 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, IKBOutStanding as MyValue, "IKB
Outstanding" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 11 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRatWork as MyValue, "CSR work" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 12 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, CSRAbsent as MyValue, "CSR Absent" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0
UNION

SELECT 13 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, TimeCardSubmit as MyValue, "Time Card
Submit" as MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 14 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA, OTHours as MyValue, "OT Hours" as
MyCode from tblEDAActivity
INNER JOIN tblEmployee on tblEDAActivity.EDA_ID = tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

UNION

SELECT 15 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 1 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"AHT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 16 as MyID, tblEDAActivityPlanMeetingDetails.ActivityDate as
[Activity Date], tblEmployee.first_name + " " + tblEmployee.Last_Name as
EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 2 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"IKB Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", tblEDAActivityPlanMeetingDetails.ActivityDate, now()) = 0


UNION

SELECT 17 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and
tblEDAActivityPlanMeetingDetails.MeetingElements = 3 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"CSI Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 18 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 4 and datediff("d",
meetingdate, now) = 0) as MyValue,
"ATT Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID and
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 ) as MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0


UNION

SELECT 20 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails
where tblEDAActivityPlanMeetingDetails.EDA_ID = tblEmployee.Employee_ID
and tblEDAActivityPlanMeetingDetails.MeetingElements = 6 and datediff("d",
meetingdate, now) = 0) as MyValue,
"SO Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0;



and crosstab query is following


TRANSFORM Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS SumOfMyValue
SELECT [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB ACTIVITY
DETAILS].MyCode, Sum([SP GET CROSS TAB ACTIVITY DETAILS].MyValue) AS [Total
Of MyValue]
FROM [SP GET CROSS TAB ACTIVITY DETAILS]
GROUP BY [SP GET CROSS TAB ACTIVITY DETAILS].MyID, [SP GET CROSS TAB
ACTIVITY DETAILS].MyCode
PIVOT [SP GET CROSS TAB ACTIVITY DETAILS].EDA;

Thanks again,



:

You omitted something in your posting.
You say what you posted is a union query but there is only one SELECT
statement.
You say what you posted is a crosstab query but there is no TRANSFORM or
PIVOT part.
--
KARL DEWEY
Build a little - Test a little


:

UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

I use above union query for my crosstab query.
My crosstab query complains it does not recognize tblEmployee.employee_ID.
The union qury works fine. If I take out the join then the crosstab query
works, but it is not the result I am looking for.

Your help is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

union query returns floating point value 0
sub query count 8
dcount in a crosstab query 3
between datetime value from date time picker 3
found record base on count records on child table 4
insert a crosstab in to temp table query 2
Date Limit in Crosstab from Union query 2
Crosstab query using union query has subquery 0

Top