count number of rows related to another table

[Adam Adams]

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

[Mr Key]

Mh!
have you ever dreaming while walking? Never!!!
You have to sleep I think.
What you are looking for is impossible!!!

[Rabbit]

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.

[NeoPa]

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.

[Adam Adams]

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 :)

[NeoPa]

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.

[Adam Adams]

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.

[patjones]

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

[Adam Adams]

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

[NeoPa]

I would suggest the following. It's lucky [tbparent] isn't required as it seems to have two fields with the same name.

SELECT   [parentID]
         Count([Dbirth]) AS [NumDB]
FROM     [tbchild]
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).