Query results incomplete

[Guest]

I have a form with a combo box to select a Product Name and display its
Product Number and Description in a subform. The subform utilizes the
following query as the record source:

SELECT Products.[Product Part Number], Products.ProductName,
Parts.[Sub-Assembly Part Number], Parts.[TC Part Number], Parts.[Part
Description], Parts.[Part Category]
FROM Products INNER JOIN Parts ON Products.[Product Part Number] =
Parts.[Product Part Number];

The problem is the results of the query is not returning all rows of the
Products table so I have Product Names from the combo box with no
corresponding description etc. to display in the subform and I cannot see why
(they do exist in the underlying table). The master/child forms are linked
by Products Part Number.

What am I doing wrong?

Thanks

 

[Tom Ellison]

Dear Mattie:

The query you show would NOT be expected to return all the rows of the
Products table. It would show only those rows of the Products table which
have related rows in the Parts table. Any Product which does not have a
related Part would be excluded.

To show all Products whether they have a Part or not, change INNER JOIN to
LEFT JOIN.

The Products which have no Parts will then be shown in the query with NULL
values in all the columns which derive from the Parts table.

For your information, a study of JOINs will show there are 4 kinds of JOINs:

INNER
LEFT
RIGHT
FULL or FULL OUTER

The INNER join is symetrical in excluding rows unless BOTH joined sets have
related rows. The OUTER join is symetrical in including all rows where
EITHER set is represented. LEFT and RIGHT joins will show all the rows of
one set and only those from the other that relate. These are asymetrical
JOINs.

In the case of a one-to-many relationship with enforced referential
integrity, the LEFT JOIN (from the foreign table on the left to the
dependent table on the right) would be identical to the OUTER JOIN, while
the INNER JOIN and RIGHT JOIN would be identical. This is because there are
no orphan rows on the dependent side of the relationship. There are other
types of situations where the 4 types of joins would give 4 unique sets of
results, but these are less commonly seen in database applications.

Tom Ellison

 

[Guest]

Thank you, you're the best! I have one other question...How do I make it
display the data in the subform upon selection of the item in the combo box.
Currently, I have to make the combo box selection, then click on the record
selector in the subform to then display the relevant fields.

Thanks

Dear Mattie:

The query you show would NOT be expected to return all the rows of the
Products table. It would show only those rows of the Products table which
have related rows in the Parts table. Any Product which does not have a
related Part would be excluded.

To show all Products whether they have a Part or not, change INNER JOIN to
LEFT JOIN.

The Products which have no Parts will then be shown in the query with NULL
values in all the columns which derive from the Parts table.

For your information, a study of JOINs will show there are 4 kinds of JOINs:

INNER
LEFT
RIGHT
FULL or FULL OUTER

The INNER join is symetrical in excluding rows unless BOTH joined sets have
related rows. The OUTER join is symetrical in including all rows where
EITHER set is represented. LEFT and RIGHT joins will show all the rows of
one set and only those from the other that relate. These are asymetrical
JOINs.

In the case of a one-to-many relationship with enforced referential
integrity, the LEFT JOIN (from the foreign table on the left to the
dependent table on the right) would be identical to the OUTER JOIN, while
the INNER JOIN and RIGHT JOIN would be identical. This is because there are
no orphan rows on the dependent side of the relationship. There are other
types of situations where the 4 types of joins would give 4 unique sets of
results, but these are less commonly seen in database applications.

Tom Ellison

I have a form with a combo box to select a Product Name and display its
Product Number and Description in a subform. The subform utilizes the
following query as the record source:

SELECT Products.[Product Part Number], Products.ProductName,
Parts.[Sub-Assembly Part Number], Parts.[TC Part Number], Parts.[Part
Description], Parts.[Part Category]
FROM Products INNER JOIN Parts ON Products.[Product Part Number] =
Parts.[Product Part Number];

The problem is the results of the query is not returning all rows of the
Products table so I have Product Names from the combo box with no
corresponding description etc. to display in the subform and I cannot see
why
(they do exist in the underlying table). The master/child forms are
linked
by Products Part Number.

What am I doing wrong?

Thanks

 

[Tom Ellison]

Dear Mattie:

My standard way of doing this is to write the SQL for the subform in code in
the combo box's On Click event and assign that to the RecordSource of the
subform. The SQL code can include the current filtering of the recordset.

Tom Ellison

Thank you, you're the best! I have one other question...How do I make it
display the data in the subform upon selection of the item in the combo
box.
Currently, I have to make the combo box selection, then click on the
record
selector in the subform to then display the relevant fields.

Thanks

Dear Mattie:

The query you show would NOT be expected to return all the rows of the
Products table. It would show only those rows of the Products table
which
have related rows in the Parts table. Any Product which does not have a
related Part would be excluded.

To show all Products whether they have a Part or not, change INNER JOIN
to
LEFT JOIN.

The Products which have no Parts will then be shown in the query with
NULL
values in all the columns which derive from the Parts table.

For your information, a study of JOINs will show there are 4 kinds of
JOINs:

INNER
LEFT
RIGHT
FULL or FULL OUTER

The INNER join is symetrical in excluding rows unless BOTH joined sets
have
related rows. The OUTER join is symetrical in including all rows where
EITHER set is represented. LEFT and RIGHT joins will show all the rows
of
one set and only those from the other that relate. These are asymetrical
JOINs.

In the case of a one-to-many relationship with enforced referential
integrity, the LEFT JOIN (from the foreign table on the left to the
dependent table on the right) would be identical to the OUTER JOIN, while
the INNER JOIN and RIGHT JOIN would be identical. This is because there
are
no orphan rows on the dependent side of the relationship. There are
other
types of situations where the 4 types of joins would give 4 unique sets
of
results, but these are less commonly seen in database applications.

Tom Ellison

I have a form with a combo box to select a Product Name and display its
Product Number and Description in a subform. The subform utilizes the
following query as the record source:

SELECT Products.[Product Part Number], Products.ProductName,
Parts.[Sub-Assembly Part Number], Parts.[TC Part Number], Parts.[Part
Description], Parts.[Part Category]
FROM Products INNER JOIN Parts ON Products.[Product Part Number] =
Parts.[Product Part Number];

The problem is the results of the query is not returning all rows of
the
Products table so I have Product Names from the combo box with no
corresponding description etc. to display in the subform and I cannot
see
why
(they do exist in the underlying table). The master/child forms are
linked
by Products Part Number.

What am I doing wrong?

Thanks