crosstab query child table count

  • Thread starter Thread starter inungh
  • Start date Start date
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,
 
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?
 
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 -
Click to expand...


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,
 
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 -
Click to expand...


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,
.
Click to expand...
 
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
Click to expand...
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;
Click to expand...
MS Access complained that the following sub query does not regnize
[tblEDAActivityPlanDetails].[EDA_ID] field
Click to expand...
(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])
Click to expand...
Thanks millions,
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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

Thanks again,
 
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?
Click to expand...
:
I have a crosstab query and would like to count parent and child
tables like following:
Click to expand...
Select tblParent.Parent, count(tblParent.PrentID), (Select
count(tblChild.ChildID) where tblParent.ParentID =
tblChild.ParentID)
From tblParent
Group By tblParent.Parent
Click to expand...
It seems that MS Access does not allow to do this using crosstab
query.
Are there any workaround?
Click to expand...
Your information is great appreciated,
.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Thanks for helping,
Here is the SQL of crosstab query
Click to expand...
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;
Click to expand...
MS Access complained that the following sub query does not regnize
[tblEDAActivityPlanDetails].[EDA_ID] field
Click to expand...
(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])
Click to expand...
Thanks millions,
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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

Thanks again,
Click to expand...
 
Back
Top