Database Discussion - SQL/Access Search for ’Pairs’ of records

Print Page | Close Window

SQL/Access Search for ’Pairs’ of records

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=4625
Printed Date: 30 March 2026 at 3:54am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL/Access Search for ’Pairs’ of records

Posted By: schammy
Subject: SQL/Access Search for ’Pairs’ of records
Date Posted: 30 July 2003 at 2:58pm

(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?

Replies:

Posted By: ljamal
Date Posted: 30 July 2003 at 3:31pm

I would join the husbands and wives with a JOIN and return them as a unit.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: schammy
Date Posted: 30 July 2003 at 3:33pm

Thanks Jamal, but I am not a programmer. What do you mean by JOIN? Is this an SQL command or an ACCESS function?

Posted By: ljamal
Date Posted: 30 July 2003 at 3:37pm

It's a SQL command which allows you to combine records which meet your specified criteria.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: schammy
Date Posted: 30 July 2003 at 3:39pm

I thought JOIN is used when one has 2 DIFFERENT tables. All my records ar in 1 table.

Posted By: ljamal
Date Posted: 30 July 2003 at 3:44pm

You can join a table with itself as long as you declare a different name for one instance of the table

For instance you can do:
select Table.Pkey, Tble.PKEY from Table, Table Tble where tble.Pkey = Table.PKey+3

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Flamewave
Date Posted: 30 July 2003 at 3:47pm

This page may help you to understand the JOIN command better:

http://www.w3schools.com/sql/sql_join.asp - http://www.w3schools.com/sql/sql_join.asp

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: michael
Date Posted: 30 July 2003 at 3:52pm

To do just a join on the No. Field, well not really a join more of a Subquery you could do something like:
select * from tblTest where [No.] IN (Select [No.] from tblTest where Surname = 'Jones')

Now you just have to adjust the subquery to your LIKE query for both field but you should get the jist of it.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: schammy
Date Posted: 31 July 2003 at 12:21am

Thanks Michael,

That sounds more like it. I knew it would probably be a simple answer. One last bit of advice though (I am a doctor and not a proficient programmer!)...

What would the query string be for 2 fields ('no.' and 'year')?

Posted By: Morgan
Date Posted: 02 August 2003 at 10:00pm

How about changing the way you are displaying the data? What about:

Surname (of search), Firstname (of search), Year, No, Surname (of spouse), Firstname (of spouse)

To do this in MS Access, then for your SQL query try:

SELECT h.surname, h.first_name, h.[year], h.[no.], w.surname, w.first_name
FROM marriages h
INNER JOIN marriages w
ON h.[year] = w.[year]
AND h.[no.] = w.[no.]
AND h.surname <> w.surname
AND h.first_name <> w.first_name
WHERE h.surname LIKE '*JONES*';

In MS SQL Server:

-------------
Morgan