By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

sum vs count - join problem?

AccessIdiot
100+
P: 493
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)

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. GROUP BY tbl_Species_Freshwater.Common_Name, tbl_Species_Freshwater.Species_Code
  4. HAVING (((tbl_Species_Freshwater.Species_Code)="GRNSTURG" Or (tbl_Species_Freshwater.Species_Code)="WHTSTURG"))
  5. 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?
Aug 13 '07 #1
Share this Question
Share on Google+
4 Replies


AccessIdiot
100+
P: 493
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.

:)
Aug 13 '07 #2

Rabbit
Expert Mod 10K+
P: 12,389
The problem is the multiple joins.

What's going on is:

Expand|Select|Wrap|Line Numbers
  1. Table1
  2. MainID(PK)
  3. 1
  4. 2
  5. 3
  6.  
  7. Table2
  8. SecID(PK)     MainID(FK)
  9. 1             1
  10. 2             1
  11. 3             2
  12. 4             2
  13. 5             3
  14. 6             3
  15.  
  16. Table3
  17. TerID(PK)     SecID(FK)
  18. 1             1
  19. 2             1
  20. 3             2
  21. 4             2
  22. 5             3
  23. 6             3
  24. 7             4
  25. 8             4
  26. 9             5
  27. 10            5
  28. 11            6
  29. 12            6
  30.  
When you join Table2 and Table3, you get:
Expand|Select|Wrap|Line Numbers
  1. TerID     Table2.MainID     Table2.SecID=Table3.SecID
  2. 1         1                 1
  3. 2         1                 1
  4. 3         1                 2
  5. 4         1                 2
  6. 5         2                 3
  7. 6         2                 3
  8. 7         2                 4
  9. 8         2                 4
  10. 9         3                 5
  11. 10        3                 5
  12. 11        3                 6
  13. 12        3                 6
  14.  
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.
Aug 13 '07 #3

AccessIdiot
100+
P: 493
Ah, that makes sense. Well I'm certainly glad I found a work around!

Thanks for the explanation
Aug 13 '07 #4

Rabbit
Expert Mod 10K+
P: 12,389
Not a problem, good luck.
Aug 14 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.