Prev: Return all dates for 'next month' irrespective of year or day
Next: Query Question
From: Duane Hookom on 19 Mar 2010 08:36
Yes it does.

--
Duane Hookom
MS Access MVP


"inungh" <inungh(a)gmail.com> wrote in message
news:ac0b3f46-65e9-47e4-8e75-670de1cb56d0(a)19g2000yqu.googlegroups.com...
> 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,

First  |  Prev  | 
Pages: 1 2
Prev: Return all dates for 'next month' irrespective of year or day
Next: Query Question