recursive relationship query question

  • Thread starter Thread starter LMC
  • Start date Start date
L

LMC

When I apply a recursive relationship to a series of MS-Access 2003 tables
and queries (see simplified examples below), it returns extra records that it
shouldn't. I would appreciate any guidance on figuring out why and how to fix
it or if there is a better way of going about it.

I have three tables... Tables A and B have a many-to-many relationship
establised in Table C.
Tbl-A_________ Tbl-B__________ Tbl-C___________
fld-A-id fld-A fld-B-id fld-B fld-A-id fld-B-id
A1 a B1 b A1 B1
A2 aa B2 bb A1 B2
A3 aaa B3 bbb A3 B2
A4 aaaa B4 bbbb A4 B4

I have two quieries built on the above tables. Query-A returns all of
Table-A records and the relational Field-B-id values from Table-C. Query-BA
returns all of Table-B records and the relational values from all fields in
Query-A.
Qry-A_____________________________
Tbl-A__________ Tbl-C___________
fld-A-id fld-A fld-A-id fld-B-id
A1 a A1 B1
A2 aa A1 B2
A3 aaa A3 B2
A4 aaaa A4 B4

Qry-BA__________________________
Tbl-B__________ Qry-A________
fld-B-id fld-B fld-A-id fld-A
B1 b A1 a
B2 bb A1 a
B2 bb A3 aaa
B3 bbb null null
B4 bbbb A4 aaaa

A form button initiates code to save Query-BA to a table and flatten
normalized Field-A values based on duplicate Field-B values. My relational
theory works fine up to this point.
Tbl-BA______________________________
fld-B-id fld-B fld-A-id fld-A (flat)
B1 b A1 a
B2 bb A1, A3 a, aaa
B3 bbb null null
B4 bbbb A4 aaaa

My problem resides with the recursive part of Query-ABA that returns all
Query-A records with related Table-BA records including the code-flattened
values.
Expected Qry-ABA Results________________________
Qry-A________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

The above theory works as expected when I implement it in a database using
only the sample content explained above. But in my active database,
containing more complicted values, the results below is what I get. It
returns extra derived rows where Query-A records return empty Tbl-BA values
as well as repeated identical rows. It does the same thing even if I change
the query join from a left join to an inner join.

Example of Problem Qry-ABA Results_______________
Qry-A_________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a null null null
A1 a null null null
A1 a B1 b a
A1 a B2 bb a, aaa
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

I apologize for the lengthy explaination; I didn't see that it could be
explained more succinctly. Again, I appreciate any sort of advice.
 
Hi LMC,

"It returns extra derived rows where Query-A records return empty Tbl-BA
values as well as repeated identical rows"

to suppress the rows where you have Nulls that you do not want, just add
criteria under that column -->
Is Not Null

to suppress identical rows, add the word DISTINCT after the word SELECT
in the SQL:

SELECT DISTINCT ...



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Thanks for your reply Crystal. I think your suggestions are on track but I
tried them without success. When I change SELECT to SELECT DISTINCT, I
receive an error saying "ORDER BY clause conflicts with DISTINCT". My
"DISTINCT" and "Is Not Null" criteria is also conditional. My need is...
1) Keep rows with DUPLICATE Qry-A values.and UNIQUE related Tbl-BA values
2) Keep rows with UNIQUE Qry-A values.and NULL related Tbl-BA values
3) Rid all other rows.

Below is the more complicated recursive SQL statement of my live database...
SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal
Paragraph], [x-tblRFP].RFPid, [x-tblRFP].RFPdoc, [x-tblRFP].RFPnum,
[x-tblRFP].RFPsec, [x-tblRFP].RFPpara, [x-tblRFP].[Proposal Reference] AS
[Proposal References], [x-tblRFP].Compliance, "" AS [Review Notes], "" AS
[Review Recommendations]
FROM qselDOCbyAO LEFT JOIN [x-tblRFP] ON qselDOCbyAO.RFPid =
[x-tblRFP].RFPid ORDER BY qselDOCbyAO.volNum, qselDOCbyAO.s1, qselDOCbyAO.s2,
qselDOCbyAO.s3, qselDOCbyAO.s4, qselDOCbyAO.s5, qselDOCbyAO.s6,
qselDOCbyAO.s7, qselDOCbyAO.s8, [x-tblRFP].ID;
 
