crosstab query child table count
in [Queries]
|
From: inungh on 2 Mar 2010 10:11 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,
From: Duane Hookom on 2 Mar 2010 11:43 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 "inungh" wrote: > 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, > . >
From: inungh on 2 Mar 2010 13:03 On Mar 2, 11:43 am, Duane Hookom <duanehookom(a)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? > -- > Duane Hookom > Microsoft Access MVP > > > > "inungh" wrote: > > 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,
From: Duane Hookom on 2 Mar 2010 14:54 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" wrote: > On Mar 2, 11:43 am, Duane Hookom <duanehookom(a)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? > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > > > "inungh" wrote: > > > 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, > . >
From: inungh on 19 Mar 2010 07:31 On Mar 2, 3:54 pm, Duane Hookom <duanehookom(a)NO_SPAMhotmail.com> wrote: > 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" wrote: > > On Mar 2, 11:43 am, Duane Hookom <duanehookom(a)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? > > > -- > > > Duane Hookom > > > Microsoft Access MVP > > > > "inungh" wrote: > > > > 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,
|
Next
|
Last
Pages: 1 2 Prev: Return all dates for 'next month' irrespective of year or day Next: Query Question |