SQL Statement Join With CASE

Question

I have problem with this 3 table below from MS ACCESS to do "SQL CASE IF ELSE" where I don't how to start.

Table A (Registrations)

Name    | Desc      | Amount    | Year
NameA   | JAN&NOV   | 100.00    | 2015
NameA   | BOOKS     | 70.00     | 2015
NameA   | UNIFORM   | 100.00    | 2015
NameB   | JAN&NOV   | 100.00    | 2015

Table B (Montly Payment and Others)

Name    | Desc      | Amount    | Year
NameA   | PAY FEB   | 100.00    | 2015
NameB   | PAY MAC   | 100.00    | 2015
NameA   | PAY MAC   | 100.00    | 2015
NameB   | OTHERS    | 20.00     | 2015
NameB   | PAY APR   | 100.00    | 2015

Table C (Student List)

Name    | Year      | Class 
NameA   | 2015      | A 
NameB   | 2015      | B

Result:

Name    | Year  | Class | Jan&Nov   | Pay Feb   | Pay Mac   | Pay Apr
NameA   | 2015  | A     | 100.00    | 100.00    | 100.00    | 000.00
NameB   | 2015  | B     | 100.00    | 100.00    | 100.00    | 100.00

I need to create field Pay Jan&Nov, Pay Feb till Pay Oct with data based of this 3 table. If data do not exist I need to format it as "00.00" or "NOT PAID".

I hope anybody who have a solutions please help me. I am new of SQL statement actually.

Answer 1

Here is the straight-forward solution with subqueries instead of aggregation and case constructs first. (I use NZ to return a 0 in case of NULL. This would be COALESCE in standard SQL).

select 
  name, 
  year,
  class,
  (
    select nz(sum(r.amount), 0)
    from registrations r 
    where r.name = s.name
    and r.year = s.year
    and r.desc = 'JAN&NOV'
  ) as "Jan&Nov",
  (
    select nz(sum(r.amount), 0)
    from monthly m 
    where m.name = s.name
    and m.year = s.year
    and desc = 'PAY FEB'
  ) as "Pay Feb",
  ...
from student_list s;

And here is the same with joins, aggregation and IIF (which would be CASE in standard SQL):

select 
  s.name, 
  s.year,
  s.class,
  nz(max(r.amount), 0) as "Jan&Nov",
  nz(max(iif(m.desc = 'PAY FEB', m.amount end, null)), 0) as "Pay Feb",
  ...
from student_list s
left join
(
  select 
    name, 
    year,
    sum(amount) as amount
  from registrations
  where desc = 'JAN&NOV'
  group by name, year
) r on r.name = s.name and r.year = s.year
left join
(
  select 
    name, 
    year,
    desc,
    sum(amount) as amount
  from monthly
  where group by name, year, desc
) m on m.name = s.name and m.year = s.year
group by s.name, s.year, s.class;

(You may or may not need additional parentheses around the joins. I think I remember MS Access to be rather peculiar with the join syntax.)

As to displaying the numbers in a certain format: this is something you'd usually do in your GUI layer. When using SQL for the formatting instead, you must convert the mere numbers into strings, e.g. 20 into '20.00'. In MS Access you do this with FORMAT:

format (value, 'Standard')

With the second query:

format(nz(max(iif(m.desc = 'PAY FEB', m.amount end, null)), 0), 'Standard')

which shows '0.00' in case of NULL. Or:

nz(format(max(iif(m.desc = 'PAY FEB', m.amount, null)), 'Standard'), 'NOT PAID')

which shows 'NOT PAID' in case of NULL.

Answer 2

It's not too clear from the question how TableA and TableB are related togheter, but it looks like you want a UNION ALL query and you want to exclude some categories:

SELECT
  c.Name,
  c.Year,
  c.Class,
  ab.Desc,
  ab.Amount
FROM
  TableC AS C INNER JOIN (
    SELECT * FROM TableA WHERE Desc NOT IN ('BOOKS', 'UNIFORM', 'OTHERS')
    UNION ALL
    SELECT * FROM TableB WHERE Desc NOT IN ('BOOKS', 'UNIFORM', 'OTHERS')
  ) AS ab
  ON c.Name = ab.Name AND c.Year = ab.Year

then you can use a Pivot using the previous query as a subquery:

TRANSFORM Sum(Amount) AS SumOfAmount
SELECT Name, Year, Class, Sum(Amount) AS [Sum_Amount]
FROM (

  ...the query above...

) AS s
GROUP BY Name, Year, Class
PIVOT Desc;

Answer 3

Thanks you to fthiella,DeadZone,Beth and Thorsten Kettner who give me a right direction. Since trying many day using yours solution directly on MS Access IDE finally I found a trick. I need to thanks you very much to solving my problem. The trick is :-

A) IF RECORD IS EMPTY or EXIST by SELECTION then using (Select Nz(MAX(m.amount),'NOT PAID') as PAYFEB.

All kind solutions like LEFT JOIN or Direct SUBQUERIES are working actually. But I more prefer SUBQUERIES because more organize and I can see if any syntax or field are not register.

Lastly, without yours support I cannot get this answer of this matter. Thanks you very much guys.