Hi LMC (what is your name?)

if you cannot use a Unique Values query (DISTINCT), then make it a
Totals query and use GroupBy in every column -- this will also eliminate
duplicates.

As for the criteria, you will have to explain a little differently about
what the problem is and what is wrong with the data generated

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your reply Crystal. I think your suggestions are on track but I
tried them without success. When I change SELECT to SELECT DISTINCT, I
receive an error saying "ORDER BY clause conflicts with DISTINCT". My
"DISTINCT" and "Is Not Null" criteria is also conditional. My need is...
1) Keep rows with DUPLICATE Qry-A values.and UNIQUE related Tbl-BA values
2) Keep rows with UNIQUE Qry-A values.and NULL related Tbl-BA values
3) Rid all other rows.

Below is the more complicated recursive SQL statement of my live database...
SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal
Paragraph], [x-tblRFP].RFPid, [x-tblRFP].RFPdoc, [x-tblRFP].RFPnum,
[x-tblRFP].RFPsec, [x-tblRFP].RFPpara, [x-tblRFP].[Proposal Reference] AS
[Proposal References], [x-tblRFP].Compliance, "" AS [Review Notes], "" AS
[Review Recommendations]
FROM qselDOCbyAO LEFT JOIN [x-tblRFP] ON qselDOCbyAO.RFPid =
[x-tblRFP].RFPid ORDER BY qselDOCbyAO.volNum, qselDOCbyAO.s1, qselDOCbyAO.s2,
qselDOCbyAO.s3, qselDOCbyAO.s4, qselDOCbyAO.s5, qselDOCbyAO.s6,
qselDOCbyAO.s7, qselDOCbyAO.s8, [x-tblRFP].ID;


strive4peace said:
Hi LMC,

"It returns extra derived rows where Query-A records return empty Tbl-BA
values as well as repeated identical rows"

to suppress the rows where you have Nulls that you do not want, just add
criteria under that column -->
Is Not Null

to suppress identical rows, add the word DISTINCT after the word SELECT
in the SQL:

SELECT DISTINCT ...



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
Click to expand...
Click to expand...
 
My name is Laurie, thanks for asking.

I tried the GROUP BY all columns both with and without an aggregate function
and in both instance it returns symbol charachters in the joined table fields
that look like chinese writting. Argh.

strive4peace said:
Hi LMC (what is your name?)

if you cannot use a Unique Values query (DISTINCT), then make it a
Totals query and use GroupBy in every column -- this will also eliminate
duplicates.

As for the criteria, you will have to explain a little differently about
what the problem is and what is wrong with the data generated

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your reply Crystal. I think your suggestions are on track but I
tried them without success. When I change SELECT to SELECT DISTINCT, I
receive an error saying "ORDER BY clause conflicts with DISTINCT". My
"DISTINCT" and "Is Not Null" criteria is also conditional. My need is...
1) Keep rows with DUPLICATE Qry-A values.and UNIQUE related Tbl-BA values
2) Keep rows with UNIQUE Qry-A values.and NULL related Tbl-BA values
3) Rid all other rows.

Below is the more complicated recursive SQL statement of my live database...
SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal
Paragraph], [x-tblRFP].RFPid, [x-tblRFP].RFPdoc, [x-tblRFP].RFPnum,
[x-tblRFP].RFPsec, [x-tblRFP].RFPpara, [x-tblRFP].[Proposal Reference] AS
[Proposal References], [x-tblRFP].Compliance, "" AS [Review Notes], "" AS
[Review Recommendations]
FROM qselDOCbyAO LEFT JOIN [x-tblRFP] ON qselDOCbyAO.RFPid =
[x-tblRFP].RFPid ORDER BY qselDOCbyAO.volNum, qselDOCbyAO.s1, qselDOCbyAO.s2,
qselDOCbyAO.s3, qselDOCbyAO.s4, qselDOCbyAO.s5, qselDOCbyAO.s6,
qselDOCbyAO.s7, qselDOCbyAO.s8, [x-tblRFP].ID;


strive4peace said:
Hi LMC,

"It returns extra derived rows where Query-A records return empty Tbl-BA
values as well as repeated identical rows"

to suppress the rows where you have Nulls that you do not want, just add
criteria under that column -->
Is Not Null

to suppress identical rows, add the word DISTINCT after the word SELECT
in the SQL:

SELECT DISTINCT ...



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




