Combining Records Question.

[sabpeter22]

I have data like this:

Record 1:
Doe, Jane Doe, 1 main street
Record 2:
Doe, John Doe, 1 Main Street

What I what to do is the following:
Doe, Jane Doe, John Doe, 1 Main Street

How do I do this.

 

[vanderghast]

SELECT a.fullName & ( ", " + b.fullName)
FROM tableName AS a LEFT JOIN tableName As b
ON a.address = b.address
AND a.fullName < b.fullName


should do, but it may well list the man first, instead of the woman first.


It is also limited to a MAX of two people with the same address. Well, if
you have a third one, all the possible pairs will be generated.

The < is used to break reflexivity ( not John Doe, John Doe) and symetry
( if there is a Jane Doe, John Doe, there will be NO John Doe, Jane Doe)


Vanderghast, Access MVP

 

[Dale Fye]

What do you want to do for records that look like:

Doe, Jane Doe, 1 Main Street
Smith, John Smith, 1 Main Street

You need a function that will concatenate records based on some criteria.
It might look something like the one below. This function accepts the name
of a field, the name of table, and several optional parameters (delimiter,
wrapper, and criteria), and returns a string of values from the specified
field in the specified table. The way you use it might be something like:

Select LastName,
Address,
fnConcat("FullName", "yourTable",,,"[LastName] = """ &
yourTable.LastName & """ AND [Address] = """ & yourTable.Address & """) as
Occupants
FROM yourTable
GROUP BY LastName, Address

Public Function fnConcat(FieldName As String, TableName As String, _
Optional Delimeter As String = ",", _
Optional Wrapper As String = "", _
Optional Criteria As Variant = Null) As String

Dim strSQL As String
Dim rs As DAO.Recordset
Dim varConcat As Variant

strSQL = "SELECT [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

varConcat = Null
While Not rs.EOF
If Not IsNullOrBlank(rs(0)) Then
varConcat = (varConcat + Delimeter) & (Wrapper + rs(0) + Wrapper)
End If
rs.MoveNext
Wend

ConcatExit:
fnConcat = Nz(varConcat, "")
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function

ConcatError:
debug.print "Something failed in the concatenation function"
debug.print err.number, err.description
debug.print
Resume ConcatExit

End Function

 

[sabpeter22]

What I'm looking to do is an easy way I can for example:

Take the records sample from below.

For example:

Take the multiple records., and have multiple fields in a table take the
records example: Then delete the duplicate record.

LastName Name 1 Name 2 Address
Doe John Doe Jane Doe 1 Main Street

What do you want to do for records that look like:

Doe, Jane Doe, 1 Main Street
Smith, John Smith, 1 Main Street

You need a function that will concatenate records based on some criteria.
It might look something like the one below. This function accepts the name
of a field, the name of table, and several optional parameters (delimiter,
wrapper, and criteria), and returns a string of values from the specified
field in the specified table. The way you use it might be something like:

Select LastName,
Address,
fnConcat("FullName", "yourTable",,,"[LastName] = """ &
yourTable.LastName & """ AND [Address] = """ & yourTable.Address & """) as
Occupants
FROM yourTable
GROUP BY LastName, Address

Public Function fnConcat(FieldName As String, TableName As String, _
Optional Delimeter As String = ",", _
Optional Wrapper As String = "", _
Optional Criteria As Variant = Null) As String

Dim strSQL As String
Dim rs As DAO.Recordset
Dim varConcat As Variant

strSQL = "SELECT [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

varConcat = Null
While Not rs.EOF
If Not IsNullOrBlank(rs(0)) Then
varConcat = (varConcat + Delimeter) & (Wrapper + rs(0) + Wrapper)
End If
rs.MoveNext
Wend

ConcatExit:
fnConcat = Nz(varConcat, "")
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function

ConcatError:
debug.print "Something failed in the concatenation function"
debug.print err.number, err.description
debug.print
Resume ConcatExit

End Function
----
HTH
Dale

I have data like this:

Record 1:
Doe, Jane Doe, 1 main street
Record 2:
Doe, John Doe, 1 Main Street

What I what to do is the following:
Doe, Jane Doe, John Doe, 1 Main Street

How do I do this.

 

[ff]

"(e-mail address removed)" <[email protected]>
escreveu na mensagem

What I'm looking to do is an easy way I can for example:

Take the records sample from below.

For example:

Take the multiple records., and have multiple fields in a table take the
records example: Then delete the duplicate record.

LastName Name 1 Name 2 Address
Doe John Doe Jane Doe 1 Main Street

What do you want to do for records that look like:

Doe, Jane Doe, 1 Main Street
Smith, John Smith, 1 Main Street

You need a function that will concatenate records based on some criteria.
It might look something like the one below. This function accepts the
name
of a field, the name of table, and several optional parameters
(delimiter,
wrapper, and criteria), and returns a string of values from the specified
field in the specified table. The way you use it might be something
like:

Select LastName,
Address,
fnConcat("FullName", "yourTable",,,"[LastName] = """ &
yourTable.LastName & """ AND [Address] = """ & yourTable.Address & """)
as
Occupants
FROM yourTable
GROUP BY LastName, Address

Public Function fnConcat(FieldName As String, TableName As String, _
Optional Delimeter As String = ",", _
Optional Wrapper As String = "", _
Optional Criteria As Variant = Null) As String

Dim strSQL As String
Dim rs As DAO.Recordset
Dim varConcat As Variant

strSQL = "SELECT [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

varConcat = Null
While Not rs.EOF
If Not IsNullOrBlank(rs(0)) Then
varConcat = (varConcat + Delimeter) & (Wrapper + rs(0) +
Wrapper)
End If
rs.MoveNext
Wend

ConcatExit:
fnConcat = Nz(varConcat, "")
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function

ConcatError:
debug.print "Something failed in the concatenation function"
debug.print err.number, err.description
debug.print
Resume ConcatExit

End Function
----
HTH
Dale

I have data like this:

Record 1:
Doe, Jane Doe, 1 main street
Record 2:
Doe, John Doe, 1 Main Street

What I what to do is the following:
Doe, Jane Doe, John Doe, 1 Main Street

How do I do this.