Excluding fields from group by in a subquery

Question

EDIT

I made the query work with the help of Hockenberry! Thanks. But i have one more problem. My timeplan table structure is in fact as follows.

Material no|Production time|Production place
12345|2,10|Robot
12345|7,40|Machining
67890|2,34|Machining
34567|9,93|Manuel

My query just takes the time from timeplan table if it matches the production place from table report.

i want query result to add second production place's production time to the sum grouping by [Production date],[Production place],[Shift]

in other words i dont want to group the query by Report.ID, Report.[Creation date], Report.[Responsible] and Report.[Number of Workers], but Timeplan.[Production place]. But i want this results on the row thats being taken from report table.

 SELECT *, 

 (TotalInterruptDuration + TotalReworkDuration + TotalOvertimeDuration) AS TotalSum

 FROM (
SELECT 
    Report.ID, 
    Report.[Creation date], 
    Report.[Production date], 
    Report.[Production place],
    Report.[Shift], 
    Report.[Responsible],
    Report.[Number of workers],

    (SELECT Sum(Interrupts.[Interrupt duration]) 
     FROM Interrupts
     WHERE Interrupts.ID=Report.ID) AS TotalInterruptDuration,

    (SELECT Sum([Rework].[Rework duration]) 
     FROM [Rework]
     WHERE [Rework].ID=Report.ID) AS TotalReworkDuration, 

    (SELECT Sum(Overtime.[Overtime duration]) 
     FROM Rework
     WHERE Rework.ID=Report.ID) AS TotalOvertimeDuration,

    Year([Report]![Production date]) AS Year, 
    DatePart("ww",[Report]![Production date]-1) AS Week,   
    Month(Report![Production date]) AS Month,

    Sum(Quantity.[Quantity] * Timeplan.[Production.time]) AS CalcValue

FROM (Report 

     INNER JOIN Quantity ON Quantity.ID = Report.ID)
     INNER JOIN Timetable ON Report.[Production place] = Timetable.[Production place] AND Quantity.[Material no] = Timeplan.[Material no]
     GROUP BY Report.ID, Report.[Creation date], Report.[Production date],  Report.[Production place], Report.[Shift], Report.[Responsible], Report.     [Number of workers])
  tmp

..............................................

First of all sorry for my bad English. I have a query as follows.

SELECT 
    Report.ID, 
    Report.[Creation date], 
    Report.[Production date], 
    Report.[Production place],
    Report.[Shift], 
    Report.[Responsible],
    Report.[Number of workers],

    (SELECT Sum(Interrupts.[Interrupt duration]) 
     FROM Interrupts
     WHERE Interrupts.ID=Report.ID) AS TotalInterruptDuration,

    (SELECT Sum([Rework].[ Rework duration]) 
     FROM [Rework]
     WHERE [Rework].ID=Report.ID) AS TotalReworkDuration, 

    (SELECT Sum(Overtime.[Overtime duration]) 
     FROM Overtime
     WHERE Overtime.ID=Report.ID)  AS TotalOvertimeDuration,

    Year([Report]![Production date]) AS Year, 
    DatePart("ww",[Report]![Production date]-1) AS Week,  
    Month(Report![Production date]) AS Month

FROM Report

My first question is I would like to Sum TotalInterruptDuration, TotalReworkDuration, TotalOvertimeDuration in the same row.

And second question which far more important is; I want to join Report and Quantity table if ID matches and multiply Report.[Number of Workers] with sum of [Production time] derived from corresponding [Material no] by joining [Quantity] and [Timeplan] tables.

For example i want to look table quantity for id(1) and then multiply production time of material 12345 with 10(quantity) and production time of 67890 and multiply it with 11(quantity) and production time of 34567 and multiply it with 7 (quantity) and sum values and print in the query.

My tables look like this.

    **Table Report**

    *ID     Creation date   Production date Production place    Shift   Responsible Number of workers*


    1   01.01.2015  01.01.2015      Robot           2   Omer        12

    2   02.01.2015  02.01.2015      Robot           3   Erdem       15

    3   03.01.2015  03.01.2015      Machining       2   Sukru       4

    4

    **Table Quantity**


    *ID Quantity    Material No*

    1   10      12345

    1   11      67890

    1   7       34567

    2   3       12345

    3   6       67890

    3   6       34567