LMC wrote:
When I apply a recursive relationship to a series of MS-Access 2003 tables
and queries (see simplified examples below), it returns extra records that it
shouldn't. I would appreciate any guidance on figuring out why and how to fix
it or if there is a better way of going about it.

I have three tables... Tables A and B have a many-to-many relationship
establised in Table C.
Tbl-A_________ Tbl-B__________ Tbl-C___________
fld-A-id fld-A fld-B-id fld-B fld-A-id fld-B-id
A1 a B1 b A1 B1
A2 aa B2 bb A1 B2
A3 aaa B3 bbb A3 B2
A4 aaaa B4 bbbb A4 B4

I have two quieries built on the above tables. Query-A returns all of
Table-A records and the relational Field-B-id values from Table-C. Query-BA
returns all of Table-B records and the relational values from all fields in
Query-A.
Qry-A_____________________________
Tbl-A__________ Tbl-C___________
fld-A-id fld-A fld-A-id fld-B-id
A1 a A1 B1
A2 aa A1 B2
A3 aaa A3 B2
A4 aaaa A4 B4

Qry-BA__________________________
Tbl-B__________ Qry-A________
fld-B-id fld-B fld-A-id fld-A
B1 b A1 a
B2 bb A1 a
B2 bb A3 aaa
B3 bbb null null
B4 bbbb A4 aaaa

A form button initiates code to save Query-BA to a table and flatten
normalized Field-A values based on duplicate Field-B values. My relational
theory works fine up to this point.
Tbl-BA______________________________
fld-B-id fld-B fld-A-id fld-A (flat)
B1 b A1 a
B2 bb A1, A3 a, aaa
B3 bbb null null
B4 bbbb A4 aaaa

My problem resides with the recursive part of Query-ABA that returns all
Query-A records with related Table-BA records including the code-flattened
values.
Expected Qry-ABA Results________________________
Qry-A________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

The above theory works as expected when I implement it in a database using
only the sample content explained above. But in my active database,
containing more complicted values, the results below is what I get. It
returns extra derived rows where Query-A records return empty Tbl-BA values
as well as repeated identical rows. It does the same thing even if I change
the query join from a left join to an inner join.

Example of Problem Qry-ABA Results_______________
Qry-A_________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a null null null
A1 a null null null
A1 a B1 b a
A1 a B2 bb a, aaa
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

I apologize for the lengthy explaination; I didn't see that it could be
explained more succinctly. Again, I appreciate any sort of advice.
Click to expand...
Click to expand...
Click to expand...
 
I tried the GROUP BY all columns both with and without an aggregate
function
and in both instance it returns symbol charachters in the joined table
fields
that look like chinese writting. Argh.
Click to expand...

Are you joining on memo fields? Are you using combo boxes in the query?

(david)

LMC said:
My name is Laurie, thanks for asking.

I tried the GROUP BY all columns both with and without an aggregate
function
and in both instance it returns symbol charachters in the joined table
fields
that look like chinese writting. Argh.

strive4peace said:
Hi LMC (what is your name?)

if you cannot use a Unique Values query (DISTINCT), then make it a
Totals query and use GroupBy in every column -- this will also eliminate
duplicates.

As for the criteria, you will have to explain a little differently about
what the problem is and what is wrong with the data generated

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your reply Crystal. I think your suggestions are on track
but I
tried them without success. When I change SELECT to SELECT DISTINCT, I
receive an error saying "ORDER BY clause conflicts with DISTINCT". My
"DISTINCT" and "Is Not Null" criteria is also conditional. My need
is...
1) Keep rows with DUPLICATE Qry-A values.and UNIQUE related Tbl-BA
values
2) Keep rows with UNIQUE Qry-A values.and NULL related Tbl-BA
values
3) Rid all other rows.

Below is the more complicated recursive SQL statement of my live
database...
SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal
Paragraph], [x-tblRFP].RFPid, [x-tblRFP].RFPdoc, [x-tblRFP].RFPnum,
[x-tblRFP].RFPsec, [x-tblRFP].RFPpara, [x-tblRFP].[Proposal Reference]
AS
[Proposal References], [x-tblRFP].Compliance, "" AS [Review Notes], ""
AS
[Review Recommendations]
FROM qselDOCbyAO LEFT JOIN [x-tblRFP] ON qselDOCbyAO.RFPid =
[x-tblRFP].RFPid ORDER BY qselDOCbyAO.volNum, qselDOCbyAO.s1,
qselDOCbyAO.s2,
qselDOCbyAO.s3, qselDOCbyAO.s4, qselDOCbyAO.s5, qselDOCbyAO.s6,
qselDOCbyAO.s7, qselDOCbyAO.s8, [x-tblRFP].ID;


