Conflicts in MS-Access

Navigation
0

I have a table in an access database with the following fields: 

DATA       | SALA | AULA1 | AULA2 | AULA3 | AULA4 | DOCENTE | TURNO
----------------------------------------------------------------------
02/02/2015 | S1   | 1     | 1     | 0     | 0     | JOÃO    | MATUTINO
----------------------------------------------------------------------
02/02/2015 | S1   | 0     | 0     | 1     | 1     | JORGE   | MATUTINO    
----------------------------------------------------------------------
02/02/2015 | S1   | 1     | 1     | 0     | 0     | JOSÉ    | MATUTINO
----------------------------------------------------------------------    
02/02/2015 | S2   | 1     | 1     | 1     | 0     | MARIA   | MATUTINO

I would like to make a query that returns the conflicting classes on the day taking into account the day and the room used.

Ex: 02/02/2015 room 01 is being used by the class in the first two hours of class and in the two endings. Note that on the same day 2 (3rd line) there is a conflict between the 3rd and 1st line (in class 1 and 2). I would like to format a query that informs these two conflicts.

Could anyone help me?

Sorry for the table format ... I could not configure it at all.

    
asked by anonymous 17.06.2015 / 19:52

1 answer

1

Based on the principle that your table has an identifier and using EXISTS , it facilitates the query: 

SELECT t1.* 
FROM suatabela t1
WHERE EXISTS (
              SELECT t2.id 
              FROM suatabela t2 
              WHERE t1.ID <> t2.ID 
                AND t1.DATA = t2.DATA 
                AND t1.SALA = t2.SALA
                AND (t1.AULA1 = 1 AND t2.AULA1 = 1)
                     OR (t1.AULA2 = 1 AND t2.AULA2 = 1)
                     OR (t1.AULA3 = 1 AND t2.AULA3 = 1)
                     OR (t1.AULA4 = 1 AND t2.AULA4 = 1))
             )



t1.ID <> t2.ID

This is the most important part of SELECT.

It should be somehow easier, but so uncreative now.

suatabela : this table you showed the information.

    
17.06.2015 / 20:02
File manipulation Error generating Report in Jasper Ireports