Query that counts first item and then counts within that item

[joehuggins]

We have a database that collects data related to corals, specifically how coral grows on rubble.
We have a table that collects data on each rubble piece. From this table, there is a one-to-many relationship with a coral table, each piece of coral has a unique record. Thus, one piece of rubble may have 0 or more corals at one time.


We then record a year later whether the unique coral has survived.
We want to write a query that counts the number of corals on each rubble piece and how many survived one year later.
Thanks!

[CJ_London]

From this table, there is a one-to-many relationship with a coral table, each piece of coral has a unique record.

Just to be clear, from your description a piece of coral can only be on one piece of rubble - it can't appear on another piece of rubble? i.e. you will only have one piece of rubble with say elkhorn coral on it.

But to answer your question based on what you have said, use an aggregate query on your coral table, grouping on the the rubbleFK field and count the rubbleFK for the number. As to how many survived after 12 months, all depends on what data you have to determine - presumably you have some fields to indicated when it first took up residence on the rubble and its state (alive/dead) after twelve months.

[joehuggins]

Thanks, CJ_London (and extra points for knowing your coral species!)!

Yes, an identified coral can only appear on a unique piece of rubble.

There is a field, yes/no, that indicates survival after 12 months. So what we want to be able to create is a datasheet that has:
RubbleID Original_Coral_Count Survival_Count

[June7]

Something like:

SELECT RubbleID, Count("*") AS Original_Coral_Count, Sum(Abs(Survived)) AS Survival_Count
FROM Rubble INNER JOIN Corals ON Rubble.RubbleID = Corals.RubbleID_FK
GROUP BY Rubble.RubbleID;

[joehuggins]

Something like:

SELECT RubbleID, Count("*") AS Original_Coral_Count, Sum(Abs(Survived)) AS Survival_Count
FROM Rubble INNER JOIN Corals ON Rubble.RubbleID = Corals.RubbleID_FK
GROUP BY Rubble.RubbleID;

Thanks June7!

The 2 tables I am working with are Data_Rubble and Data_Coral. The relevaqnt fields are: Data_Rubble.Rubble_ID and Data_Coral.Coral_ID and Data_Coral.Recruited and Data
_Coral.FK_Rubble_ID

Using the query above and converting it to my names this is what I come up with:

SELECT Data_Rubble.Rubble_ID, Count("*") AS Original_Coral_Count, Sum(Abs(Data_Coral.Recruited)) AS Survival_Count
FROM Rubble INNER JOIN Corals ON Data_Rubble.Rubble_ID = Data_Coral.FK_Rubble_ID
GROUP BY Data_Rubble.Rubble_ID;

I don't quite understand this bit: Count("*") Is something from my data fields supposed to replace the *

And is this portion correct?

Thanks

[joehuggins]

Thanks June7!

I was able to make this work given what you showed.

I really appreciate your help. Best!