:

Hi LMC,

"It returns extra derived rows where Query-A records return empty
Tbl-BA
values as well as repeated identical rows"

to suppress the rows where you have Nulls that you do not want, just
add
criteria under that column -->
Is Not Null

to suppress identical rows, add the word DISTINCT after the word
SELECT
in the SQL:

SELECT DISTINCT ...



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




LMC wrote:
When I apply a recursive relationship to a series of MS-Access 2003
tables
and queries (see simplified examples below), it returns extra records
that it
shouldn't. I would appreciate any guidance on figuring out why and
how to fix
it or if there is a better way of going about it.

I have three tables... Tables A and B have a many-to-many
relationship
establised in Table C.
Tbl-A_________ Tbl-B__________ Tbl-C___________
fld-A-id fld-A fld-B-id fld-B fld-A-id fld-B-id
A1 a B1 b A1 B1
A2 aa B2 bb A1 B2
A3 aaa B3 bbb A3 B2
A4 aaaa B4 bbbb A4 B4

I have two quieries built on the above tables. Query-A returns all of
Table-A records and the relational Field-B-id values from Table-C.
Query-BA
returns all of Table-B records and the relational values from all
fields in
Query-A.
Qry-A_____________________________
Tbl-A__________ Tbl-C___________
fld-A-id fld-A fld-A-id fld-B-id
A1 a A1 B1
A2 aa A1 B2
A3 aaa A3 B2
A4 aaaa A4 B4

Qry-BA__________________________
Tbl-B__________ Qry-A________
fld-B-id fld-B fld-A-id fld-A
B1 b A1 a
B2 bb A1 a
B2 bb A3 aaa
B3 bbb null null
B4 bbbb A4 aaaa

A form button initiates code to save Query-BA to a table and flatten
normalized Field-A values based on duplicate Field-B values. My
relational
theory works fine up to this point.
Tbl-BA______________________________
fld-B-id fld-B fld-A-id fld-A (flat)
B1 b A1 a
B2 bb A1, A3 a, aaa
B3 bbb null null
B4 bbbb A4 aaaa

My problem resides with the recursive part of Query-ABA that returns
all
Query-A records with related Table-BA records including the
code-flattened
values.
Expected Qry-ABA Results________________________
Qry-A________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

The above theory works as expected when I implement it in a database
using
only the sample content explained above. But in my active database,
containing more complicted values, the results below is what I get.
It
returns extra derived rows where Query-A records return empty Tbl-BA
values
as well as repeated identical rows. It does the same thing even if I
change
the query join from a left join to an inner join.

Example of Problem Qry-ABA Results_______________
Qry-A_________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a null null null
A1 a null null null
A1 a B1 b a
A1 a B2 bb a, aaa
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

I apologize for the lengthy explaination; I didn't see that it could
be
explained more succinctly. Again, I appreciate any sort of advice.
Click to expand...
Click to expand...
Click to expand...
 
Hi Laurie,

here is the SQL you posted -- formatted for better readibility

SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter
, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal Paragraph]
, [x-tblRFP].RFPid
, [x-tblRFP].RFPdoc
, [x-tblRFP].RFPnum
, [x-tblRFP].RFPsec
, [x-tblRFP].RFPpara
, [x-tblRFP].[Proposal Reference] AS [Proposal References]
, [x-tblRFP].Compliance
, "" AS [Review Notes]
, "" AS [Review Recommendations]
FROM qselDOCbyAO
LEFT JOIN [x-tblRFP]
ON qselDOCbyAO.RFPid = [x-tblRFP].RFPid
ORDER BY qselDOCbyAO.volNum
, qselDOCbyAO.s1
, qselDOCbyAO.s2
, qselDOCbyAO.s3
, qselDOCbyAO.s4
, qselDOCbyAO.s5
, qselDOCbyAO.s6
, qselDOCbyAO.s7
, qselDOCbyAO.s8
, [x-tblRFP].ID;


What is the SQL of qselDOCbyAO ?



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



