adding two values WHERE parent_ID is the same

[dago]

Hi,

In MsAccess 97 I have a database with basically two tables: "parents" and "children"
Each parent has a unique ID number in the "parents" table. Each child has the same in the "Children" table.
The 2 tables are related one (parents) to many (children).

Parents have to pay schoolfees for their children. Fees vary according to income, etc. so there are many different school fees, which are put into the "Children" table into each child's record. One parent_id (in the children's table) may have two or more different fees.

So far my introduction.

Now here is my question: so the parents of course have to pay fees for all their children. In a query I want to see all separate fees, but also the added fees for those parents with more than one child!
The idea is to take these data into a billing system.

Who helps me with the code? All help is appreciated!

I started out like this:SELECT Sum(Children.month_fee) AS SumOfMonthfee
FROM Children;

Dago

 

[SydneyGeek]

Hi, this is best viewed in a form where the Parent form is the main form, and the Child form is the subform, with the 2 forms connected through ParentID. Each Parent record will then show all Child records for that parent.
To get an idea of how it connects, look at the Northwind sample database and check out any of the forms that also contain subforms.
One potential problem with this setup: Each parent in a 2-parent family will be shown the fees for the children and this could cause duplication.
Maybe you need a Family table connected to Child, one-to-many. In the Family table you can have Father, Mother (or Guardian1 and Guardian2) fields. That way you will only generate one set of fees for each family. Under those conditions, this query would work:

SELECT Families.FamilyName, Count(Children.ChildName) AS CountOfChildName, Sum(Children.Fees) AS SumOfFees
FROM Families INNER JOIN Children ON Families.FamilyID = Children.FamilyID
GROUP BY Families.FamilyName;

HTH
Denis

 

[dago]

Thanks, Denis,
I adapted it but cannot get it working, though.
http://members.chello.nl/d.m.de.werd/tabel.jpg shows part of the Children's table.
As you can see, this small part shows already three double parent ID's
(Sorry, the table is Dutch: I'm from the Netherlands)
Now I want a query to add the sum of fees per parent (ID). So it should show the added total fee for the Werd family, Manning family, Gunsing family and the other families as well, of couse...

Looking forward to any help.

Thanks, Dago

 

[SydneyGeek]

Hi, you could try grouping on OuderID or Kind_Anaam and summing the Maandbedrag.
Build a new query in Design view, taking these fields:
OuderID, Kind_Anaam, Maandbedrag
Click the Totals button in the toolbar (the Sigma button). new row appears with Group By under each field. Leave Group By for OuderID, and change Kind_Anaam to Count and Maandbedrag to Sum

That should do it
Denis

 

[dago]

Thanks, Denis

It works now! Great!

Dago