#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    Question calculating Count Records from related Table?


    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....
  2. #2
  3. Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    May 2008
    Posts
    1,036
    Rep Power
    610
    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
    - My mind doesn't wander, most of the times, it leaves. - S R
    - Whether U agree or disagree, pls add to my rep, either ways I got ur brain thinking.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by 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.
    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.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Originally Posted by ally82
    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.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    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!!!

Similar Threads

  1. Count records in a different table
    By kila2 in forum ASP Development
    Replies: 9
    Last Post: April 11th, 2006, 02:13 PM
  2. Count Records in a Table
    By LozWare in forum ASP Development
    Replies: 7
    Last Post: January 15th, 2006, 09:16 AM
  3. Count Records in a Table
    By crackster in forum ASP Development
    Replies: 2
    Last Post: October 28th, 2005, 04:53 AM
  4. Count records in table - VBA
    By canadian in forum Microsoft Access Help
    Replies: 7
    Last Post: March 22nd, 2005, 02:25 PM
  5. Count records in a table and update to another table
    By Awinters in forum Microsoft Access Help
    Replies: 2
    Last Post: February 25th, 2005, 10:13 AM

IMN logo majestic logo threadwatch logo seochat tools logo