Pls Help...

[nisa]

Hi.

I've recently tried to create a quey which would give me the cases I'm working with, the name of the employee, and how many people the employee has "recruited" for the case.

But for some odd reason the query shows only 5 employees out of 23, and they just loop them over and over.

My SQL looks like this:

SELECT DISTINCTROW Arbejdsseddeltimer.ProjektId, Arbejdsseddeltimer.ArbejdsseddelId, Medarbejdere.MedarbejderId, Medarbejdere.Fornavn, Medarbejdere.Efternavn, Sum(Arbejdsseddeltimer.Antal_Rek) AS [Sum Of Antal_Rek]
FROM Medarbejdere INNER JOIN (Arbejdsseddeltimer INNER JOIN Arbejdssedler ON Arbejdsseddeltimer.Antal_Rek = Arbejdssedler.Antal_Rek) ON Medarbejdere.MedarbejderId = Arbejdssedler.MedarbejderId
GROUP BY Arbejdsseddeltimer.ProjektId, Arbejdsseddeltimer.ArbejdsseddelId, Medarbejdere.MedarbejderId, Medarbejdere.Fornavn, Medarbejdere.Efternavn;

A little translation for some of the words:
Arbejdsseddeltimer: This means the amount of hours spend on a specific worktable.
ArbejdsseddelId: This is the Id number for the specific employee's worktable.
Efternavn: Last name
Fornavn: Surname
Medarbejdere: Employees
MedarbejderId: The Id number for a specific employee.
ProjektId: The Id number for a specific project



Any ideas on this?

 

[Guus2005]

Perhaps you should use a LEFT or RIGHT OUTER JOIN.
The fact that 5 persons keep popping up looks like you have one table not joined. Odd. Seems that's not the case. Well, hope this helps.

 

[raskew]

Hi -
Based on your translation, here's a cut-down version of your code, which may be a little easier to view. Other than reducing long table names and substituting translated fields, no other changes have been made.

SELECT  DISTINCT
    Arbe.ProjektId
  , Arbe.WorktableID
  , Meda.EmployeeID
  , Meda.SurName
  , Meda.LastName
  , Sum(Arbe.Antal_Rek) AS [Sum Of Antal_Rek]
FROM
   Meda
INNER JOIN
   (Arbe
INNER JOIN
   Arbe
ON
   Arbe.Antal_Rek = Arbe.Antal_Rek) 
ON
   Meda.EmployeeID = Arbe.EmployeeID
GROUP BY
   Arbe.ProjektId
  , Arbe.EmployeeID
  , Meda.EmployeeID
  , Meda.Surname
  , Meda.LastName;

Don't have a solution, but the two INNER JOINS, one after another, looks suspect.

Bob

 

[nisa]

It's all generated with the Guide, so if there's something that looks suspect, then it's a bug in Access, which have made me do it

 

[nisa]

Perhaps you should use a LEFT or RIGHT OUTER JOIN.
The fact that 5 persons keep popping up looks like you have one table not joined. Odd. Seems that's not the case. Well, hope this helps.

Tried to add a RIGHT OUTER JOIN insted of the second INNER JOIN, and it sort of worked, but now I get all the employees, but not all of them is under the cases where they have been set to work...

 

[nisa]

I've tried something new, but now it complains that there's something wrong in the SQL or JOIN

SELECT Arbejdsseddeltimer.ProjektId, Projekter.MedarbejderId, Medarbejdere.Fornavn, Medarbejdere.Efternavn, Arbejdsseddeltimer.Antal_Rek
FROM (Medarbejdere RIGHT JOIN (Projekter INNER JOIN Arbejdsseddeltimer ON Projekter.ProjektId = Arbejdsseddeltimer.ProjektId) ON Medarbejdere.MedarbejderId = Projekter.MedarbejderId) INNER JOIN Arbejdssedler ON (Medarbejdere.MedarbejderId = Arbejdssedler.MedarbejderId) AND (Arbejdsseddeltimer.ArbejdsseddelId = Arbejdssedler.ArbejdsseddelId) AND (Arbejdsseddeltimer.Antal_Rek = Arbejdssedler.Antal_Rek);

 

[Guus2005]

Post your database or a subset showing the problem.

 

[nisa]

a subset? I'm not quite sure what that is...

 

[ajetrumpet]

But for some odd reason the query shows only 5 employees out of 23, and they just loop them over and over.

This happens quite a bit when working with JOINS. Like you didn't know that...

From your first post...

SELECT DISTINCTROW Arbejdsseddeltimer.ProjektId, Arbejdsseddeltimer.ArbejdsseddelId, 
Medarbejdere.MedarbejderId, Medarbejdere.Fornavn, 
Medarbejdere.Efternavn, Sum(Arbejdsseddeltimer.Antal_Rek) AS [Sum Of Antal_Rek]

[COLOR="Red"]FROM Medarbejdere INNER JOIN 
(Arbejdsseddeltimer INNER JOIN Arbejdssedler ON 
Arbejdsseddeltimer.Antal_Rek = Arbejdssedler.Antal_Rek) ON 
Medarbejdere.MedarbejderId = Arbejdssedler.MedarbejderId[/COLOR]

GROUP BY Arbejdsseddeltimer.ProjektId, Arbejdsseddeltimer.ArbejdsseddelId, 
Medarbejdere.MedarbejderId, Medarbejdere.Fornavn, Medarbejdere.Efternavn;

The SQL in RED suggests that you have more than a simple one-to-many relationship involved between the two tables being queried. Say for example, I have a table that has a PK and an FK in another table. The FK table also has other fields that are part of a "many" side to relationships (or the FK from the first table is a PK for another relationship). This doubling of joins in the FK table field gives you a duplication of records.

Maybe you can post a screenshot of your relationship window??

Also, DISTINCTROW rarely does anything for you (and is rarely needed) - but what it does do can be found specifically in the help menu; might want to change it to DISTINCT instead.

 

[Guus2005]

a subset? I'm not quite sure what that is...

A subset is part of your database, it is usually smaller. It is important that this smaller part of your database shows the problem.

 

[nisa]

I found the problem, and as said by ajetrumpet, then the problem was simply my joins. By errasing one of the joins, then I made it work as it should... But thx for the help guys!