Joins in Query

[CindySue]

I have three tables. LP, TA and PR. I need to create a report that selects only those records in LP which have one or more matching records in either the TA or the PR table (or it could have multiple matches in both). I created a report with records from LP and two sub reports with records from TA and LP, but of course I get records that don't have entries in either table. I tried to create a query using joins, but it gave me an error message about an ambigous outer join. I tried to create a separate query and use that, as the error message said to do, but I can't seem to get anything to work. I finally created a make table query and an append query to select the matching records from one table and then append matching records from another and based the report on that new table and it works, but it is so clumsy and I need some additional reports so I don't want to continue that.

My fields that I'm working with are:
LP table
license number
Site name
City
ABC
and some other fields.

TA table
license number
Type
Consultant
Date Completed

PR table
license number
type
Scale
Consultant
Date Completed

I would be most appreciative of some help (in newbie language). I tried searching for other posts with "Joins" but I coudn't find anything that I could make work. Thanks so much.

[Rabbit]

I have three tables. LP, TA and PR. I need to create a report that selects only those records in LP which have one or more matching records in either the TA or the PR table (or it could have multiple matches in both). I created a report with records from LP and two sub reports with records from TA and LP, but of course I get records that don't have entries in either table. I tried to create a query using joins, but it gave me an error message about an ambigous outer join. I tried to create a separate query and use that, as the error message said to do, but I can't seem to get anything to work. I finally created a make table query and an append query to select the matching records from one table and then append matching records from another and based the report on that new table and it works, but it is so clumsy and I need some additional reports so I don't want to continue that.

My fields that I'm working with are:
LP table
license number
Site name
City
ABC
and some other fields.

TA table
license number
Type
Consultant
Date Completed

PR table
license number
type
Scale
Consultant
Date Completed

I would be most appreciative of some help (in newbie language). I tried searching for other posts with "Joins" but I coudn't find anything that I could make work. Thanks so much.

SELECT DISTINCT [LP Table].[License Number]
FROM (([LP Table] INNER JOIN [TA Table] ON [LP Table].[License Number] = [TA Table].[License Number]) INNER JOIN [PR Table] ON [LP Table].[License Number] = [PR Table].[License Number]);
 

[CindySue]

This seems to be selecting the records that have an entry in both tables, but not those that have an entry in only one table or the other as well. Also, it occurred to me that for my purposes it would be better to select from a query that is checking the date rather than the table. I am assuming I can substitute the query name for the table name? Thanks again so much for the help.

[Rabbit]

SELECT DISTINCT [LP Table].[License Number]
FROM [LP Table] INNER JOIN [TA Table] ON [LP Table].[License Number] = [TA Table].[License Number]
UNION
SELECT DISTINCT [LP Table].[License Number]
FROM [LP Table] INNER JOIN [PR Table] ON [LP Table].[License Number] = [PR Table].[License Number];
 

[CindySue]

THAT SO WORKS!!! Thank you so very much! I can't tell you how wonderful it is to have someone willing to help.

[Rabbit]

THAT SO WORKS!!! Thank you so very much! I can't tell you how wonderful it is to have someone willing to help.

Not a problem, good luck.

And I missed your question the first time around but yes, you can use query names instead of table names.