sum vs count - join problem?

[AccessIdiot]

I have three tables used in a query.

Table A: Has field with name of species.
Table B: Has the number of specimens caught per catch.
Table C: Has measurements of specimens.

I have generated the query in design view to get the min, max, and avg length of two particular species. The query should also report the number of specimens. For some reason I am not getting the right numbers.

For example, I know for a fact that there were 84 sturgeon. If I use SUM(SpecimenCount) I get a total of 240? But if I use COUNT(SpecimenCount) I get 84.

However, in a simpler query that just reports name of species and SUM(SpecimenCount) it reports the correct number of 84.

My buddy suggested it might have something to do with the kind of join Access is using. When I look at the query in SQL view it is using an Inner Join and looks like this (sorry for the long query)

SELECT tbl_Species_Freshwater.Common_Name, Min(tbl_Specimen_Fish_Replicate.Total_Len) AS MinOfTotal_Len, Max(tbl_Specimen_Fish_Replicate.Total_Len) AS MaxOfTotal_Len, Avg(tbl_Specimen_Fish_Replicate.Total_Len) AS AvgOfTotal_Len, Min(tbl_Specimen_Fish_Replicate.Standard_Len) AS MinOfStandard_Len, Max(tbl_Specimen_Fish_Replicate.Standard_Len) AS MaxOfStandard_Len, Avg(tbl_Specimen_Fish_Replicate.Standard_Len) AS AvgOfStandard_Len, Sum(tbl_Specimen_Replicate.SpecimenCount) AS SumOfSpecimenCount
FROM tbl_Species_Freshwater INNER JOIN (tbl_Specimen_Replicate INNER JOIN tbl_Specimen_Fish_Replicate ON tbl_Specimen_Replicate.Specimen_ID = tbl_Specimen_Fish_Replicate.Specimen_ID) ON tbl_Species_Freshwater.Species_ID = tbl_Specimen_Replicate.Species_ID
GROUP BY tbl_Species_Freshwater.Common_Name, tbl_Species_Freshwater.Species_Code
HAVING (((tbl_Species_Freshwater.Species_Code)="GRNSTURG" Or (tbl_Species_Freshwater.Species_Code)="WHTSTURG"))
ORDER BY tbl_Species_Freshwater.Common_Name;

I have no idea what kind of join I should be using instead. I just know that the correct number of sturgeon in the db is 84. :)

Any ideas?

[AccessIdiot]

Okay, I might have found a solution. Instead of trying to do a sum on the SpecimenCount field I just did a count on the total length field - in other words, counting how many records held a total length. I think that will work just fine.

:)

[Rabbit]

The problem is the multiple joins.

What's going on is:

 Table1
MainID(PK)
1
2
3
 
 Table2
SecID(PK)     MainID(FK)
1             1
2             1
3             2
4             2
5             3
6             3
 
 Table3
TerID(PK)     SecID(FK)
1             1
2             1
3             2
4             2
5             3
6             3
7             4
8             4
9             5
10            5
11            6
12            6
 

When you join Table2 and Table3, you get:

TerID     Table2.MainID     Table2.SecID=Table3.SecID
1         1                 1
2         1                 1
3         1                 2
4         1                 2
5         2                 3
6         2                 3
7         2                 4
8         2                 4
9         3                 5
10        3                 5
11        3                 6
12        3                 6
 

So, as you can see, if you go to sum/count anything in Table2, it's going to be more than it's supposed to be because its records get multiplied by the one-to-many relationship. In this case, any count done on Table2 get's mutliplied by 2. But only because I gave each record in Table2 2 records in Table3. So depending on how many duplicate foreign keys you have, the more your count/sum gets multiplied.

And the problem is even greater if you do a sum/count on Table1.

You'll have to seperate the sum into another query.

[AccessIdiot]

Ah, that makes sense. Well I'm certainly glad I found a work around!

Thanks for the explanation

[Rabbit]

Not a problem, good luck.