Using left Join with limits on 2nd Table

[sjck]

I have two tables with a left join on Provider ID. I want everything returned from the 1st table and then all records from the 2nd table where the Provider ID matches. The problem I am having is that I need to set a limit on the 2nd table to only reuturn "Active" providers. But when I add that limit it applies it towards the entire queyr and therfore does not return all records from the 1st table.
I need the query to return all records from the 1st table, and then any matching "Active" records from the 2nd table therefore only applying the limit to the second table.

Below is the SQL:

SELECT [Colorado Top Providers].ID, ONESOURCE_O_D1_PRV.T30_PRV_ID, ONESOURCE_O_D1_PRV.CALC_PRV_ID, ONESOURCE_O_D1_PRV.CALC_PRV_LOC, ONESOURCE_O_D1_PRV.T13_STAT_CD, ONESOURCE_O_D1_PRV.T13_EXTR_PRV_NBR, ONESOURCE_O_D1_PRV.T30_TIN_NBR, ONESOURCE_O_D1_PRV.T30_TIN_TYPE_CD, ONESOURCE_O_D1_PRV.T30_STAT_CD INTO [T0000: PRV Table TEST]
FROM [Colorado Top Providers] LEFT JOIN ONESOURCE_O_D1_PRV ON [Colorado Top Providers].[Prov ID 9 Digit] = ONESOURCE_O_D1_PRV.CALC_PRV_ID
WHERE (((ONESOURCE_O_D1_PRV.T30_STAT_CD)="A"));

 

[r937]

SELECT C.ID
     , O.T30_PRV_ID
     , O.CALC_PRV_ID
     , O.CALC_PRV_LOC
     , O.T13_STAT_CD
     , O.T13_EXTR_PRV_NBR
     , O.T30_TIN_NBR
     , O.T30_TIN_TYPE_CD
     , O.T30_STAT_CD 
  INTO [T0000: PRV Table TEST]
  FROM [Colorado Top Providers] AS C
LEFT OUTER
  JOIN ONESOURCE_O_D1_PRV AS O
    [blue]ON (
       O.CALC_PRV_ID = C.[Prov ID 9 Digit]
   AND O.T30_STAT_CD = 'A'
       )[/blue]

r937.com | rudy.ca

 

[lespaul]

you know rudy, I was going to post something similar, but then I remembered that PH corrected me in another post that you can't do the filtering in the JOIN in Access...I do it all the time in DB2, but apparently Access can't?

Maybe I'm misremembering....

Leslie

 

[r937]

you might be misremembering

or else i'm unforgetting

;-)

r937.com | rudy.ca

 

[sjck]

I tried it on my first query and it seems to work!

 

[lespaul]

apparently i was misremembering!