Jump to content

Homeless Shelter Resident reports: Family size

This topic is 6566 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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


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.


Link to comment
Share on other sites

This topic is 6566 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.