union query without "blank" recordsl

[Mark Kubicki]

I have a union query between 2 related tables, and where they may be records
in one table [FixtureTypes] with no value in the other
[FixtureCatalogsPages ].
I've got this (below) query written, which successfully joins the 2;
however, it still provides a record when there is no match. I want the
query to provide ONLY records for where there is a match between the 2
tables.

I suspect I should be using some sort of property or filter?

SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo
AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink,
FixtureCatalogsPages.printOrder
FROM FixtureTypes
LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo =
FixtureCatalogsPages.CatalogNumber) AND (FixtureTypes.Manufacturer =
FixtureCatalogsPages.Manufacturer);

any thoughts and suggestions will be greatly appreciated in advance,
thanks,
mark

 

[John Spencer]

That is not a UNION query. It is a joined query.

As a guess, try changing LEFT JOIN to INNER JOIN and see if that gives you the
desired results. The LEFT JOIN will return all records in the first table and
any matching data in the second table. If there is no matching data in the
second table then it will return blanks for all the fields in the second table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Wolfgang Kais]

Hello Mark.

I have a union query between 2 related tables, and where they may be
records in one table [FixtureTypes] with no value in the other
[FixtureCatalogsPages].
I've got this (below) query written, which successfully joins the 2;
however, it still provides a record when there is no match. I want
the query to provide ONLY records for where there is a match between
the 2 tables.

I suspect I should be using some sort of property or filter?

SELECT FixtureTypes.Type, FixtureTypes.Manufacturer,
FixtureTypes.CatalogNo AS CatalogNumber,
FixtureCatalogsPages.CatalogSheetLink, FixtureCatalogsPages.printOrder
FROM FixtureTypes
LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo =
FixtureCatalogsPages.CatalogNumber) AND
(FixtureTypes.Manufacturer = FixtureCatalogsPages.Manufacturer);

any thoughts and suggestions will be greatly appreciated in advance,
thanks,
mark

You mean that you're joining two tables in a query, this is not a
union query.
For some reason, you have chosen to use a LEFT JOIN, which returns all
rows from the left side (FixtureTypes), also those with no matching
record in the right table (FixtureCatalogsPages). To only display
the matching rows, change the LEFT JOIN to a simple INNER JOIN.

 

[KARL DEWEY]

Use INNER join instead of LEFT.