Combining multiple records? Newbie question.

[ChuckCasey]

I have linked to a database that has a table that has these fields:

acct_no, seq_no, user_defined

If the seq_no (sequence number) is 1, then the user_defined field holds a birth date. If the seq_no is 2, then the user_defined field holds a mother's maiden name.
So when I query the account number, I get 2 records.

I have another table that has more personal data and has a relationship with this linked table via the acct_no. I want to create a form that allows me to display all the personal data for someone, including the 2 pieces of data (birth date & mother's maiden name) that are currently displayed as 2 records. I don't know how to do this because the user-defined field is used for 2 purposes.

Is there a way to combine the 2 records into one? Many thanks.

 

[lespaul]

Here's a starting point. This query will return a single record with both pieces of data:

SELECT A.ACCT_NO, A.SEQ_NO, B.USER_DEFINED
FROM TableName A
INNER JOIN TableName B ON A.ACCT_NO = B.ACCT_NO

Leslie

 

[PHV]

Something like this ?
SELECT P.*, U1.user_defined AS BirthDate, U2.user_defined AS MotherMaiden
FROM (tblPersonalData AS P
LEFT JOIN tblUserMore AS U1 ON P.acct_no = U1.acct_no)
LEFT JOIN tblUserMore AS U2 ON P.acct_no = U2.acct_no
WHERE Nz(U1.seq_no, 1) = 1 AND Nz(U2.seq_no, 2) = 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244