4   5       12345

    **Table Timeplan**

        *Material No    Production Time*

        12345        34

        67890        11

        34567        21

score 1 · Accepted Answer · edited May 23 '17 at 12:28

Edit on Question 1:

SELECT *, 
    TotalSum = (TotalInterruptDuration + TotalReworkDuration + TotalOvertimeDuration)
FROM (
    SELECT 
        Report.ID, 
        Report.[Creation date], 
        Report.[Production date], 
        Report.[Production place],
        Report.[Shift], 
        Report.[Responsible],
        Report.[Number of workers],

        (SELECT Sum(Interrupts.[Interrupt duration]) 
         FROM Interrupts
         WHERE Interrupts.ID=Report.ID) AS TotalInterruptDuration,

        (SELECT Sum([Rework].[ Rework duration]) 
         FROM [Rework]
         WHERE [Rework].ID=Report.ID) AS TotalReworkDuration, 

        (SELECT Sum(Overtime.[Overtime duration]) 
         FROM Overtime
         WHERE Overtime.ID=Report.ID)  AS TotalOvertimeDuration,

        Year([Report]![Production date]) AS Year, 
        DatePart("ww",[Report]![Production date]-1) AS Week,  
        Month(Report![Production date]) AS Month

    FROM Report
    ) tmp

Question 2:

SELECT
     Report.*       -- all rows from table Report
--  ,Quantity.*     -- all rows from table Quantity
--  ,Timeplan*      -- all rows from table Timeplan

    ,CalcValue = Report.[Number of Workers] * Timeplan.[Production Time]

FROM Report
INNER JOIN Quantity ON Quantity.ID = Report.ID
INNER JOIN Timeplan ON TimePlan.[Material No] = Quantity.[Material No]

Remove th -- if you need to see the columns from the Quantity or Timeplan tables. The Reporttable just shows the rows which matches the Quantity-table AND the Timeplan-table.

EDIT Both queries combined.

SELECT *, 
    TotalSum = (TotalInterruptDuration + TotalReworkDuration + TotalOvertimeDuration)
FROM (
    SELECT 
        Report.ID, 
        Report.[Creation date], 
        Report.[Production date], 
        Report.[Production place],
        Report.[Shift], 
        Report.[Responsible],
        Report.[Number of workers],

        (SELECT Sum(Interrupts.[Interrupt duration]) 
         FROM Interrupts
         WHERE Interrupts.ID=Report.ID) AS TotalInterruptDuration,

        (SELECT Sum([Rework].[ Rework duration]) 
         FROM [Rework]
         WHERE [Rework].ID=Report.ID) AS TotalReworkDuration, 

        (SELECT Sum(Overtime.[Overtime duration]) 
         FROM Overtime
         WHERE Overtime.ID=Report.ID)  AS TotalOvertimeDuration,

        Year([Report]![Production date]) AS Year, 
        DatePart("ww",[Report]![Production date]-1) AS Week,  
        Month(Report![Production date]) AS Month,

        Report.[Number of Workers] * Timeplan.[Production Time] AS CalcValue
    FROM
         (Report
         INNER JOIN Quantity 
         ON Quantity.ID = Report.ID)
         INNER JOIN Timeplan 
         ON TimePlan.[Material No] = Quantity.[Material No]
    ) tmp

EDIT updated the join for access. Please note the access-join conditions: Multiple INNER JOIN SQL ACCESS

Please note the access join conditions. You can find an example here (but i hope my last update may help you). http://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access — Hockenberry, Feb 20 '15 at 09:33
If your question was successfully answered, you should close the question and create a new one. If you start a new question, it's on the top of the currently Open questions and you will draw more attention from more people. If you edit your (already answered) question, it won't draw so much attention. But i'm going to look for your new question when i'm free (i think this will be in one hour. Or two.). — Hockenberry, Feb 21 '15 at 19:14

Excluding fields from group by in a subquery

1 Answers1