Oops, forgot!
I need to assign every student a family id. Therefore if 3 students live at the same address, they all have the same familyid.
If one student lives at an address, he/she needs to be assigned a family id.
Thanks

Secondly, I'd counsel you to take a step back, and gather your rules / business requirements to some simpler layout. Can an address exist in the system if no one currently lives there? You imply a need for a location id (145 above) rather than family, as you use "family" in both related, and unrelated, terms.

Can a person belong to more than one location, and/or more than one family? Student John lives at one address with everyone else in his/her dormitory; and also lives with his/her parents across town on weekends. S/he receives postal mail at both locations.

How do you differentiate between related family and unrelated family? Family Smith are Student John's parents; they rent a room to another student, Smith. And of course, there are multiple Smiths (even John Smiths) at the dorm. Moreover, Smith might do a co-op out of state for one term, and upon return, move into an apartment.

IOW both the family surname, and the street address, cannot uniquely differentiate between data sets.

Hi Dvz:
(1) No address can exist in the system if no one currently lives there. It is a requirement.

(2) A person or persons(students) can belong to only one location. These are not students in a college setting and are all under 18, so there is no movement out to a dorm part of the time, etc. It doesn't matter if it is called FamilyID or LocationId.

(3) If 3 students live in the same household, 2 may be from a previous marriage (Smith) and 1 from the current marriage (Jones).

(4) You are right, the family surname & streetaddress cannot uniquely differentiate between data sets. I was hoping if we added the motheremail field to the mix, it might help. The motheremail is the same for all students in the same household, regardless of the students' last name.

Unfortunately, I get asked to do things that take a long time manually and was trying to find a way to automate.

I know the most basic way is to order the data by either address or motheremail, eyeball the student names, then assign the numbers manually, but I was hoping for a workaround.

(1) Yes, the motheremail may change, but it will attach to all the children at that one address.

(2) I don't care about the hyphen....just so ONE familyid is assigned to all the children at that one address.
Which means, the family id would be 4001 (or whatever works) for all children at the 121 Spruce Street address. It doesn't matter the format--i used the hyphen as an example. You mentioned locationid earlier...that kind of thing.