I have a database that tracks residents of a homeless shelter. Each individual has a unique ID (case number) as follows: 01.100. Also, each family member has a family case number: F01.100. The head of the family would be 01.100 with the members being 01.101, 01.102...All the members would have the family case number F01.100.As you can imagine, a homeless shelter has many reports; local, county, state and federal government as well as non-profits that help fund us. One of the numbers I have to generate is the number of families with 2 members, 3 members, 4 members, 5 members and 6+ members. Maybe I've been working on this too long but I'm drawing a blank here. Your help would be appreciated and very helpful.

Hi pjmo,

You need to set up a self-join relationship on the part of the ID number that uniquely identifies each family. If each person has an ID like 01.100, 01.101, etc., then call the numbers to the left of the point the FamilyID. Then set up a relationship called Families where FamilyID=FamilyID.

To do this you'll need to create a calc filed where the numbers to the left of the point are separated from the rest of the ID in a single field. There are several ways you might pull this off. Assume you have the ID number in the format 01.001. To return the number to the left of the decimal point you could use

Left(ID, Position(ID, ".",1,1)-1)

So once you've got the family part of the ID stripped off and the self-join relationship set up, counting the number of family members is pretty straight forward. Something like

CountMembers = count(Familes::FamilyID)

will give you a count of how many family members are in a family. Everyone in family 01 will have CountMembers=3 if there are 3 members of family 01.

Then to find out how many 3-member families you have, create a found set that has 1 member of each family in it. Since according to you scheme, each family head has the number **.100, you can find all the cases where the number to the right of the point =100. This will give you a set of family heads. Search your ID field for

==*.100

This will return all of the family heads. Then search this set again for CountMembers=3, using the constrain search option. The number of records left in the found set is the number of 3-member families you have.

Best luck. Let me know if you have questions.

Dan

Dan, Thank you for the really quick reply - I'll let you know how it works out

Peter

Dan,

It is working - Thank you very much. This is for annual reports for our funding and now I can submit the information.

Peter

Great! I work for Habitat for Humanity myself, so I'm always happy to help the cause of decent shelter. And I know what grant reporting can be like too. Yuck.

Cheers,

Dan