If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query to update familyID by last name
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 Structu 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. |
#2
|
|||
|
|||
query to update familyID by last name
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; -- Build a little, test a little. "Kathy R." wrote: 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 Structu 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. . |
#3
|
|||
|
|||
query to update familyID by last name
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. wrote: 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 Structu 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. |
#4
|
|||
|
|||
query to update familyID by last name
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. KARL DEWEY wrote: 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; |
#5
|
|||
|
|||
query to update familyID by last name
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. John Spencer wrote: 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. wrote: 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 Structu 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. |
Thread Tools | |
Display Modes | |
|
|