Union Query question

[Joe]

Hi All

I have a union query that doesnt quite do what I want it to. The
query is supposed to grab all records from the Trade-Skill table and
also display corresponding records from Contractor-Skill-Project table
that match.

There are 92 records in the skill table and I want the output to be 92
records and where there is a match in the other table have the Project
Code and Contractor Level display. There is no more than 1 matching
record in the 2nd table for each record in the first table.

When I run the below SQL it is creating 92 rows for every single
Project Skill and an additional 52 rows for all the records that match
in Table2.

I am sure it is a quick fix but I am going blind looking. Could
someone give it a quick look? Thx

SELECT [Contractor-Skill-Project].Project_Code, [Contractor-Skill-
Project].contractor_level, [Contractor-Skill-
Project].contractor_trade_name
FROM [Contractor-Skill-Project]
WHERE ((([Contractor-Skill-Project].Project_Code)="AxteReas") AND
(([Contractor-Skill-Project].contractor_level)="primary selected"))
UNION SELECT NULL,NULL, [Trade-Skill].Trade_Name
FROM [Trade-Skill];

 

[John Spencer]

You probably need an outer JOIN query and NOT a union query. A union query
combines a set of records from one query with another set of records from
another query.

So if you have 92 records in query A and another 52 records in query B, you
will get a total of 144 rows (records) returned.

Can't tell you how to build your query since you did not tell us the fields in
the two tables that allow the tables to relate to each other.

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

 

[Joe]

You probably need an outer JOIN query and NOT a union query. A union query
combines a set of records from one query with another set of records from
another query.

So if you have 92 records in query A and another 52 records in query B, you
will get a total of 144 rows (records) returned.

Can't tell you how to build your query since you did not tell us the fields in
the two tables that allow the tables to relate to each other.

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

Hi All

I have a union query that doesnt quite do what I want it to. The
query is supposed to grab all records from the Trade-Skill table and
also display corresponding records from Contractor-Skill-Project table
that match.

There are 92 records in the skill table and I want the output to be 92
records and where there is a match in the other table have the Project
Code and Contractor Level display. There is no more than 1 matching
record in the 2nd table for each record in the first table.

When I run the below SQL it is creating 92 rows for every single
Project Skill and an additional 52 rows for all the records that match
in Table2.

I am sure it is a quick fix but I am going blind looking. Could
someone give it a quick look? Thx

SELECT [Contractor-Skill-Project].Project_Code, [Contractor-Skill-
Project].contractor_level, [Contractor-Skill-
Project].contractor_trade_name
FROM [Contractor-Skill-Project]
WHERE ((([Contractor-Skill-Project].Project_Code)="AxteReas") AND
(([Contractor-Skill-Project].contractor_level)="primary selected"))
UNION SELECT NULL,NULL, [Trade-Skill].Trade_Name
FROM [Trade-Skill];

Thanks John, I actually figured out that what I needed was a query
with the 52 records and then a query based on that query that excluded
he 52 records. Took a little extra thinking on this one.

Love these groups, thanks for all the support!!!!