Combine several columns from one table to one column in another table?

[nathan2314]

:hi:
Hi All,
I'm very new at Access and am building a database with several tables.
I have a 'family' table that has all the family members for each PERSON in the database. The 'family' table uses a lookup up table to allow the user to select the 'family member type' when inputing a family member for the PERSON in the database.
So now I have another table where I need to input important people that know each PERSON and their spouse. So I have a Person_ID that identifies the PERSON being looked at (This is also in the 'family' table) in the new table. But now I would like to add the PERSON's spouse name in ONE text field (say called 'SpouseName') by going back to the 'family' table and matching the PERSON_ID and 'family member type' as spouse (spouse is 'family member type'=3 in the lookup table being used in the 'family' table) and then putting this in the 'SpouseName' column of the new table. But the names in the family table are in three different columns ('firstname' middlename' and 'lastname').
How can I do this. I've been fooling around with the 'Row Souce' field in the 'Lookup' tab of the new table but not getting it to work??? :doh:

Appreciate any Help!!

[OBP]

In general it is not good practice to duplicate data in a second Table.
I can however understand why you wish to, what you should do is just refer to the Name in the Family Table using it's ID.
If you really want to combine the Names in to one name the best place to do it is in a Query.
You can use a new Column and type in fullname: [firstname] & " " & [middlename] & " " & [lastname]

[nathan2314]

Hi
Thanks for the reply. Sorry I'm not sure I quite get how to do what you indicated.:dunno The Family Table does have a primary key called FamilyMemberID but it can have several values per PERSON in the database as a person can have several enteries for brothers/sisters/aunts etc etc. The Family Table does have the associated Person_ID as a foreign key. So I was thinking I could use this to match the Person_ID in the new table (Also a foreign key) and then pull FamilyMemberType=3 (this is the value in the lookup table for Spouse) in the Family Table to get the FirstName, LastName, MiddleName. The Family Table is where the actual data is inputed as this SpouseName in the new table with just be automatically populated based on the data in the Family Table if I can get it to work. I understand what you said about You can use a new Column and type in fullname: [firstname] & " " & [middlename] & " " & [lastname] but how do I get the record I want in the family table to apply this to the column called SpouseName in the new table.
Would I do somekind of SQL logic in the lookup up tab or do all of it in a separate query. And if I do it in a query how do I get that into the field SpouseName in the new table?
Sorry I'm probably asking basic easy stuff but I'm kinda a dope:dunno at Access but trying to learn...
Appreciate your help!!

[OBP]

As I said the only thing that should be stored in the new table is the Person's ID and then you can pick up the name in a Query or in a Combo.
In the Form that you use for your new Table I assume that you would use a Combo to select the people to go in it, (Or are you going to just use a query?)
If you are going to use a Combo box you can add the Fullname column to the Combo's row source SQL query. when the person is selected in the combo you have some very simple VBA in the "After Update" event procedure of the combo like -

me.spousename = me.combo.column(4)

coulmn 4 assumes that the combo has ID, lastname, firstname, middelname and fullname as it's columns.
Alternatively you can just use a Combo with ID and Fullname and just store the ID in the spousename, but have it's coulmn width set to 0cm, So that you storte the ID but see the fullname.