Access using an SQL JOIN or similar statement as a record source for a report

SELECT * 
FROM QryMain 
WHERE [Plant Number] IN (SELECT [Plant Number] FROM [QryProceduresDone]); 

Hi all.

Currently using the above as a report record source.

Basically I want the report to show only records from QryMain that also have corresponding records in QryProceduresDone. Both Queries have the [Plant Number] field.

The above statement works fine but I believe it could slow down if having to deal with many records.

I believe using a JOIN statement is much more efficient and have tried various attempts at using join without success.

Can anyone please help in suggesting a way to apply a join or any other alternative statement that is more efficient than what I am using?

Using Access 2010.

Thanks in advance.

Answer

The equivalent code with a join would be:

SELECT m.* 
FROM QryMain AS m INNER JOIN QryProceduresDone AS pd
ON pd.[Plant Number] = m.[Plant Number];

but a join may return duplicates if the relation of the tables is not 1:1 and also I’m not sure if this would make any significant difference in performance.

What may be better is EXISTS:

SELECT m.* 
FROM QryMain AS m 
WHERE EXISTS (SELECT 1 FROM QryProceduresDone AS pd WHERE pd.[Plant Number] = m.[Plant Number]);