I need to merge two fields together to create a unique record id

[Guest]

I am mining a database and need to combine two fields on a table inorder to
build a query that would properly related data. For example, to find a
person in this database the person has a familyid and a personid. familyid
is unique to a family and the personid refers to a particular person in that
family. Jack and Joe Smith, two brothers live at the same address. This
smith family has a familyid of 100213, Jack's personid is 0001 and Joe's is
0002.

I will need to do this an another table for the same fields inorder to
relate the data in the two tables.

Thank you,

Jack

 

[Ken Snell \(MVP\)]

I'm not understanding your question. Do you want to write a query that will
allow you to find a record that has the desired FamilyID and PersonID
values? Or do you want to set up a relationship between tables using these
two fields? Please provide more details.

 

[John Vinson]

On Wed, 22 Nov 2006 18:22:01 -0800, Jack Fleet <Jack

I am mining a database and need to combine two fields on a table inorder to
build a query that would properly related data. For example, to find a
person in this database the person has a familyid and a personid. familyid
is unique to a family and the personid refers to a particular person in that
family. Jack and Joe Smith, two brothers live at the same address. This
smith family has a familyid of 100213, Jack's personid is 0001 and Joe's is
0002.

I will need to do this an another table for the same fields inorder to
relate the data in the two tables.

Thank you,

Jack

It is not necessary to combine the two fields in order to get a join.
You can join FamilyID to FamilyID, PersonID to PersonID; in fact you
can use up to *TEN* fields in a Join.

John W. Vinson[MVP]

 

[Guest]

Ken,

Thank you. A multiple join would work just fine, like you and John
mentioned. Although I am not sure I know how to do that can you point me in
a direction?

Jack

 

[Guest]

When creating your query, in design view, drag your mouse from familyid in
table1 to familyid in table2 and then do the same with personid.

If you are in sql view the query will look something like...

SELECT <the columns you want>
FROM table1 INNER JOIN table2
ON table1.familyid = table2.familyid
AND table1.personid = table1.personid

Hope this helps
Regards

Andy Hull

 

[Guest]

Thanks, I give that a try. Sounds simple enough.

Jack