multi level query using the same table over and over

[Karen]

I need to query a single table over and over again and have a problem
with one of the links.

I'm using Access 2003 and the tables are MS SQL tables from our
business software.
The table is a bill of material table with a parent field and a
component field (there are other fields but they aren't important at
this time).

When I created the Select Query I added the table 6 times (each was
given an alias). I linked from the component of the first to the
parent of the second; from the component of the second to the parent of
the third and so on. I then ran the query for the parent of the first
equal to the item I was looking for.

The problem is that something is wrong with the link between the third
and fourth level. The query doesn't return parents in the fourth level
for all of the components in the third. It returns some of them but
not all of them. I don't see a pattern in the records that would
explain this. And the business software returns all of the components
in the product structure.

What is going on with this? Am I trying to do this type of link with
too many levels? Is there a better way to do this type of query?

Thanks for your help.

Karen

 

[[MVP] S.Clark]

If you have all of the joins as INNER joins, then it would require that the
parent have a component at every level to return values. By making them an
OUTER join, it would show all records, whether they had one component, or
multiple.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Karen]

All of the joins are LEFT JOINs.
I think the joins are correct since the query returns the components
for some items on the third level. The problem is that it isn't
returning data where I know there should be data (for other items on
the third level). If I use these items as the first level, their
structure is displayed as expected.

Here is the SQL code for the query...

SELECT DISTINCT dbo_BMPRDSTR_SQL.item_no,
dbo_BMPRDSTR_SQL.comp_item_no, level1.comp_item_no,
level2.comp_item_no, level3.item_no, level3.seq_no,
level3.comp_item_no, level4.comp_item_no, dbo_imitmidx_sql.item_desc_1,
dbo_imitmidx_sql.item_desc_2, dbo_imitmidx_sql_1.item_desc_1,
dbo_imitmidx_sql_1.item_desc_2
FROM (((((dbo_BMPRDSTR_SQL LEFT JOIN dbo_BMPRDSTR_SQL AS level1 ON
dbo_BMPRDSTR_SQL.comp_item_no = level1.item_no) INNER JOIN
dbo_imitmidx_sql ON dbo_BMPRDSTR_SQL.item_no =
dbo_imitmidx_sql.item_no) INNER JOIN dbo_imitmidx_sql AS
dbo_imitmidx_sql_1 ON dbo_BMPRDSTR_SQL.comp_item_no =
dbo_imitmidx_sql_1.item_no) LEFT JOIN dbo_BMPRDSTR_SQL AS level2 ON
level1.comp_item_no = level2.item_no) LEFT JOIN dbo_BMPRDSTR_SQL AS
level3 ON level2.comp_item_no = level3.item_no) LEFT JOIN
dbo_BMPRDSTR_SQL AS level4 ON level3.comp_item_no = level4.item_no
WHERE (((dbo_BMPRDSTR_SQL.item_no)=[enter code]));

 

[[MVP] S.Clark]

Using Criteria (WHERE clause) with Outer joins, will often cause the query
to behave as an Inner Join.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

All of the joins are LEFT JOINs.
I think the joins are correct since the query returns the components
for some items on the third level. The problem is that it isn't
returning data where I know there should be data (for other items on
the third level). If I use these items as the first level, their
structure is displayed as expected.

Here is the SQL code for the query...

