Select records with similar data

[Kiran]

Hi,

Loan Borrower SSN
1 Kishore 123-456-789
1 Venugopal 234-567-891
2 Sudeendra 345-678-912
2 Sadashiva 456-789-123
3 Raghavendra 567-891-234
3 Bheemsen 678-912-345

Based on the above sample data I need a query which looks like below. Could
someone help me on this.

Loan Borrower SSN CoBorrower Co_SSN
1 Kishore 123-456-789 Venugopal 234-567-891
2 Sudeendra 345-678-912 Sadashiva 456-789-123
3 Raghavendra 567-891-234 Bheemsen 678-912-345

 

[Stefan_889_12]

Hi,
I call your table Table_SSN.

1. I saved query qryFirstSSN
SELECT Table_SSN.Loan, First(Table_SSN.Borrower) AS FirstOfBorrower,
First(Table_SSN.SSN) AS FirstOfSSN
FROM Table_SSN
GROUP BY Table_SSN.Loan;

2. I saved query qryLastSSN
SELECT Table_SSN.Loan, Last(Table_SSN.Borrower) AS LastOfBorrower,
Last(Table_SSN.SSN) AS LastOfSSN
FROM Table_SSN
GROUP BY Table_SSN.Loan;

3. Last query show your required output:

SELECT qryFirstSSN.Loan, qryFirstSSN.FirstOfBorrower AS Borrower,
qryFirstSSN.FirstOfSSN AS SSN, qryLastSSN.LastOfBorrower AS CoBorrower,
qryLastSSN.LastOfSSN AS CO_SSN
FROM qryFirstSSN INNER JOIN qryLastSSN ON qryFirstSSN.Loan = qryLastSSN.Loan;

Stefan

 

[Kiran]

Thanks for your help. Is it not possible to run a single query and get the
result

Kiran

 

[John Spencer]

Possible yes. IF your field and table names consist of ONLY Letters,
Numbers and the underscore character.

SELECT PartA.Loan, FirstOfBorrower, FirstOfSSN
, LastOfBorrower, LastOfSSN
FROM
(SELECT Table_SSN.Loan, First(Table_SSN.Borrower) AS FirstOfBorrower,
First(Table_SSN.SSN) AS FirstOfSSN
FROM Table_SSN
GROUP BY Table_SSN.Loan) as PartA
LEFT JOIN
(SELECT Table_SSN.Loan, Last(Table_SSN.Borrower) AS LastOfBorrower,
Last(Table_SSN.SSN) AS LastOfSSN
FROM Table_SSN
GROUP BY Table_SSN.Loan) as PartB
ON PartA.Loand = PartB.Loan

The problem here is that if there are not exactly TWO persons per loan
you will either have redundant data (same person listed twice) OR
missing data (additional persons missing)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Kiran]

Is it possible for us to have the desired result if we have a unique id like
below

Loan Borrower SSN UniqueID
1 Kishore 123-456-789 234567
1 Venugopal 234-567-891 435729
2 Sudeendra 345-678-912 357298
2 Sadashiva 456-789-123 572986
3 Raghavendra 567-891-234 729869
3 Bheemsen 678-912-345 298695

 

[John Spencer]

Now that I look at this a second time, you should be able to do this in
a simpler query. That looks like the following.

SELECT Table_SSN.Loan
, First(Table_SSN.Borrower) AS FirstOfBorrower
, First(Table_SSN.SSN) AS FirstOfSSN
, First(Table_SSN.UniqueID) as FirstOfUniqueID
, Last(Table_SSN.Borrower) AS LastOfBorrower
, Last(Table_SSN.SSN) AS LastOfSSN
, Last(Table_SSN.UniqueID) as LastOfUniqueID
FROM Table_SSN
GROUP BY Table_SSN.Loan

Just be aware that first and last return fields values from the first
and last record found in each group of records. That does not mean the
first (or last) record entered, the most recent by some date field, etc.)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================