On Microsoft Access: Query for Count Returns Incorrect Number

Question

In the following query, the following should be returned: Name of Parent Tree, Species Latin Name, Count of Trays of Seeds Grown, Count of Seeds Germinated. 

However, the Count of Trays of Seeds Grown should be 1, there is only 1 record in that table. The count that is being displayed is instead the count of seeds germinated. It appears to be performing one count of Trays for each count of Seeds Germinated. So I understand what is happening - but not why or how to fix it. Any tips welcome. 

For reference, the SQL code is:

SELECT Trees.ID, Trees.Common_Name AS [Tree Name], Species.Latin_Name AS [Latin Name], Count(Trays.No_Of_Seeds) AS [Number of Trays], Count(Germinations.ID) AS [Number of Germinations]

FROM Species INNER JOIN (((Trees INNER JOIN Seeds ON Trees.ID = Seeds.Parent_Tree) INNER JOIN Trays ON Seeds.ID = Trays.Seeds) INNER JOIN Germinations ON Trays.ID = Germinations.Tray) ON Species.ID = Trees.Species

GROUP BY Trees.ID, Trees.Common_Name, Species.Latin_Name;

Answer 1

You could try using three queries...

1. A query to show the number of trays
2. A query to show the number of seeds germinated
3. A query to combine those two queries

Answer 2

It may be illustrative to first run a Select query, with only the various IDs on it.

Unfortunately you called them all "ID", so you would have to enter aliases:

TreeID: ID
Trees

Etc.

Another concern is that I do not see 1:M symbols on your relations. Do you in fact have a Relations diagram with the relations on it, and right-click each relation and in the dialog check the box to enforce them.

Answer 3

I suspect if you removed the Group By and run as a SELECT query, you will find the query returns 4 records. Probably because there are 4 seed records for the matching seed, so each Tray record would appear 4 times in the query.

Answer 4

Hi Maier,

Have you seen the above replies? If you have any updates, please feel free and come back to share with us.

Regards,

George

Answer 5

This might seem pedantic but... Botanic Nomenclature is not necessarily Latin. Botanic names are just as often derived from Greek. You could use Species_Name but then that would be confusing because binomial nomenclature is "Genus species" (note: Genus name is capitalised, species name is not. eg Sequoia sempervirens) so unless you want two columns - one for Genus Name and one for Species Name then Botanic_Name may be more suitable and this will also allow for named varieties.

Good column naming conventions save time.

As for Primary Key and Foreign Keys - there are really only two good ways to go.

1. Use the same name for both the PK and FK eg Trees.TreeID and Seeds.TreeID

or

2. use PK and FK instead of ID eg: Trees.TreePK and Seeds.TreeFK

either clearly show the relationship

even better - use an underscore to separate the entity name from the suffix eg Tree_ID

Consistency is the key to good applications.

consitently use a suffix (or a prefix) that describes the data type. eg Collected_Date. This makes coding much easier. Less need to go back to check on the data dictionary. To another developer, Collected and Purchased could just as easlity be boolean as date. However you are consistently using the past tense for dates which is good. When coding, having to guess whether the colum name is Collection_Date or Collected_Date is a pain. Here you have used Date as a prefix which is fine if you can be consistent. But that would mean using column names like ID_Tree and Name_Botanic which don't sit well so probably better to use suffixes to describe the data type. Just be consistent.

Don't use plurals for table names. Use a tbl prefix. Yeah I know, the norm for collections is to use plurals but then english language is way too inconsistent -it's not always the entity name & 's'. Species is just one example. Entity is, itself, another.

Later in your career as a developer, you will find that having a PK name that is the table name with _ID or _PK on the end (less the tbl prefix) will save you time. For example, when you build a new application, the best approach is to build one form as a prototype then clone it to use for other entities. Way easier to do if you don't need to deal with the inconsistencies arising from plurals. 

As Tom suggests, you should review your relationships. But who am I to talk on the subject of relationships?

Now getting back to your question: given the Tray > Germination relationship

- you will need to do a sub query for germinations that counts Germination ID grouped on Tray ID. That sub query will give you one record for each Tray with a count of seedlings germinated from that tray. Let's call that Germination_Count.

- put that sub query back in the query you have instead of the Germinations table and you have a one to one relationship from Trays to the sub query.

You can then Count Trays and Sum Germination_Count to get what you want. which is kinda what theDBGuy suggested.

You probably don't want an inner join on Trays to the sub query if you want to include a count of trays without germinations.

But quite why you would have an individual record for each seedling is another story.