SELECT DISTINCT dbo_BMPRDSTR_SQL.item_no,
dbo_BMPRDSTR_SQL.comp_item_no, level1.comp_item_no,
level2.comp_item_no, level3.item_no, level3.seq_no,
level3.comp_item_no, level4.comp_item_no, dbo_imitmidx_sql.item_desc_1,
dbo_imitmidx_sql.item_desc_2, dbo_imitmidx_sql_1.item_desc_1,
dbo_imitmidx_sql_1.item_desc_2
FROM (((((dbo_BMPRDSTR_SQL LEFT JOIN dbo_BMPRDSTR_SQL AS level1 ON
dbo_BMPRDSTR_SQL.comp_item_no = level1.item_no) INNER JOIN
dbo_imitmidx_sql ON dbo_BMPRDSTR_SQL.item_no =
dbo_imitmidx_sql.item_no) INNER JOIN dbo_imitmidx_sql AS
dbo_imitmidx_sql_1 ON dbo_BMPRDSTR_SQL.comp_item_no =
dbo_imitmidx_sql_1.item_no) LEFT JOIN dbo_BMPRDSTR_SQL AS level2 ON
level1.comp_item_no = level2.item_no) LEFT JOIN dbo_BMPRDSTR_SQL AS
level3 ON level2.comp_item_no = level3.item_no) LEFT JOIN
dbo_BMPRDSTR_SQL AS level4 ON level3.comp_item_no = level4.item_no
WHERE (((dbo_BMPRDSTR_SQL.item_no)=[enter code]));

If you have all of the joins as INNER joins, then it would require that
the
parent have a component at every level to return values. By making them
an
OUTER join, it would show all records, whether they had one component, or
multiple.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Karen]

In that case, are there any suggested work-arounds so that I can get
the desired data from my file?

K-

Using Criteria (WHERE clause) with Outer joins, will often cause the query
to behave as an Inner Join.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

All of the joins are LEFT JOINs.
I think the joins are correct since the query returns the components
for some items on the third level. The problem is that it isn't
returning data where I know there should be data (for other items on
the third level). If I use these items as the first level, their
structure is displayed as expected.

Here is the SQL code for the query...

SELECT DISTINCT dbo_BMPRDSTR_SQL.item_no,
dbo_BMPRDSTR_SQL.comp_item_no, level1.comp_item_no,
level2.comp_item_no, level3.item_no, level3.seq_no,
level3.comp_item_no, level4.comp_item_no, dbo_imitmidx_sql.item_desc_1,
dbo_imitmidx_sql.item_desc_2, dbo_imitmidx_sql_1.item_desc_1,
dbo_imitmidx_sql_1.item_desc_2
FROM (((((dbo_BMPRDSTR_SQL LEFT JOIN dbo_BMPRDSTR_SQL AS level1 ON
dbo_BMPRDSTR_SQL.comp_item_no = level1.item_no) INNER JOIN
dbo_imitmidx_sql ON dbo_BMPRDSTR_SQL.item_no =
dbo_imitmidx_sql.item_no) INNER JOIN dbo_imitmidx_sql AS
dbo_imitmidx_sql_1 ON dbo_BMPRDSTR_SQL.comp_item_no =
dbo_imitmidx_sql_1.item_no) LEFT JOIN dbo_BMPRDSTR_SQL AS level2 ON
level1.comp_item_no = level2.item_no) LEFT JOIN dbo_BMPRDSTR_SQL AS
level3 ON level2.comp_item_no = level3.item_no) LEFT JOIN
dbo_BMPRDSTR_SQL AS level4 ON level3.comp_item_no = level4.item_no
WHERE (((dbo_BMPRDSTR_SQL.item_no)=[enter code]));

If you have all of the joins as INNER joins, then it would require that
the
parent have a component at every level to return values. By making them
an
OUTER join, it would show all records, whether they had one component, or
multiple.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html



I need to query a single table over and over again and have a problem
with one of the links.

I'm using Access 2003 and the tables are MS SQL tables from our
business software.
The table is a bill of material table with a parent field and a
component field (there are other fields but they aren't important at
this time).

When I created the Select Query I added the table 6 times (each was
given an alias). I linked from the component of the first to the
parent of the second; from the component of the second to the parent of
the third and so on. I then ran the query for the parent of the first
equal to the item I was looking for.

The problem is that something is wrong with the link between the third
and fourth level. The query doesn't return parents in the fourth level
for all of the components in the third. It returns some of them but
not all of them. I don't see a pattern in the records that would
explain this. And the business software returns all of the components
in the product structure.

What is going on with this? Am I trying to do this type of link with
too many levels? Is there a better way to do this type of query?

Thanks for your help.

Karen