Join to a table based on different criteria

[sb4216]

I am trying to find out how to join 2 tables (in reality one of them is a query that I am trying to join to a reference table) based on different join criteria. I am tyring to join [South]=[South] in one case and [Other] like "Other*" in another case. I am not having much luck.

Here is a simplified example of what I am trying to do:

QueryTable:
Customer Tech Region
1 A South
1 B South
2 B North
2 C North
3 A Other North
3 C Other North
4 B Other South
5 C Other South
6 D South

BudgetTable:
Tech Region BudgetAmt
A South 100
B South 50
C South 25
A North 300
B North 50
C North 250
A Other 25
B Other 100
C Other 500

Desired Results:
Customer Tech Query.Region Budget.Region BudgetAmt
1 A South South 100
1 B South South 50
2 B North North 50
2 C North North 250
3 A Other North Other 25
3 C Other North Other 500
4 B Other South Other 100
5 C Other South Other 500
6 D South

I created the below query and I get "JOIN expression not supported."

SELECT querytable.*, budgettable.*, budgettable_1
FROM (QueryTable LEFT JOIN BudgetTable ON (QueryTable.Region = BudgetTable.Region) AND (QueryTable.Tech = BudgetTable.Tech))
left join budgettable_1 on (QueryTable.Tech = BudgetTable_1.Tech) AND (BudgetTable_1.Region = "Other");

I know that would not have given me 100% accurate results, but I was trying to take it one step at a time.

 

[boblarson]

You don't join on the region value. You join on REGION to QryRegion or from REGION to BudgetRegion and then in the CRITERIA you put which one or ones you want.

 

[sb4216]

In general I understand that....but my desired results contain both sets the ones that I can match on region as is and the ones that can't match specifically based on the data. So my desired results contain the South, North and Other regions. Not just one or two at a time. This query is just the beginning, I then have to create a query based on budget amount percentages by customer. I can handle that part, I am just struggling trying to get all of my regions/budget amounts into one table.

I have been continuing to work with this query and it seems like union might work, but when I do that my results are not quite right.