Query to unnormalize some data

I have a the table with fields [FamNo], [SeqNo], [Name]. It is normalized. I
want to select out [SeqNo]= 1 or 2 (easy enough), and create two new
variables [NewName1] and [NewName2], so each record has both the 1 and 2
Names. Any ideas? And if this matters, the query needs to be based on a 2
linked tables (on FamNo).

On Wed, 17 Mar 2010 20:53:39 -0400, "Joseph Greenberg"
wrote:

I have a the table with fields [FamNo], [SeqNo], [Name]. It is normalized. I
want to select out [SeqNo]= 1 or 2 (easy enough), and create two new
variables [NewName1] and [NewName2], so each record has both the 1 and 2
Names. Any ideas? And if this matters, the query needs to be based on a 2
linked tables (on FamNo).


A "self join" query will work here. Add the table to the query grid *twice*,
joining the two on FamNo. Put a criterion of =1 on the first instance of
SeqNo, and =2 on the second. You will now have both name fields available.
--

John W. Vinson [MVP]

ok, this is cool, thanks for this - learned something.

However, it is not quite getting me what I want. Specfically, if I add the
table twice to the grid, then I have to add [SeqNo} from [Table] and [SeqNo]
from [Table_1], and put in a criteria (on the same line) for each field
(respectively, 1 for first field, 2 for second field, then I put in the Name
field twice, I get the format I want, but it is missing the records where
there is no SeqNo=2, that is, where there is only 1 parent member in the
database (not 2 parents). If this isn't clear, it is only giving me "parent"
records where there are two parents (seqno 1 and seqno 2). I need the final
query to give me all the records, even if there is only a seqno 1 associated
with a famno. Here is the SQL currently:

SELECT tMemberDetail.FamNo, tMemberDetail.SeqNo, tMemberDetail_1.SeqNo,
tMemberDetail.HebrewName, tMemberDetail_1.HebrewName
FROM (tMemberDetail INNER JOIN tMemberDetail AS tMemberDetail_1 ON
tMemberDetail.FamNo = tMemberDetail_1.FamNo) INNER JOIN tMemberHeader ON
tMemberDetail.FamNo = tMemberHeader.FamNo
WHERE (((tMemberDetail.SeqNo)=1) AND ((tMemberDetail_1.SeqNo)=2));

Any further thoughts?



"John W. Vinson" wrote in message
...
On Wed, 17 Mar 2010 20:53:39 -0400, "Joseph Greenberg"

wrote:

I have a the table with fields [FamNo], [SeqNo], [Name]. It is normalized.
I
want to select out [SeqNo]= 1 or 2 (easy enough), and create two new
variables [NewName1] and [NewName2], so each record has both the 1 and 2
Names. Any ideas? And if this matters, the query needs to be based on a 2
linked tables (on FamNo).


A "self join" query will work here. Add the table to the query grid
*twice*,
joining the two on FamNo. Put a criterion of =1 on the first instance of
SeqNo, and =2 on the second. You will now have both name fields available.
--

John W. Vinson [MVP]

You could perhaps do it with subqueries:

SELECT FamNo, SeqNo AS SeqNo1,
(SELECT FIRST(SeqNo)
FROM tMemberDetail AS MD2
WHERE MD2.FamNo = MD1.FamNo
AND SeqNo=2) AS SeqNo2,
HebrewName AS HebrewName1,
(SELECT FIRST(HebrewName)
FROM tMemberDetail AS MD3
WHERE MD3.FamNo = MD1.FamNo
AND SeqNo=2) AS HebrewName2
FROM tMemberDetail AS MD1
WHERE SeqNo=1;

The FIRST operators might not be necessary; they are to arbitrarily ensure
that each subquery returns one row only.

Ken Sheridan
Stafford, England

Joseph Greenberg wrote:
ok, this is cool, thanks for this - learned something.

However, it is not quite getting me what I want. Specfically, if I add the
table twice to the grid, then I have to add [SeqNo} from [Table] and [SeqNo]
from [Table_1], and put in a criteria (on the same line) for each field
(respectively, 1 for first field, 2 for second field, then I put in the Name
field twice, I get the format I want, but it is missing the records where
there is no SeqNo=2, that is, where there is only 1 parent member in the
database (not 2 parents). If this isn't clear, it is only giving me "parent"
records where there are two parents (seqno 1 and seqno 2). I need the final
query to give me all the records, even if there is only a seqno 1 associated
with a famno. Here is the SQL currently:

SELECT tMemberDetail.FamNo, tMemberDetail.SeqNo, tMemberDetail_1.SeqNo,
tMemberDetail.HebrewName, tMemberDetail_1.HebrewName
FROM (tMemberDetail INNER JOIN tMemberDetail AS tMemberDetail_1 ON
tMemberDetail.FamNo = tMemberDetail_1.FamNo) INNER JOIN tMemberHeader ON
tMemberDetail.FamNo = tMemberHeader.FamNo
WHERE (((tMemberDetail.SeqNo)=1) AND ((tMemberDetail_1.SeqNo)=2));

Any further thoughts?

On Wed, 17 Mar 2010 20:53:39 -0400, "Joseph Greenberg"

[quoted text clipped - 11 lines]
joining the two on FamNo. Put a criterion of =1 on the first instance of
SeqNo, and =2 on the second. You will now have both name fields available.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

Well, I must admit that this worked, although I really don't understand it.
I got the number of rows I expected, for familes with one or two parents,
and the correct names in two columns. If you don't mind, for my education,
would you mind explaining this a little? I'm not much of an SQL expert.

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a5412afe46f4a@uwe...
You could perhaps do it with subqueries:

SELECT FamNo, SeqNo AS SeqNo1,
(SELECT FIRST(SeqNo)
FROM tMemberDetail AS MD2
WHERE MD2.FamNo = MD1.FamNo
AND SeqNo=2) AS SeqNo2,
HebrewName AS HebrewName1,
(SELECT FIRST(HebrewName)
FROM tMemberDetail AS MD3
WHERE MD3.FamNo = MD1.FamNo
AND SeqNo=2) AS HebrewName2
FROM tMemberDetail AS MD1
WHERE SeqNo=1;

The FIRST operators might not be necessary; they are to arbitrarily ensure
that each subquery returns one row only.

Ken Sheridan
Stafford, England

Joseph Greenberg wrote:
ok, this is cool, thanks for this - learned something.

However, it is not quite getting me what I want. Specfically, if I add the
table twice to the grid, then I have to add [SeqNo} from [Table] and
[SeqNo]
from [Table_1], and put in a criteria (on the same line) for each field
(respectively, 1 for first field, 2 for second field, then I put in the
Name
field twice, I get the format I want, but it is missing the records where
there is no SeqNo=2, that is, where there is only 1 parent member in the
database (not 2 parents). If this isn't clear, it is only giving me
"parent"
records where there are two parents (seqno 1 and seqno 2). I need the
final
query to give me all the records, even if there is only a seqno 1
associated
with a famno. Here is the SQL currently:

SELECT tMemberDetail.FamNo, tMemberDetail.SeqNo, tMemberDetail_1.SeqNo,
tMemberDetail.HebrewName, tMemberDetail_1.HebrewName
FROM (tMemberDetail INNER JOIN tMemberDetail AS tMemberDetail_1 ON
tMemberDetail.FamNo = tMemberDetail_1.FamNo) INNER JOIN tMemberHeader ON
tMemberDetail.FamNo = tMemberHeader.FamNo
WHERE (((tMemberDetail.SeqNo)=1) AND ((tMemberDetail_1.SeqNo)=2));

Any further thoughts?

On Wed, 17 Mar 2010 20:53:39 -0400, "Joseph Greenberg"

[quoted text clipped - 11 lines]
joining the two on FamNo. Put a criterion of =1 on the first instance of
SeqNo, and =2 on the second. You will now have both name fields
available.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1