Sum results of a calculated control 1

[THWatson]

Using Access 2000

A report groups by FamilyID. The first line shows the primary adults in the family, which have a RelationshipID of 1 and 2. The second line shows children, which have a RelationshipID of 3. The third line shows additional adults in the family, if there are any, and their RelationshipID is 4 or greater.

Records in a typical report would display as follows:
ADAMS, John & Rosalind
Freddie, Susan (children)
Sally Goode

BROWN, Jim & Mabel
Tanya, Tim (children)
Bruce Frobisher, Clara Friendly (other Adults)

This 3rd line is brought in by

=Concat("qryRelationship4Plus","FullName","FamilyID = " & [FamilyID])

This Concat is a function from a module that pulls records together by FamilyID.

By constructing a small Totals Query, I can get a count of these other adults, based on the FamilyID. So the first record I show would have a count of 1, and the second a count of 2.

How do I get a total of 3 in the report footer?

1. I have tried Sum(Abs).
2. I have tried putting a running sum text box in the group footer and then referencing it in the report footer. In this case, I get a value of 2, whereas it should be a value of 3.

Any shoves in the right direction would be appreciated.

Tom

 

[THWatson]

I found a method. There may be better methods but this works.

1. Make a totals query to give a total by FamilyID

2. In a Group Footer, put a text box, txtCountOthers, with the following...
=DSum("CountF","qryCountOthers","FamilyID = " & [FamilyID])

3. In the report footer, put a text box with the following...
=Nz(txtCountOthers)

Tom

 

[dhookom]

Can't you just join qryCountOthers into the report's record source? Join the FamilyID fields and stick CountF in the report's field list. You can then easily and more efficiently sum the values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

Duane
Yep. For sure.

Then use a text box in the report footer with the following as its control source...
=Sum(Abs([CountF]))

Wish I had thought of that several hours ago. This is a many-to-many setup which creates some complications. I fooled around, trying to get that sum to work and finally made a post when I couldn't seem to get it.

Thanks for a great solution!

I trust your grandson is doing wonderfully well. We had all of our gang (17 in total, with children and grandchildren) together at Christmas for the first time in 19 years.

All the best in 2007.

Tom

 

[dhookom]

Thanks for the well wishes. Jackson is the only kid in his generation on both sides of our families so life is good for a 3 yr old at Christmas.

Glad to hear the field in the record source worked. It should be much more efficient than DSum().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

[off topic]
Lucky Jackson! My dad has 12 granddaughters (ages 3 - 17) and 1 grandson (also age 3)......who do you think is the crown prince in our family???

While we weren't together for Christmas, most of us went with Dad & Jane to celebrate their 20 year anniversary...on a cruise the week after Thanksgiving (Jamaica, Grand Cayman & Cozumel). For Christmas our 4 family members looked at the pictures and said "What a great time we had!"

Can't afford to do Christmas AND a cruise!

Les[/off topic]

 

[THWatson]

[reply to off topic]
Thanks for your great story, Les.

My 7 grandchildren range in age from 21 (4th year of University) down to 2. Guess where much of the focus goes when everybody is together - yep, to the youngest, who is, at least at the moment, the Crown Princess.

It's an interesting thing in families. I am a retired minister so over the years have conducted many funerals. At the reception time afterwards, somebody in the family brings in the newest arrival in the family. And the focus is almost immediately from the death experienced to the "new life" eminently evident in the baby...life goes on.

Tom [/reply to off topic)

 

[MallardVHS]

Dear THWAtson:

A goodly while back in college I had to read Robert GFrost.

One thread of his delt with a boy, his hand cut off, and the after behaviour of the else. His line about 'they weren't the one what's dead' still rings.

don

[green]"They say that Fret A Stair was afraid of flights." I recommended a 12-step program.[/green]