crosstab query child table count

I

inungh

I have a crosstab query and would like to count parent and child
tables like following:


Select tblParent.Parent, count(tblParent.PrentID), (Select
count(tblChild.ChildID) where tblParent.ParentID = tblChild.ParentID)
From tblParent
Group By tblParent.Parent


It seems that MS Access does not allow to do this using crosstab
query.
Are there any workaround?

Your information is great appreciated,
 
D

Duane Hookom

Can you provide the SQL of your crosstab? How about some context to this
question so we can wrap our heads around what you are attempting to do?
 
I

inungh

Can you provide the SQL of your crosstab? How about some context to this
question so we can wrap our heads around what you are attempting to do?
--
Duane Hookom
Microsoft Access MVP








- Show quoted text -


Thanks for helping,
Here is the SQL of crosstab query

TRANSFORM Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID
SELECT tblActivity.ActivityCode,
Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID1, (Select
count(Elements_ID) from tblEDAPlanElementsDetails DD where [DD].
[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID] and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID]) AS Expr1
FROM (((((tblEDAActivityPlanDetails INNER JOIN tblActivity ON
tblEDAActivityPlanDetails.ActivityID = tblActivity.ActivityID) INNER
JOIN (tblEDAPlanElementsDetails INNER JOIN tblMeetingelements ON
tblEDAPlanElementsDetails.Elements_ID =
tblMeetingelements.MeetingElementsID) ON
(tblEDAActivityPlanDetails.CSR_ID = tblEDAPlanElementsDetails.CSR_ID)
AND (tblEDAActivityPlanDetails.ActivityID =
tblEDAPlanElementsDetails.ActivityID) AND
(tblEDAActivityPlanDetails.ActivityDate =
tblEDAPlanElementsDetails.ActivityDate) AND
(tblEDAActivityPlanDetails.EDA_ID = tblEDAPlanElementsDetails.EDA_ID))
INNER JOIN tblEmployee ON tblEDAActivityPlanDetails.CSR_ID =
tblEmployee.Employee_ID) INNER JOIN tblEmployee AS EDA ON
tblEmployee.EDA = EDA.Employee_ID) INNER JOIN tblEmployee AS
Supervisor ON tblEmployee.Supervisors = Supervisor.Employee_ID) INNER
JOIN tblEmployee AS Manager ON tblEmployee.Manager =
Manager.Employee_ID
GROUP BY tblActivity.ActivityCode
PIVOT tblMeetingelements.MeetingElementsCode
WITH OWNERACCESS OPTION;


MS Access complained that the following sub query does not regnize
[tblEDAActivityPlanDetails].[EDA_ID] field

(Select count(Elements_ID) from tblEDAPlanElementsDetails DD where
[DD].[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID] and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID])


Thanks millions,
 
D

Duane Hookom

Crosstabs are touchy. They don't care at all for subqueries. You can
generally replace the subquery with the much less efficient DCount().

--
Duane Hookom
Microsoft Access MVP


inungh said:
Can you provide the SQL of your crosstab? How about some context to this
question so we can wrap our heads around what you are attempting to do?
--
Duane Hookom
Microsoft Access MVP








- Show quoted text -


Thanks for helping,
Here is the SQL of crosstab query

TRANSFORM Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID
SELECT tblActivity.ActivityCode,
Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID1, (Select
count(Elements_ID) from tblEDAPlanElementsDetails DD where [DD].
[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID] and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID]) AS Expr1
FROM (((((tblEDAActivityPlanDetails INNER JOIN tblActivity ON
tblEDAActivityPlanDetails.ActivityID = tblActivity.ActivityID) INNER
JOIN (tblEDAPlanElementsDetails INNER JOIN tblMeetingelements ON
tblEDAPlanElementsDetails.Elements_ID =
tblMeetingelements.MeetingElementsID) ON
(tblEDAActivityPlanDetails.CSR_ID = tblEDAPlanElementsDetails.CSR_ID)
AND (tblEDAActivityPlanDetails.ActivityID =
tblEDAPlanElementsDetails.ActivityID) AND
(tblEDAActivityPlanDetails.ActivityDate =
tblEDAPlanElementsDetails.ActivityDate) AND
(tblEDAActivityPlanDetails.EDA_ID = tblEDAPlanElementsDetails.EDA_ID))
INNER JOIN tblEmployee ON tblEDAActivityPlanDetails.CSR_ID =
tblEmployee.Employee_ID) INNER JOIN tblEmployee AS EDA ON
tblEmployee.EDA = EDA.Employee_ID) INNER JOIN tblEmployee AS
Supervisor ON tblEmployee.Supervisors = Supervisor.Employee_ID) INNER
JOIN tblEmployee AS Manager ON tblEmployee.Manager =
Manager.Employee_ID
GROUP BY tblActivity.ActivityCode
PIVOT tblMeetingelements.MeetingElementsCode
WITH OWNERACCESS OPTION;


MS Access complained that the following sub query does not regnize
[tblEDAActivityPlanDetails].[EDA_ID] field

(Select count(Elements_ID) from tblEDAPlanElementsDetails DD where
[DD].[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID] and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID])


Thanks millions,
.
 
I

inungh

Crosstabs are touchy. They don't care at all for subqueries. You can
generally replace the subquery with the much less efficient DCount().

--
Duane Hookom
Microsoft Access MVP