My name is Laurie, thanks for asking.

I tried the GROUP BY all columns both with and without an aggregate function
and in both instance it returns symbol charachters in the joined table fields
that look like chinese writting. Argh.

strive4peace said:
Hi LMC (what is your name?)

if you cannot use a Unique Values query (DISTINCT), then make it a
Totals query and use GroupBy in every column -- this will also eliminate
duplicates.

As for the criteria, you will have to explain a little differently about
what the problem is and what is wrong with the data generated

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your reply Crystal. I think your suggestions are on track but I
tried them without success. When I change SELECT to SELECT DISTINCT, I
receive an error saying "ORDER BY clause conflicts with DISTINCT". My
"DISTINCT" and "Is Not Null" criteria is also conditional. My need is...
1) Keep rows with DUPLICATE Qry-A values.and UNIQUE related Tbl-BA values
2) Keep rows with UNIQUE Qry-A values.and NULL related Tbl-BA values
3) Rid all other rows.

Below is the more complicated recursive SQL statement of my live database...
SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal
Paragraph], [x-tblRFP].RFPid, [x-tblRFP].RFPdoc, [x-tblRFP].RFPnum,
[x-tblRFP].RFPsec, [x-tblRFP].RFPpara, [x-tblRFP].[Proposal Reference] AS
[Proposal References], [x-tblRFP].Compliance, "" AS [Review Notes], "" AS
[Review Recommendations]
FROM qselDOCbyAO LEFT JOIN [x-tblRFP] ON qselDOCbyAO.RFPid =
[x-tblRFP].RFPid ORDER BY qselDOCbyAO.volNum, qselDOCbyAO.s1, qselDOCbyAO.s2,
qselDOCbyAO.s3, qselDOCbyAO.s4, qselDOCbyAO.s5, qselDOCbyAO.s6,
qselDOCbyAO.s7, qselDOCbyAO.s8, [x-tblRFP].ID;


:

Hi LMC,

"It returns extra derived rows where Query-A records return empty Tbl-BA
values as well as repeated identical rows"

to suppress the rows where you have Nulls that you do not want, just add
criteria under that column -->
Is Not Null

to suppress identical rows, add the word DISTINCT after the word SELECT
in the SQL:

SELECT DISTINCT ...



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




LMC wrote:
When I apply a recursive relationship to a series of MS-Access 2003 tables
and queries (see simplified examples below), it returns extra records that it
shouldn't. I would appreciate any guidance on figuring out why and how to fix
it or if there is a better way of going about it.

I have three tables... Tables A and B have a many-to-many relationship
establised in Table C.
Tbl-A_________ Tbl-B__________ Tbl-C___________
fld-A-id fld-A fld-B-id fld-B fld-A-id fld-B-id
A1 a B1 b A1 B1
A2 aa B2 bb A1 B2
A3 aaa B3 bbb A3 B2
A4 aaaa B4 bbbb A4 B4

I have two quieries built on the above tables. Query-A returns all of
Table-A records and the relational Field-B-id values from Table-C. Query-BA
returns all of Table-B records and the relational values from all fields in
Query-A.
Qry-A_____________________________
Tbl-A__________ Tbl-C___________
fld-A-id fld-A fld-A-id fld-B-id
A1 a A1 B1
A2 aa A1 B2
A3 aaa A3 B2
A4 aaaa A4 B4

Qry-BA__________________________
Tbl-B__________ Qry-A________
fld-B-id fld-B fld-A-id fld-A
B1 b A1 a
B2 bb A1 a
B2 bb A3 aaa
B3 bbb null null
B4 bbbb A4 aaaa

A form button initiates code to save Query-BA to a table and flatten
normalized Field-A values based on duplicate Field-B values. My relational
theory works fine up to this point.
Tbl-BA______________________________
fld-B-id fld-B fld-A-id fld-A (flat)
B1 b A1 a
B2 bb A1, A3 a, aaa
B3 bbb null null
B4 bbbb A4 aaaa

My problem resides with the recursive part of Query-ABA that returns all
Query-A records with related Table-BA records including the code-flattened
values.
Expected Qry-ABA Results________________________
Qry-A________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

The above theory works as expected when I implement it in a database using
only the sample content explained above. But in my active database,
containing more complicted values, the results below is what I get. It
returns extra derived rows where Query-A records return empty Tbl-BA values
as well as repeated identical rows. It does the same thing even if I change
the query join from a left join to an inner join.

