Subquery with two left joins

[Bonediggler1]

Hello--

I am getting the "the specified field 'C.ALC' could refer to more than one table listed in the FROM clause of your SQL statement" error.

Any insight here would be great!

SELECT D.[ALC REGION], D.[ALC LOCATION], C.*, E.TYPE

INTO TBLPBR_DATA

FROM

(([SELECT B.ALC, A.* FROM TBLPBR A LEFT JOIN QRYGET_ALC B ON B.CLAIM_NUM = A.PATIENT_CLAIM_NUMBER]. AS C) LEFT JOIN TBLALC_LOCATIONS AS D ON C.ALC = D.[ALC NUM])

LEFT JOIN TBLALC_TYPE_MATCH E ON C.ALC = E.ALC

 

[GingerR]

what's QRYGET_ALC? Somewhere you have C.ALC referenced in a part of this query. you have to investigate each piece.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[lameid]

Try removing the [red]offending dot (.)[/red].

SELECT D.[ALC REGION], D.[ALC LOCATION], C.*, E.TYPE

INTO TBLPBR_DATA

FROM 

(([SELECT B.ALC,  A.*  FROM TBLPBR A LEFT JOIN QRYGET_ALC B ON B.CLAIM_NUM = A.PATIENT_CLAIM_NUMBER][red].[/red] AS C)  LEFT JOIN TBLALC_LOCATIONS AS D ON C.ALC = D.[ALC NUM]) 

LEFT JOIN TBLALC_TYPE_MATCH E ON C.ALC = E.ALC

 

[Bonediggler1]

Thank you both for the suggestions.

As for #1, the query works with the first left join but throws the error after adding the second. I don't know how C.ALC could be referenced in qryGet_ALC as it is a seperate query. Maybe there are too many fields named ALC in the overall SQL statement?

As for # 2, I removed the dot then got the error that Accecss did not recognize the subquery...

Any more thoughts?

 

[lameid]

Also the [red]INTO TBLPBR_DATA[/red] is invalid syntax.

If you remove that and the dot I mentioned, it will at least have correct syntax.