A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

union query without "blank" recordsl



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2010, 05:54 PM posted to microsoft.public.access.queries
mark kubicki
external usenet poster
 
Posts: 84
Default union query without "blank" recordsl

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



  #2  
Old May 26th, 2010, 07:21 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default union query without "blank" recordsl

Mark Kubicki wrote:

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);


That is NOT a union query. A union query has more than one
SELECT statemenr with UNION or UNION ALL between them.

To avoid retrieving records from FixtureTypes when there is
no matching record in FixtureCatalogsPages, change the LEFT
JOIN to INNER JOIN

--
Marsh
MVP [MS Access]
  #3  
Old May 26th, 2010, 07:53 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default union query without "blank" recordsl

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

Mark Kubicki wrote:
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



  #4  
Old May 26th, 2010, 07:58 PM posted to microsoft.public.access.queries
Wolfgang Kais[_4_]
external usenet poster
 
Posts: 18
Default union query without "blank" recordsl

Hello Mark.

Mark Kubicki wrote:
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.

--
Regards,
Wolfgang


  #5  
Old May 27th, 2010, 09:19 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default union query without "blank" recordsl

Use INNER join instead of LEFT.
--
Build a little, test a little.


"Mark Kubicki" wrote:

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



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.