Example of Problem Qry-ABA Results_______________
Qry-A_________ Tbl-BA_____________________
fld-A-id fld-A fld-B-id fld-B fld-A (flat)
A1 a null null null
A1 a null null null
A1 a B1 b a
A1 a B2 bb a, aaa
A1 a B1 b a
A1 a B2 bb a, aaa
A2 aa null null null
A3 aaa null null null
A3 aaa B2 bb a, aaa
A4 aaaa B4 bbbb aaaa

I apologize for the lengthy explaination; I didn't see that it could be
explained more succinctly. Again, I appreciate any sort of advice.
Click to expand...
Click to expand...
Click to expand...
 
Yes I am joining on memo fields

That's the problem. You will have to find a something different.

Joining on memo fields (on the first 255 characters of memo
fields actually) works in simple queries, but breaks like this
in queries like this.

It's a feature that was added in Jet 4.0. and has never been
fixed. I continue to have great respect for the original
developers of Jet 1 and 2.

(david)

LMC said:
David: Yes I am joining on memo fields but am not using combo boxes in the
query.

Crystal: Below is SQL statement you reqested as well as a description of
the
query it is based on. I appreciate your diligence in trying to help me
solve
this issue.

qselDOCbySection is my first query which successfully extracts content
from
my first table (tblDOC) along with s# fields which are calculated fields
that
parse out a section number for sorting purposes (i.e. section:1.1.2
becomes
s1:1, s2:1, s3:2, s4:0, etc). It also pulls a related id field from a
second
table (trelDOCdesign) that contains only two id fields that establish a
many-to-many relationship between records of the first table (tblDOC) and
a
third table (tblRFP).

The qselDOCbyAO SQL below is my second query that successfully formats the
above data as well as pulls other related fields from the third table.
This
is the first recursion of cross references.

SELECT qselDOCbySection.DOCid
, qselDOCbySection.modNum
, IIf(IsNull([secNum]),"","v" & [volNum] & "-s" & [secNum]) AS Xref
, qselDOCbySection.secName
, qselDOCbySection.secAuthor
, qselDOCbySection.pgs
, qselDOCbySection.secScore
, qselDOCbySection.Notes
, qselDOCbySection.Memo
, trelDOCdesign.RFPid
, qselDOCbySection.volNum
, qselDOCbySection.secNum
, qselDOCbySection.s1
, qselDOCbySection.s2
, qselDOCbySection.s3
, qselDOCbySection.s4
, qselDOCbySection.s5
, qselDOCbySection.s6
, qselDOCbySection.s7
, qselDOCbySection.s8
FROM qselDOCbySection
LEFT JOIN trelDOCdesign
ON qselDOCbySection.DOCid = trelDOCdesign.DOCid
ORDER BY qselDOCbySection.volNum
, qselDOCbySection.s1
, qselDOCbySection.s2
, qselDOCbySection.s3
, qselDOCbySection.s4
, qselDOCbySection.s5
, qselDOCbySection.s6
, qselDOCbySection.s7
, qselDOCbySection.s8;


qselXREFbyAO>x-tblRFP is my third query which is the second recursion of
cross references that produces undesired results explained in my previous
posts. The needs of this query are...
1) Keep rows with DUPLICATE tblDOC values and UNIQUE tblRFP values
2) Keep rows with UNIQUE tblDOC values and NULL tblRFP values
3) Rid all other rows.

SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter
, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal Paragraph]
, [x-tblRFP].RFPid
, [x-tblRFP].RFPdoc
, [x-tblRFP].RFPnum
, [x-tblRFP].RFPsec
, [x-tblRFP].RFPpara
, [x-tblRFP].[Proposal Reference] AS [Proposal References]
, [x-tblRFP].Compliance
, "" AS [Review Notes]
, "" AS [Review Recommendations]
FROM qselDOCbyAO
LEFT JOIN [x-tblRFP]
ON qselDOCbyAO.RFPid = [x-tblRFP].RFPid
ORDER BY qselDOCbyAO.volNum
, qselDOCbyAO.s1
, qselDOCbyAO.s2
, qselDOCbyAO.s3
, qselDOCbyAO.s4
, qselDOCbyAO.s5
, qselDOCbyAO.s6
, qselDOCbyAO.s7
, qselDOCbyAO.s8
, [x-tblRFP].ID;