Thanks for helping,
Here is the SQL of crosstab query
TRANSFORM Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID
SELECT tblActivity.ActivityCode,
Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID1, (Select
count(Elements_ID) from tblEDAPlanElementsDetails DD where [DD].
[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID]  and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID]) AS Expr1
FROM (((((tblEDAActivityPlanDetails INNER JOIN tblActivity ON
tblEDAActivityPlanDetails.ActivityID = tblActivity.ActivityID) INNER
JOIN (tblEDAPlanElementsDetails INNER JOIN tblMeetingelements ON
tblEDAPlanElementsDetails.Elements_ID =
tblMeetingelements.MeetingElementsID) ON
(tblEDAActivityPlanDetails.CSR_ID = tblEDAPlanElementsDetails.CSR_ID)
AND (tblEDAActivityPlanDetails.ActivityID =
tblEDAPlanElementsDetails.ActivityID) AND
(tblEDAActivityPlanDetails.ActivityDate =
tblEDAPlanElementsDetails.ActivityDate) AND
(tblEDAActivityPlanDetails.EDA_ID = tblEDAPlanElementsDetails.EDA_ID))
INNER JOIN tblEmployee ON tblEDAActivityPlanDetails.CSR_ID =
tblEmployee.Employee_ID) INNER JOIN tblEmployee AS EDA ON
tblEmployee.EDA = EDA.Employee_ID) INNER JOIN tblEmployee AS
Supervisor ON tblEmployee.Supervisors = Supervisor.Employee_ID) INNER
JOIN tblEmployee AS Manager ON tblEmployee.Manager =
Manager.Employee_ID
GROUP BY tblActivity.ActivityCode
PIVOT tblMeetingelements.MeetingElementsCode
WITH OWNERACCESS OPTION;
MS Access complained that the following sub query does not regnize
[tblEDAActivityPlanDetails].[EDA_ID] field
(Select count(Elements_ID) from tblEDAPlanElementsDetails DD where
[DD].[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID]  and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID])
Thanks millions,
.- Hide quoted text -

- Show quoted text -

it does not recognize dcount either.
Any other solution for this?

Thanks again,
 
D

Duane Hookom

Yes it does.

--
Duane Hookom
MS Access MVP


inungh said:
Crosstabs are touchy. They don't care at all for subqueries. You can
generally replace the subquery with the much less efficient DCount().

--
Duane Hookom
Microsoft Access MVP



inungh said:
On Mar 2, 11:43 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide the SQL of your crosstab? How about some context to
this
question so we can wrap our heads around what you are attempting to
do?
:
I have a crosstab query and would like to count parent and child
tables like following:
Select tblParent.Parent, count(tblParent.PrentID), (Select
count(tblChild.ChildID) where tblParent.ParentID =
tblChild.ParentID)
From tblParent
Group By tblParent.Parent
It seems that MS Access does not allow to do this using crosstab
query.
Are there any workaround?
Your information is great appreciated,
.- Hide quoted text -
- Show quoted text -
Thanks for helping,
Here is the SQL of crosstab query
TRANSFORM Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID
SELECT tblActivity.ActivityCode,
Count(tblEDAActivityPlanDetails.EDA_ID) AS CountOfEDA_ID1, (Select
count(Elements_ID) from tblEDAPlanElementsDetails DD where [DD].
[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID] and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID]) AS Expr1
FROM (((((tblEDAActivityPlanDetails INNER JOIN tblActivity ON
tblEDAActivityPlanDetails.ActivityID = tblActivity.ActivityID) INNER
JOIN (tblEDAPlanElementsDetails INNER JOIN tblMeetingelements ON
tblEDAPlanElementsDetails.Elements_ID =
tblMeetingelements.MeetingElementsID) ON
(tblEDAActivityPlanDetails.CSR_ID = tblEDAPlanElementsDetails.CSR_ID)
AND (tblEDAActivityPlanDetails.ActivityID =
tblEDAPlanElementsDetails.ActivityID) AND
(tblEDAActivityPlanDetails.ActivityDate =
tblEDAPlanElementsDetails.ActivityDate) AND
(tblEDAActivityPlanDetails.EDA_ID = tblEDAPlanElementsDetails.EDA_ID))
INNER JOIN tblEmployee ON tblEDAActivityPlanDetails.CSR_ID =
tblEmployee.Employee_ID) INNER JOIN tblEmployee AS EDA ON
tblEmployee.EDA = EDA.Employee_ID) INNER JOIN tblEmployee AS
Supervisor ON tblEmployee.Supervisors = Supervisor.Employee_ID) INNER
JOIN tblEmployee AS Manager ON tblEmployee.Manager =
Manager.Employee_ID
GROUP BY tblActivity.ActivityCode
PIVOT tblMeetingelements.MeetingElementsCode
WITH OWNERACCESS OPTION;
MS Access complained that the following sub query does not regnize
[tblEDAActivityPlanDetails].[EDA_ID] field
(Select count(Elements_ID) from tblEDAPlanElementsDetails DD where
[DD].[EDA_ID] = [tblEDAActivityPlanDetails].[EDA_ID] and
[tblEDAActivityPlanDetails].[ActivityDate] = [DD].[Activitydate] and
[tblEDAActivityPlanDetails].[ActivityID] = [DD].[ActivityID] and
[tblEDAActivityPlanDetails].[CSR_ID] = [DD].[CSR_ID])
Thanks millions,
.- Hide quoted text -

- Show quoted text -

it does not recognize dcount either.
Any other solution for this?

Thanks again,
 

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


Top