I do this all the time and, although I don't see the same fields in the Account as you (I used BillingState rather than Location__c and my Opportunity__c table is called plainly Opportunity) I am able to add the Billing City to the opportunities using a Left Join a la...
Id as Opp_Id,
Amount as Opp_Amount,
CloseDate as Opp_CloseDate,
Date(ClosedDate) as PaymentDate,
Year(CloseDate) as PaymentYear,
Month(CloseDate) as PaymentMonth;
SQL Select * From Opportunity
Where AccountId != Null
And Amount != Null;
Id as AccountId;
Where Id In (Select AccountId From Opportunity);
I added the Where clauses after thinking and seeing there were some null data. You may not want that.
Would you mind to mark this thread as "answered" by clicking "correct answer" on John's post? The reasons:
- The "answerers" don't waste time reading it, thinking it is still open.
- It helps others to find solutions in similar situations.
- Gives deserved credit for the correct answer. Unless you don't like John for some reason , you can always mark your own reply as "correct" - but you don't get points for this