Query to unnormalize some data

  • Thread starter Thread starter Joseph Greenberg
  • Start date Start date
J

Joseph Greenberg

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).
 
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).
Click to expand...

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.
 
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
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 said:
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).
Click to expand...

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.
Click to expand...
 
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 said:
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 said:
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
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"
<[email protected]>
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
an ok, one tweak that I can't figure out... is it possible, in the same
query, for a case where there is no SeqNo=2 for a family, to move the name
to the second column? So assuming I have men as member1 and women as member2
(uniformly and enforceable), if there is no man, can we get the women's name
in the second column of names? or do i have to create a secondary query that
does this based on the value of SeqNo2?

Joseph Greenberg said:
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 said:
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 said:
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
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"
<[email protected]>
[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.
Click to expand...
Click to expand...
Click to expand...
 
and ok, one tweak that I can't figure out... is it possible, in the same
query, for a case where there is no SeqNo=2 for a family, to move the name
to the second column? So assuming I have men as member1 and women as member2
(uniformly and enforceable), if there is no man, can we get the women's name
in the second column of names? or do i have to create a secondary query that
does this based on the value of SeqNo2?

Joseph Greenberg said:
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 said:
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 said:
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
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"
<[email protected]>
[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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top