Query calculating problem

[nia]

Hi there,
I have a main form called 'Project', within the form 'Project' I have inserted a continous subform called 'SubProject'. Within 'Project' is a numeric field called 'Headcount' and within the subform 'Subproject' is also a numeric field called 'SubProjectHeadcount'. In my query i want to be able to sum the total headcount for the project, i.e the Headcount field plus the subProjectHeadcount field, but the problem that arises is that if there is more than one subproject than it sums the 'Headcount' field in the 'Project' form twice! i.e if the 'Headcount' field has the value 10, and in the subform there are two subprojects whose 'SubProjectHeadcount' values are 5 and 7, then my query should show the total headcount as being 22, but it returns a value of 32, because it adds the 10 from the 'Profect' form twice. How can I overcome this problem?? Sorry if this was a bit long-winded!!
Thanks for any comments or answers

Cheers
nia

 

[sonny]

You may have to do this in 2 or more queries? Can you post a sample db so I can give it a go?

 

[pbaldy]

Since you've asked for a query, the table structure is more relevant than the forms, but assuming a similar structure (and limiting to the project on the form):

SELECT Project.ProjectNum, Max(Project.Headcount) AS MainHeadcount, Sum(SubProject.Headcount) AS SubHeadcount, [MainHeadcount]+[SubHeadcount] AS TotalHeadcount
FROM Project INNER JOIN SubProject ON Project.ProjectNum = SubProject.ProjectNum
GROUP BY Project.ProjectNum
HAVING Project.ProjectNum=[Forms]![Project]![txtProjectNum];

I put your names in after testing on something of mine, so hopefully I didn't goof up in renaming.

 

[nia]

Paul, thanks a million for your help and code. It worked a treat.
Everyone else thanks for your comments.

Cheers

nia