-1

I am using access 2013. I created a database of family's with multiple children. The first table has general info of every family like phone numbers address etc.And an ID number that relates to another table that has the list of children with their info like age school etc. t want to get the total number of children to a field in the first table with the family names. i appreciate all ideas!

dov
  • 1
  • 2

1 Answers1

0

Have no Access at hand but try this bit of T-SQL: SELECT Family.Name, Count(*) FROM Children INNER JOIN Family GROUP BY Family.Id

This will give you number of children per family ID. See this article for your reference: https://support.office.com/en-us/article/GROUP-BY-Clause-84eeb766-25d2-4aa1-8eea-002bb65ef3a0

Updating Family table with number of children is normally a bad practice and called "denormalization". This normally justified when you have millions-billions of records and running JOIN is more time consuming then running into an issue of incorrect count of child elements. In MSSQL this can be solved with use of triggers, which you obviously not going to have in Access. So I advise you against the approach your question starts with.

zmechanic
  • 1,842
  • 21
  • 27
  • *"In MSSQL this can be solved with use of triggers, which you obviously not going to have in Access."* - Incorrect. In Access 2010 and later an event-driven Data Macro (which is very similar to a trigger) could do it. – Gord Thompson May 04 '16 at 14:00
  • Thanks for your input, I tried using event- driven Macro but I could not figure out how to crate the specific field i asked about above. – dov May 04 '16 at 16:01
  • @dov - See [this answer](http://stackoverflow.com/a/30076587/2144390) to a very similar question. The only difference is that instead of creating a concatenated string you would be incrementing a counter and updating the parent record with the final count of its child records. – Gord Thompson May 08 '16 at 00:19