SQL/Access Search for ’Pairs’ of records

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   (I asked this question on a different Forum, but don't have a suitable answer <or else nobody understands what I'm talking about!>).

I have an Access database in which each record has personal details such as 'year', 'no.', 'first name' and 'surname'. Now, each person in the database has a matching partner (for simplicity, let's call the records 'husband' and 'wife'). Each pair is defined by a unique common record ('year', 'no').

I want to search a surname or firstname and then return that record PLUS the partner record whether or not that partner also satisfies the search criterion.

For example:

I will search by a name (eg., 'JONES') and the resulting table should look like this:

Surname     First_Name     Year     No.
JONES        Tom              1970    23
SMITH        Maggie          1970    23
BUSH          George          1956    65
JONES         Marion          1956    65

In other words, a search for 'JONES' returns 2 record pairs comprising 4 records.

My search query at the moment is as follows:

strSQL = "SELECT year, no, surname, first_name " _
& "FROM marriages " _
& "WHERE surname LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR first_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY surname;"

And the records are returned as follows:

<%
Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent
%>
<tr>
<td><%= rstSearch.Fields("year").Value %></td> 
<td><%= rstSearch.Fields("no").Value %></td> 
<td><%= rstSearch.Fields("surname").Value %></td> 
<td><%= rstSearch.Fields("first_name").Value %></td> 
</tr>

<%
rstSearch.MoveNext
Loop
%>

In the "JONES" example above, this code will only return the 1st and 4th records.

HOW DO I RETURN "PAIRS" OF RECORDS (HUSBAND AND WIFE) FROM THE DATABASE?