0

I have an access db that i must use to manage interns and the places they work at. Right now, I have two tables: one for the persons, with their personal detail and a bridge to where they work, and another table with the name of the workplace with the respective boss.

Like so:
(table 1, where the persons are listed)
Cadastro_de_estagiarios

id | Ativo | Nível | Lotação | Nome
1 | Verdadeiro | Superior | 1ª Vara Cível | Marina x
3 | Verdadeiro | Médio | 1ª Vara Cível | Raquel x
and so on...


(table 2, where the locations and bosses are specificated)
Cadastro_de_varas_e_juizes
id | Vara | Juiz responsável | Vagas totais nível superior | Vagas totais nível médio
1 | 1ª Vara Cível | fist boss | 2 | 3
2 | 2ª Vara Cível | sec boss | 2 | 4
3 | 3ª Vara Cível | third boss | 2 | 3
and so on...

To clarify, I have two kinds of interns (nível superior e nível médio), as well as two kinds of job vacancies per workplace. Like this: In 1ª Vara Cível, I can have 2 interns with "superior" and 3 with "médio".

What I need to do is get the info on how many interns are placed on each workplace per job type, and then have a query that tells me how many vacancies I still have per place and type.

I appreciate any help. Thanks!

Translating the tables
table1
id | Active | Education level of intern | Workplace | Name

table2
id | Workplace | Boss | Vacancies for college students | Vacancies for high school students

rjungers
  • 1
  • 1
  • `SELECT (SELECT COUNT(*) FROM xxx) - (SELECT COUNT(*) FROM yyy)` should work –  Jul 07 '17 at 13:40
  • but maybe the answer is some join. Can you please edit your question so it contains only english, that will probably help getting an answer. (You could also post a sqlfiddle.net link that would probably help even more) –  Jul 07 '17 at 13:42
  • I couldn't get the count - count to work because there is only one thing to count, i think, namely the total of times each workplace appear on the first table per nível (level of instruction, high school or college) médio ou superior. I'll try to use the sql fiddle site and post back. TY! – rjungers Jul 07 '17 at 13:48
  • @RC. Couldn't figure out what to input on the sql fiddle. =( – rjungers Jul 07 '17 at 13:54
  • Here you go: http://www.sqlfiddle.com/#!9/0e8b4 –  Jul 07 '17 at 14:20

1 Answers1

0

This should give you a starting point: SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE table1 (
  id INT,
  Active VARCHAR(50),
  Education_level VARCHAR(50),
  Workplace VARCHAR(50),
  Name VARCHAR(50)
);

CREATE TABLE table2  (
  id INT,
  Workplace VARCHAR(50),
  Boss VARCHAR(50),
  Vacancies_college INT,
  Vacancies_high_school INT
);

INSERT INTO table1 VALUES
(1, 'Verdadeiro', 'Superior', '1ª Vara Cível', 'Marina x'),
(3, 'Verdadeiro', 'Médio', '1ª Vara Cível', 'Raquel x');

INSERT INTO table2 VALUES
(1, '1ª Vara Cível', 'fist boss', 2, 3),
(2, '2ª Vara Cível', 'sec boss', 2, 4),
(3, '3ª Vara Cível', 'third boss', 2, 3);

Query 1:

SELECT t1.Workplace, t1.Active, t1.Education_level, 
(CASE 
 WHEN t1.Education_level = 'Médio' THEN t2.Vacancies_college
 WHEN t1.Education_level = 'Superior' THEN t2.Vacancies_high_school
END)  - COUNT(*) AS vacancies 
FROM table1 t1 LEFT JOIN table2 t2 
ON (t1.Workplace = t2.Workplace)
GROUP BY t1.Workplace, t1.Active, t1.Education_level

Results:

|     Workplace |     Active | Education_level | vacancies |
|---------------|------------|-----------------|-----------|
| 1ª Vara Cível | Verdadeiro |           Médio |         1 |
| 1ª Vara Cível | Verdadeiro |        Superior |         2 |
  • Thank you very much for your help, that got me to understand a few things. Somehow, though, I couldn't get the query to work as it says "missing operator". I've seen it work on sql fiddle, and it produces the result i want it to, but no dice on access when I changed the names and tried to run it. – rjungers Jul 07 '17 at 16:31
  • I think it's because of the case, check https://stackoverflow.com/questions/14920116/does-ms-access-support-case-when-clause-if-connect-with-odbc –  Jul 07 '17 at 16:36