Error retrieving SQL data

Asked

Viewed 50 times

2

I have this code:

SELECT Colaborador.IdColaborador, Colaborador.Numero, Colaborador.Nome, Colaborador.[Centro de Custo], Colaborador.Usuario,
Colaborador.Responsavel, Colaborador_1.Nome FROM Colaborador,
Colaborador AS Colaborador_1 WHERE
(((Colaborador_1.IdColaborador)=[Colaborador].[IdColaborador]));

Where I want to get the name of the responsible person who is also a Collaborator but the result is the name of the Collaborator repeated 2 times.

What I’m doing wrong ?

sql ms-access

  • What is the mistake you get?

  • I get the name of the first collaborator always, I do not receive your responsible.

  • You don’t have a Idresponsavel?

  • Responsible is Idresponsavel, who is also Idcollaborator

1 answer

3


Surely you’re not making the relationship right.

See in your Where you do:

(((Colaborador_1.IdColaborador)=[Colaborador].[IdColaborador]))

This will make a JOIN with the same data as your collaborator, what you need and check the Responsible ID to make the relationship.

  SELECT Colaborador.IdColaborador, Colaborador.Numero, Colaborador.Nome, Colaborador.[Centro de Custo], Colaborador.Usuario,
Colaborador.Responsavel, Colaborador_1.Nome FROM Colaborador,
Colaborador AS Colaborador_1 WHERE
(((Colaborador_1.IdColaborador)=[Colaborador].[IdResponsavel]));

  • This is for use in Access and gives the following error: Syntax error in FROM clause.

  • Ok, but surely you have in your table a responsible id, so in your select you have to exchange your relationship for that id type (((Colaborador_1.IdColaborador)=[Colaborador].[IdResponsavel]));

  • I changed the answer, what you have to understand is that the data of the first Collaborator is the main data and in them contains the secondary key FK that is the Idresponsavel you have to use that id in your relationship.

Browser other questions tagged sql ms-access

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