Two records, same last, different first, how do I show on one row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data sets that have married couples, the couples have the same last
name, I want to create a query that shows the last name plus the name of the
husband and the name of the wife on one row.
 
Assuming you have some way of knowing which people make up the couple, you
could use a subquery to get the name of any partner.

This example assumes a field name HouseholdID that is the same for the
partners:

SELECT tblPerson.PersonID, tblPerson.Surname, tblPerson.FirstName,
(SELECT TOP 1 Dupe.FirstName AS Partner
FROM tblPerson AS Dupe
WHERE Dupe.HouseholdID = tblPerson.HouseholdID
ORDER BY Dupe.PersonID) AS Partner
FROM tblPerson;

If subqueries are a new idea, here is Microsoft's introduction:
http://support.microsoft.com/?id=209066

For general guidelines on how to set up the database structure for people in
households, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
 
Combine the fields together withe a space between the fields

Select [Family name] & " " & [husband Name] & " " & [Wife Name] From Mytable
 
bltopaz said:
I have data sets that have married couples, the couples have the same last
name, I want to create a query that shows the last name plus the name of the
husband and the name of the wife on one row.
Click to expand...

This can be tricky because your constraints need to ensure couples are
one known male and one known female, one person can only be married to
one other person, etc

CREATE TABLE People
(key_col INTEGER NOT NULL,
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) NOT NULL,
sex_code INTEGER DEFAULT 0 NOT NULL,
CHECK (sex_code in (0, 1, 2)),
PRIMARY KEY (key_col, sex_code));

CREATE TABLE Marriages
(male_key_col INTEGER NOT NULL UNIQUE,
male_sex_code INTEGER NOT NULL,
CHECK (male_sex_code = 1),
CONSTRAINT fk__marriages__people__male
FOREIGN KEY (male_key_col, male_sex_code)
REFERENCES People (key_col, sex_code)
ON DELETE CASCADE
ON UPDATE CASCADE,
female_key_col INTEGER NOT NULL UNIQUE,
female_sex_code INTEGER NOT NULL,
CHECK (female_sex_code = 2),
CONSTRAINT fk__marriages__people__female
FOREIGN KEY (female_key_col, female_sex_code)
REFERENCES People (key_col, sex_code)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (male_key_col, female_key_col));

SELECT P2.first_name AS wife_name, P1.first_name AS husband_name,
P2.last_name AS married_name
FROM (People AS P1 INNER JOIN Marriages
ON Marriages.male_key_col = P1.key_col)
INNER JOIN People AS P2
ON Marriages.female_key_col = P2.key_col
AND P1.last_name = P2.last_name
 
Back
Top