cumulative sum by date&time

[TriFith]

I assumed it was dbo_components with an underscore. Is that a space between dbo and components? If not, I'll have to try downloading at home tomight.

Of course, there is an underscore I am not using spaces too much...

[Dal Jeanis]

k. I'll see if I can cut some time this weekend to review.

[Dal Jeanis]

This gets a reasonable result.

SELECT 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate,
  First(T1.Picks) As Picks,
  First(T1.Placements) As Places,
  Sum(T2.Picks) AS CumPicks,
  Sum(T2.Placements) AS CumPlaces,
  Round((Sum(T2.Picks) -  Sum(T2.Placements))/Sum(T2.Picks),4) AS CumFail
FROM
   ((dbo_PickPlaceEntries AS T1 
    INNER JOIN
    dbo_PickPlaceEntries AS T2
    ON T1.ComponentRID = T2.ComponentRID
    AND T1.MachineRID = T2.MachineRID)
      INNER JOIN dbo_Components AS TC
      ON TC.RID = T1.ComponentRID)
        INNER JOIN dbo_Machines AS TM
        ON TM.RID = T1.MachineRID
WHERE T2.ProductionDate <= T1.ProductionDate
  AND T1.ProductionDate between #3/1/2013# AND #3/31/2013#
  AND T2.ProductionDate between #3/1/2013# AND #3/31/2013#
  AND T1.ComponentRID = 76
GROUP BY 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate;

You can replace the #3/1/2013#, #3/31/2013#, and 76 with the names of your form fields and execute the query from the form, and it should get you your desired results.

[TriFith]

Hello again guys...
I´ve just find out how to implement cumulative summary function in a querry...

So:
First step: In first querry I picked entries from-to date and for specific item and also I´ve sorted them by ProductionDate (format Date&Time)

SELECT dbo_PickPlaceEntries.ProductionDate, dbo_Components.ComponentName, dbo_Machines.MachineName, dbo_PickPlaceEntries.Picks, dbo_PickPlaceEntries.PlacementsFROM (dbo_PickPlaceEntries INNER JOIN dbo_Components ON dbo_PickPlaceEntries.ComponentRID = dbo_Components.RID) INNER JOIN dbo_Machines ON dbo_PickPlaceEntries.MachineRID = dbo_Machines.RID
WHERE (((dbo_PickPlaceEntries.ProductionDate) Between [Forms]![frmStart]![Datum_od] And [Forms]![frmStart]![Datum_do]) AND ((dbo_Components.ComponentName)=[Forms]![frmStart]![Item]))
ORDER BY dbo_PickPlaceEntries.ProductionDate;

Second step: Next querry will calculate cumulative summary for data picked by previous querry.

SELECT *, (SELECT sum(Picks) FROM qryPickComponentData WHERE ProductionDate <= tmp.ProductionDate) AS CumulativeSumPicks, (SELECT sum(Placements) FROM qryPickComponentData Where ProductionDate <= tmp.ProductionDate) AS CumulativeSumPlacements
FROM qryPickComponentData AS tmp;

(as a third step I just calculated failrate like:

FailRate: (CumulativeSumPicks-CumulativeSumPlacements)/CumulativeSumPicks

However...
The key is to make sub-querry instead of DSum function in added fields...
Unfortunatelly, this way is seriously time consuming! For every line, two more querries has to be done. For 40 entries it took me about 3 minutes to get a results and I need to perform this analysis for more than 1000 entries

I am going to try a few more ways how to implement this function.

At this time I would like to thank you all for help

Cheers TriFith

[Dal Jeanis]

Okay, so here's your three queries all in one query.

SELECT 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate,
  First(T1.Picks) As Picks,
  First(T1.Placements) As Places,
  Sum(T2.Picks) AS CumPicks,
  Sum(T2.Placements) AS CumPlaces,
  Round((Sum(T2.Picks) -  Sum(T2.Placements))/Sum(T2.Picks),4) AS CumFail
FROM
   ((dbo_PickPlaceEntries AS T1 
    INNER JOIN
    dbo_PickPlaceEntries AS T2
    ON T1.ComponentRID = T2.ComponentRID
    AND T1.MachineRID = T2.MachineRID)
      INNER JOIN dbo_Components AS TC
      ON TC.RID = T1.ComponentRID)
        INNER JOIN dbo_Machines AS TM
        ON TM.RID = T1.MachineRID
WHERE (T2.ProductionDate <= T1.ProductionDate)
  AND (T1.ProductionDate between [Forms]![frmStart]![Datum_od]  AND [Forms]![frmStart]![Datum_do])
  AND (T2.ProductionDate between [Forms]![frmStart]![Datum_od]  AND [Forms]![frmStart]![Datum_do])
  AND (TC.ComponentName = [Forms]![frmStart]![Item])
GROUP BY 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate;

If you want to include cumulative fails from prior to the start of the period, then that requires a change to this line, for example by subtracting 30 days from the start date:

  AND (T2.ProductionDate between [Forms]![frmStart]![Datum_od]  AND [Forms]![frmStart]![Datum_do])

[TriFith]

Okay, so here's your three queries all in one query.

SELECT 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate,
  First(T1.Picks) As Picks,
  First(T1.Placements) As Places,
  Sum(T2.Picks) AS CumPicks,
  Sum(T2.Placements) AS CumPlaces,
  Round((Sum(T2.Picks) -  Sum(T2.Placements))/Sum(T2.Picks),4) AS CumFail
FROM
   ((dbo_PickPlaceEntries AS T1 
    INNER JOIN
    dbo_PickPlaceEntries AS T2
    ON T1.ComponentRID = T2.ComponentRID
    AND T1.MachineRID = T2.MachineRID)
      INNER JOIN dbo_Components AS TC
      ON TC.RID = T1.ComponentRID)
        INNER JOIN dbo_Machines AS TM
        ON TM.RID = T1.MachineRID
WHERE (T2.ProductionDate <= T1.ProductionDate)
  AND (T1.ProductionDate between [Forms]![frmStart]![Datum_od]  AND [Forms]![frmStart]![Datum_do])
  AND (T2.ProductionDate between [Forms]![frmStart]![Datum_od]  AND [Forms]![frmStart]![Datum_do])
  AND (TC.ComponentName = [Forms]![frmStart]![Item])
GROUP BY 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate;

If you want to include cumulative fails from prior to the start of the period, then that requires a change to this line, for example by subtracting 30 days from the start date:

  AND (T2.ProductionDate between [Forms]![frmStart]![Datum_od]  AND [Forms]![frmStart]![Datum_do])

WOW!!!
Gratulations :-D it´s working much more faster than my separated querries. Tried to run querry for 282 entries and I have a result after 5
seconds. Great result!

Thank you

[Dal Jeanis]

Happy to be of service. If it's working, please mark thread solved. top of page, under thread tools.