calculating Count Records from related Table?

[ally82]

Hi there, I do apologize if this is answered elsewhere but an hour of googling and searching this forum came up empty...

This is what I'm hoping to do:

I would like to set up a query or calculation in a data table that is just a record count for a related item PER each individual main entry in the main table.

Specifically....

Main Table: Data Table
Related Table: Fragments
One-to-Many Key: Element ID

What I'd like to do is create a "lookup field" in Data Table under a field called Total Fragments.
I successfully did this but I only got so far as totaling ALL entries in Table "Fragments"!...

Being not a SQL expert, I don't know how to write a command that goes something like this:

For main table "Data Table", for entry "Element ID = 1", in the field called "Total Fragments", I want to perform a query that reads:
from table "Data Table", count records where the "Element ID" in Table "Fragments" is the same as "Element ID" in Table "Data Table".

so the spreadsheet would look like, in table Data Table:
Element ID: 1
Total Fragments: 45 (ie there are 45 entries in table Fragments with the Element ID of 1)
--
Element ID: 2
Total Fragments: 23 (ie there are 23 entries in table Fragments with the Element ID of 2)

etc.

basically I figured I'd need to use a "CountIf" type statement but the lovely Access wizard doesn't have those comments built-in like Excel does!

and once again I want to display this count in the main table Data Table. it SHOULD work, I think, with a "lookup" command (using Design mode).

but really is this even possible?

thanks a billion....

[new learner]

In case, you are planning to store the count in the Main Table, it is not advisable.
Have you tried using an aggregate query on the Fragments table, grouping on the ElementID & using Count on another field.

Thanks

[ally82]

In case, you are planning to store the count in the Main Table, it is not advisable.
Have you tried using an aggregate query on the Fragments table, grouping on the ElementID & using Count on another field.

I don't think putting a "Total Fragments" field in the Fragments table would make much sense because then entries would look like:
"Fragment ID 23, Element ID 9, Total Fragments 53; Fragment ID 24, Element ID 9, Total Fragments 53"
(it just doesn't make sense to do it that way).

It would be fine if all I could do was a query that spat back out:
"Element 1 has 19 entries. Element 2 has 12 entries...."
But wouldn't I still need a CountIf statement?
I just wasn't sure how to do this.

[meratigoerr]

For main table "Data Table", for entry "Element ID = 1", in the field called "Total Fragments", I want to perform a query that reads:
from table "Data Table", count records where the "Element ID" in Table "Fragments" is the same as "Element ID" in Table "Data Table".

so the spreadsheet would look like, in table Data Table:
Element ID: 1
Total Fragments: 45 (ie there are 45 entries in table Fragments with the Element ID of 1)
--
Element ID: 2
Total Fragments: 23 (ie there are 23 entries in table Fragments with the Element ID of 2)

open a new query in design view. Add both tables. Create a relationship between [Data Table].[ElementID] and [Fragments].[ElementID]
Select your ElementID field, (either table, doesn't really matter.), select primary key from table "Fragments"
turn on totals.
Group by ElementID field
Count records by primary key field.

This will give you a table with each seperate [Element ID] field and how many records exists in your tabe "Fragments" table. should look something like.

ElementID : CountofPrimaryID
1 : 45
2 : 23

My Two cents:

and once again I want to display this count in the main table Data Table.

I agree with New Learner, putting this information into a table is not advisable and WILL cause problems for you down the road. Stick the query and just call it everytime you need this information.

[ally82]

well that did it! thanks, I think what I needed was the instructions to be written in Human and not SQL

I already had the relationship in there which made it easy. Now I can make a lovely table that shows all sorts of useful info.

Many, many thanks!!!