SQL Updated question conditional division

Given columns A and B from Table1:

A B

Small     3

Med       4

Med       1

Large     2

Small     1

Given columns S, M, L from Table2:

S M L

5     5     3

8     2     1

4     6     5

2     2     8

I want to create a new column in Table1 that outputs the quotients of values of column B and Avg(Table2.S) only if the entry in the same row in column A of is ‘Small’. If column A has ‘Med’, we divide by Avg(Table2.M) and if it has ‘Large’, we divide by Avg(Table2.L). It would continue to check this row by row. For example, in the first row of column B, we have ‘3’, and on the same row in column A, we have ‘Small’, so we would output 0.6316 (3/Avg(Table2.S)). So the expected column, let’s call it C, would be:

C

0.6316

1.0667

0.2667

0.4076

0.2105

How would I output C using MS Access SQL? I tried

Select
Switch(Table1.A ='Small', Table1.B/Avg(Table2.S),
Table1.A ='Med', Table1.B/Avg(Table2.M),
Table1.A ='Large', Table1.B/Avg(Table2.L))
From Table1, Table2

but I usually get an error that says my query doesn’t include the specified expression as an aggregate function.

Answer

Here is a way to do this

select A,B,switch(A="Small",x.avg_s,A="Med",avg_m,A="Large",avg_l) as avg_vals,B/switch(A="Small",x.avg_s,A="Med",avg_m,A="Large",avg_l)
  from table1 y, 
          (select avg(S) as avg_s,avg(M) as avg_m,avg(L) as avg_l 
             from table2
          )x

The output is