Conflicts in MS-Access

Asked

Viewed 65 times

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 consultation that would return the conflicting classes on the day taking into account the day and the room used.

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

Could someone help me?

Sorry for the table format....

sql ms-access

1 answer

1


Assuming your table has an identifier and using the EXISTS, facilitates consultation:

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.

There must be some easy way, but I’m not creative right now.

suatabela: this table that you showed the information.

  • Beast, but this command Inner Join not to gather information from more than one table?

  • 1

    @Filipes.Martins That’s why I join table 2x itself to find the coincidences.

  • I got it, very good guy, I didn’t even imagine I could do it. I noticed that at the exit of the consultation the fields appeared T1. (labellodocampo) and T2. (labellodocampo) of all the fields I entered in the query. You can simplify this?

  • @Filipes.Martins You want to catch what?

  • DATA | SALA | AULA1 | AULA2 | AULA3 | AULA4 | DOCENTE | TURNO basically the information that is in the same table. Thank you so much for helping Maicon Carraro

  • @Filipes.Martins In case you have conflict line 1 and 3, which teacher will you show? the 2?

  • Exactly, it actually shows all the lines that have conflict. It is as if to show the lines that are duplicated in the fields (date and room and(Aula1 or aula2 or aula3 or aula4)

  • In this case I think it is better to use another solution EXISTS, just to confirm you have id in this table?

  • Yes, I have a code field with automatic numbering

  • @Filipes.Martins I changed the answer with the new solution, a tested

  • This last one you sent ended up giving a syntax error in the expression EXISTS...

  • @Filipes.Martins Glue the whole mistake here

  • Syntax error in query expression 'EXISTS ( SELECT t2.ID FROM Tblhorario t2 ON t1.ID <> t2.ID AND t1.DATA = t2.DATA AND t1.SALA = t2.SALA WHERE (t1.AULA1 = 1 AND t2.AULA1 = 1) OR (t1.AULA2 = 1 AND t2.AULA2 = 1)

  • One more parenthesis to close

  • I closed but the error persists. It will not be some comma that was missing?

  • @Filipes.Martins Perdao, I had forgotten one ON inside the exists, arranged in the answer of a check

  • 1

    Top Maicon, I think now it worked blz! I will do more tests.... Thanks for the force.

  • Anything but drip my name again :)

Show 13 more comments

Browser other questions tagged sql ms-access

You are not signed in. Login or sign up in order to post.