Reducing SQL code in ms access using a loop

Question

I want to automize a SQL query in MS Access. I am new to vba and it would be great if someone can help me with my problem:

I need to generate a list for my company with each employee who has worked for a specific team. Each team gets a list that includes the working hours that have been done for a specific project. Here is the sql-code for one team:

SELECT 
    Sum(b.Stunden) AS Gesamtstunden, 
    b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, 
    Team.Teamleader, b.Positionen.Position_ID, b.Positionen.Art
FROM
    (SELECT * 
     FROM 
          (SELECT * 
           FROM Leistungen 
           INNER JOIN Kostengefässe ON Leistungen.Kostengefäss_ID = Kostengefässe.Kostengefäss_ID) AS a 
     INNER JOIN 
         Positionen ON a.Position_ID = Positionen.Position_ID) AS b 
INNER JOIN 
    Team ON b.Team_ID = Team.Team_ID
WHERE 
    (((b.Mitarbeiter) = 'HBI') AND (Team.Team_ID) = '2_RR1')
GROUP BY 
    b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, 
    b.Positionen.Position_ID, b.Positionen.Art

UNION

SELECT 
    Sum(b.Stunden) AS Gesamtstunden, 
    b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, 
    b.Positionen.Position_ID, b.Positionen.Art
FROM
    (SELECT * 
     FROM 
         (SELECT * 
          FROM Leistungen 
          INNER JOIN Kostengefässe ON Leistungen.Kostengefäss_ID = Kostengefässe.Kostengefäss_ID) AS a 
     INNER JOIN 
         Positionen ON a.Position_ID = Positionen.Position_ID) AS b 
INNER JOIN 
    Team ON b.Team_ID = Team.Team_ID
WHERE 
    (((b.Mitarbeiter) = 'LBR') AND (Team.Team_ID) = '2_RR1')
GROUP BY 
    b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, 
    b.Positionen.Position_ID, b.Positionen.Art

UNION

SELECT 
    Sum(b.Stunden) AS Gesamtstunden, 
    b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, 
    b.Positionen.Position_ID, b.Positionen.Art
FROM 
    (SELECT * 
     FROM 
         (SELECT * 
          FROM Leistungen 
          INNER JOIN Kostengefässe ON Leistungen.Kostengefäss_ID = Kostengefässe.Kostengefäss_ID) AS a 
     INNER JOIN 
         Positionen ON a.Position_ID = Positionen.Position_ID) AS b 
INNER JOIN 
    Team ON b.Team_ID = Team.Team_ID
WHERE 
    (((b.Mitarbeiter) = 'PJO') AND (Team.Team_ID) = '2_RR1')
GROUP BY 
    b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, 
    b.Positionen.Position_ID, b.Positionen.Art

As you can see, I have multiple queries which I unite using the UNION. I want to reduce these queries using a loop for one single sql with the employee as a variable and loop through all employees. Any ideas?

sagi · Accepted Answer · 2016-01-12 12:13:59Z

1

You can simply use the IN condition instead of '='.

SELECT Sum(b.Stunden) AS Gesamtstunden, b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, b.Positionen.Position_ID, b.Positionen.Art
FROM (SELECT * FROM (SELECT * FROM Leistungen INNER JOIN Kostengefässe ON Leistungen.Kostengefäss_ID = Kostengefässe.Kostengefäss_ID)  AS a INNER JOIN Positionen ON a.Position_ID = Positionen.Position_ID)  AS b INNER JOIN Team ON b.Team_ID = Team.Team_ID
WHERE (((b.Mitarbeiter) in('HBI','LBR','PJO')) And (Team.Team_ID)='2_RR1')
GROUP BY b.Mitarbeiter, b.Leistungen.Kostengefäss_ID, Team.Teamleader, b.Positionen.Position_ID, b.Positionen.Art

Your code was hard to read, so I hope I saw all the differences between those 3 queries ('HBI','LBR','PJO')? BTW - you use a lot of unnecessary '()'

answered Jan 12, 2016 at 12:13

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reducing SQL code in ms access using a loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related