table update question

[JLB06LA]

I'm hoping you all can help me, because I've reached a bit of a road block on
how to accomplish the following:

First, I have two files. One is a list of households and addresses. The
second is a list of the people who live in those households. There is a
unique identifier for each household assigned to each person.

Second, I need to create one file with only one unique address per record.
However, if there are multiple names associated with an address, then each
name must populate a separate field. How do I get the 2nd, 3rd and 4th names
into separate fields in single record with the household?

Ex.
Record, Unique ID, Household Name, Full Name
1, 238, Smith Family, Bob Smith
2, 238, Smith Family, Jane Smith

Would become...

Record #, Unique ID, Household Name, Full Name 1, Full Name 2
1, 238, Smith Family, Bob Smith, Jane Smith

 

[pietlinden]

use fConcatChild from AccessWeb. What you described is exactly what it
does - well, except that you have to change the delimiter from
semi-colon to comma.

www.mvps.org/access

then just find fConcatChild in the modules section. Test it out in
Northwind first so you can get your head around it. It's much easier
to understand once you see it in action.

 

[JLB06LA]

I think I kind of get it, but unfortunately I'm not that much of a
programmer. Do I take the example code and put it in a module in Access? I
don't even know how to make it run.

I don't have Northwind.

 

[John Vinson]

I think I kind of get it, but unfortunately I'm not that much of a
programmer. Do I take the example code and put it in a module in Access? I
don't even know how to make it run.

ummm... did you *read* what was on Dev's site?

Yes, you would copy the code into a new or existing Module, select
Debug... Compile <my database>, and save.

Then you can use the function name in a Query to calculate the
concatenated field.

Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'

Substitute your fieldnames and tablenames as appropriate - since I
don't know what they are, I can't suggest them. Do you have a table of
Families with the family ID as the primary key???


John W. Vinson[MVP]