query to update familyID by last name

[Kathy R.]

I have a set of about 500 names that I need to break down into family
groups. The data was imported from an old database and this set of
names does not have any family association. Currently, for convenience
sake, they are all in the same "family," ie their FamID is all the same.

Table Structure:
tblFamily
FamID (primary key)
FamLastName

tblIndividual
InID (primary key)
InFamID (foreign key)
FirstName
LastName

What I would like to do is to build a query, sorted on LastName where
all of the same last name would be dumped into a new FamID and have the
FamLastName in tblFamily and InFamID in tblIndividual updated. I've
looked at MIN and MAX, but that just gave me the first record (Abbott)
or the last record (Zyglocke).

Can this be done in a query, perhaps as a two-step process, or is it
more complicated than that? Your help would be very much appreciated!

By the way, I realize that all of the "Jones" may not belong in the same
family, but this will get me one step closer. The next step is to
compare them to the old, hand-written membership records.

Kathy R.

 

[KARL DEWEY]

Try this --
SELECT Q.LastName, (SELECT COUNT(*) FROM [tblIndividual] Q1
WHERE Q1.[LastName] < Q.[LastName])+1 AS InFamID
FROM tblIndividual AS Q
ORDER BY Q.LastName;

 

[John Spencer]

I would use two queries.

First query would populate tblFamily. FamID would be an autonumber field to
make this easy. If FamId is something else, then you need to describe what
field type and how the data for the field is constructed.

INSERT INTO tblFamily (famLastName)
SELECT DISTINCT FamLastName
FROM tblIndividual

Now, I would use an update query to populate the InFamId field

UPDATE tblIndividual INNER JOIN tblFamily
ON tblIndividual.FamLastName = tblFamily
SET tblIndividual.InFamID = [tblFamily].[FamID]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Kathy R.]

Thank you Karl. I tried this, but it didn't do quite what I wanted. It
updated all of the InFamID's in the tblIndividual, grouped by last name,
but I need them separated into family (by last name) groups in the
tblFamily and then have those FamId's updated in the tblIndividual.

I ended up using John's solution below, but I've tucked this away in my
"should I ever need to do this in the future, here's how" folder. I can
learn as much by something that doesn't work as something that does!

Kathy R.

 

[Kathy R.]

Thank you John! With a little tweaking (I had to build a select query
first to choose just those families that weren't already in family
groups) this worked like a charm. The one thing I was missing was the
word "DISTINCT." If I had that I could have looked in the help file for
how to do it. It's like having a word on the tip of your tongue, but
not being able to bring it to mind.

Kathy R.

I would use two queries.

First query would populate tblFamily. FamID would be an autonumber
field to make this easy. If FamId is something else, then you need to
describe what field type and how the data for the field is constructed.

INSERT INTO tblFamily (famLastName)
SELECT DISTINCT FamLastName
FROM tblIndividual

Now, I would use an update query to populate the InFamId field

UPDATE tblIndividual INNER JOIN tblFamily
ON tblIndividual.FamLastName = tblFamily
SET tblIndividual.InFamID = [tblFamily].[FamID]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a set of about 500 names that I need to break down into family
groups. The data was imported from an old database and this set of
names does not have any family association. Currently, for
convenience sake, they are all in the same "family," ie their FamID is
all the same.

Table Structure:
tblFamily
FamID (primary key)
FamLastName

tblIndividual
InID (primary key)
InFamID (foreign key)
FirstName
LastName

What I would like to do is to build a query, sorted on LastName where
all of the same last name would be dumped into a new FamID and have
the FamLastName in tblFamily and InFamID in tblIndividual updated.
I've looked at MIN and MAX, but that just gave me the first record
(Abbott) or the last record (Zyglocke).

Can this be done in a query, perhaps as a two-step process, or is it
more complicated than that? Your help would be very much appreciated!

By the way, I realize that all of the "Jones" may not belong in the
same family, but this will get me one step closer. The next step is
to compare them to the old, hand-written membership records.

Kathy R.