Many thanks for you continued help.
Click to expand...
 
Correction... The RFPid joined query fields are numbers generated by the
AutoNumer Key fields of the first and third tables. But I do have memo fields
in query that display correctly. It's just a matter of deleting rows produced
from my current SQL statement that do not meet the two specified sets of
criteria.

david said:
Yes I am joining on memo fields
Click to expand...

That's the problem. You will have to find a something different.

Joining on memo fields (on the first 255 characters of memo
fields actually) works in simple queries, but breaks like this
in queries like this.

It's a feature that was added in Jet 4.0. and has never been
fixed. I continue to have great respect for the original
developers of Jet 1 and 2.

(david)

LMC said:
David: Yes I am joining on memo fields but am not using combo boxes in the
query.

Crystal: Below is SQL statement you reqested as well as a description of
the
query it is based on. I appreciate your diligence in trying to help me
solve
this issue.

qselDOCbySection is my first query which successfully extracts content
from
my first table (tblDOC) along with s# fields which are calculated fields
that
parse out a section number for sorting purposes (i.e. section:1.1.2
becomes
s1:1, s2:1, s3:2, s4:0, etc). It also pulls a related id field from a
second
table (trelDOCdesign) that contains only two id fields that establish a
many-to-many relationship between records of the first table (tblDOC) and
a
third table (tblRFP).

The qselDOCbyAO SQL below is my second query that successfully formats the
above data as well as pulls other related fields from the third table.
This
is the first recursion of cross references.

SELECT qselDOCbySection.DOCid
, qselDOCbySection.modNum
, IIf(IsNull([secNum]),"","v" & [volNum] & "-s" & [secNum]) AS Xref
, qselDOCbySection.secName
, qselDOCbySection.secAuthor
, qselDOCbySection.pgs
, qselDOCbySection.secScore
, qselDOCbySection.Notes
, qselDOCbySection.Memo
, trelDOCdesign.RFPid
, qselDOCbySection.volNum
, qselDOCbySection.secNum
, qselDOCbySection.s1
, qselDOCbySection.s2
, qselDOCbySection.s3
, qselDOCbySection.s4
, qselDOCbySection.s5
, qselDOCbySection.s6
, qselDOCbySection.s7
, qselDOCbySection.s8
FROM qselDOCbySection
LEFT JOIN trelDOCdesign
ON qselDOCbySection.DOCid = trelDOCdesign.DOCid
ORDER BY qselDOCbySection.volNum
, qselDOCbySection.s1
, qselDOCbySection.s2
, qselDOCbySection.s3
, qselDOCbySection.s4
, qselDOCbySection.s5
, qselDOCbySection.s6
, qselDOCbySection.s7
, qselDOCbySection.s8;


qselXREFbyAO>x-tblRFP is my third query which is the second recursion of
cross references that produces undesired results explained in my previous
posts. The needs of this query are...
1) Keep rows with DUPLICATE tblDOC values and UNIQUE tblRFP values
2) Keep rows with UNIQUE tblDOC values and NULL tblRFP values
3) Rid all other rows.

SELECT [qselDOCbyAO].[volNum] & "-" & [qselDOCbyAO].[s1] & ".x" AS
AutoFilter
, [modNum] & " " & [Xref] & " " & [secName] AS [Proposal Paragraph]
, [x-tblRFP].RFPid
, [x-tblRFP].RFPdoc
, [x-tblRFP].RFPnum
, [x-tblRFP].RFPsec
, [x-tblRFP].RFPpara
, [x-tblRFP].[Proposal Reference] AS [Proposal References]
, [x-tblRFP].Compliance
, "" AS [Review Notes]
, "" AS [Review Recommendations]
FROM qselDOCbyAO
LEFT JOIN [x-tblRFP]
ON qselDOCbyAO.RFPid = [x-tblRFP].RFPid
ORDER BY qselDOCbyAO.volNum
, qselDOCbyAO.s1
, qselDOCbyAO.s2
, qselDOCbyAO.s3
, qselDOCbyAO.s4
, qselDOCbyAO.s5
, qselDOCbyAO.s6
, qselDOCbyAO.s7
, qselDOCbyAO.s8
, [x-tblRFP].ID;

Many thanks for you continued help.
Click to expand...
Click to expand...
 
Back
Top