By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

count number of rows related to another table

P: 4
Hai
I need some help, for the record I am new to VB and access
I have two tables (parent) and (child) . I want to count the number of children for each parent. I want to use date of birth and not ChildID. The number must be returned to table (parent) field amount

This must be very simple
Aug 22 '11 #1
Share this Question
Share on Google+
9 Replies


100+
P: 131
Mh!
have you ever dreaming while walking? Never!!!
You have to sleep I think.
What you are looking for is impossible!!!
Aug 22 '11 #2

Rabbit
Expert Mod 10K+
P: 12,366
I don't know what you mean by count the date of birth. You don't have to count any one field. You'll be counting records and grouping by the parent foreign key field in the child table.

I also don't know what you mean by return it to the parent table. I assume you're talking about an update query. But there's no need to do that. You shouldn't store that data in the table. It should be calculated on an as needed basis.

Also, your data structure is flawed. What happens when a child becomes a parent themself? You end up duplicating data.
Aug 22 '11 #3

NeoPa
Expert Mod 15k+
P: 31,494
Adam:
This must be very simple
It doesn't sound like you'd know to be frank.

I have to say that your design needs some serious consideration (See Rabbit's post). Anything implemented within such a scenario is unlikely to be remotely simple.
Aug 23 '11 #4

P: 4
Thank you all for the feedback,
It seems that I asked for the impossible. For Mr key, I dream and walk and sleep-dream-walk ;)
Can someone explain to me (if possible) how to count the numbers of birth dates in the (child) table for each (parent)? Your hints are more than welcome

This must be very difficult :)
Aug 23 '11 #5

NeoPa
Expert Mod 15k+
P: 31,494
Adam:
This must be very difficult :)
I have to admit I enjoyed that. A real ROFL moment. I'm still not sure I can rely on your appreciation of the difficulty level, but a laugh is always a laugh. Spot on!

Adam:
Can someone explain to me (if possible) how to count the numbers of birth dates in the (child) table for each (parent)?
This is more complicated than it sounds (You just knew I was going to say that didn't you?), but possibly not in the way you might think. Because the design of the database is :
  1. Somewhat unnatural. As Rabbit mentioned you don't find humans naturally separated by parent/child relationships - rather you have humans with various attributes and various relationships to (some) other humans.
  2. Unexplained here. You don't provide much in the way of detail of what you're working with.
On top of that, the wording of the question is somewhat ambiguous. You say you want to count the dates, but do you mean the number of records associated with each parent? Possibly the number of different dates within that group (Such that twins would count only once).

Fundamentally, the answer would involve a GROUP BY query using the Count() function within the SELECT clause. More detail than that would rely on information we don't yet have.
Aug 23 '11 #6

P: 4
Thanks NeoPa,

Hahah,

Ok I admit the data base (which I inherited) is unnatural and it is a complex problem. so m I, a complex unnatural problem.

Thanks, I'll have to figure something else out. I'm not a coding wiz (hahahah) and have no idea how to solve it but …. On one day there will be a (work around) solution.
Aug 23 '11 #7

patjones
Expert 100+
P: 931
What everyone is getting at is that you're not giving us any information in your question, and thus it's impossible to answer the question. If you can tell us what your table structure is exactly (table names, what the columns are, and how the tables are related) then we'll probably be able to give you a more focused response.

Pat
Aug 23 '11 #8

P: 4
Hai zepphead80,

I have two tables (tbparent) and (tbchild).
(Tbparent) contains [parentID], [parentID] ,[fname] ,[sname]
(Tbchild) contains [childID], [fname], [Dbirth] ,[parentID]

Question

How to count the number [Dbirth] for each [parentID]?

I hope this is clear
Thanks
Aug 24 '11 #9

NeoPa
Expert Mod 15k+
P: 31,494
I would suggest the following. It's lucky [tbparent] isn't required as it seems to have two fields with the same name.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [parentID]
  2.          Count([Dbirth]) AS [NumDB]
  3. FROM     [tbchild]
  4. GROUP BY [parentID]
PS. You didn't answer the questions I posed in post #6, so the SQL still doesn't make a lot of sense (but still works in a very basic way). Technically this answers your question but it's unlikely to be perfect I suspect, because I'm guessing the question is a reflection of your confusion about what you actually want. Counting a particular field (rather than the records containing those fields) is rarely what's required. Generally the records are what need counting (in which case you'd use Count(*) instead).
Aug 24 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.