Wrong Results in query

[setis]

I have 3 tables:

*Members (MemberID)
*Cases (CaseID, MemberID)
*Activities (ActivityID, CaseID)

I am trying to do a query to see a count of cases and activities by members.

When I do Members->Cases(count) I get the correct results for nr of cases:
Member 1, 19 cases

When I try to add activities... : Members->Cases(count)->Activities(count), I get wrong results:
Member 1, 74 Cases, 72 Activities (The number of activities is correct, but the nr of cases is 19)

I changed the query properties to "Unique values - Yes" but I still get the same results.

What am I doing wrong?

 

[arnelgp]

It may double because it will create a cartesian when you join the 3 tables. Better use sub query on the faulty result.

 

[plog]

Please show the SQL of the query. Most likely you have items in the GROUP BY that shouldn't be there.

 

[isladogs]

Try first creating a select distinct query based on the 3 tables.
Do you get the results you expect or multiples of each record?
If it seems ok, then create an aggregate query based on your select query

 

[Pat Hartman]

Count() counts the rows in the domain.  Since you are selecting three tables - grandparent-parent-child, you are "duplicating" grandparents for every parent and parents and grandparents for every child.  What you are counting is only the child records.

You can solve the problem by using two queries.

Select MemberID,  Count(*) As CaseCount
From Members Left Join Cases on Members.MemberID = Cases.CaseID
Group By MemberID;

Save this as qCaseCount.

Create the second query and join qCaseCount to Activities
Select qCaseCount.*,  ActivityID, Count(*) As ActivityCount
From qCaseCount Left Join Activities on qCaseCount.CaseID = Activities.CaseID
Group By qCaseCount.MemberID, qCaseCount.CaseCount, Activities.CaseID;