cumulative sum by date&time

[TriFith]

Hello everyone,


I stucked on a problem in my next project and I would like to ask you for help...
I need to make cumulative sum query to make diagram of "fail rate" in time.


Following table is a result of my first querry which is filtering "ComponentName" from all entries and sorting data by "ProductionDate":

ProductionDate	ComponentName	Picks	Placements	MachineName
4. 11. 2013 12:45:05	026/000	23	22	HSC
4. 11. 2013 13:00:02	026/000	18	18	HSC
4. 11. 2013 13:15:02	026/000	26	26	HSC
4. 11. 2013 13:30:03	026/000	18	18	HSC
4. 11. 2013 13:45:02	026/000	6	6	HSC
4. 11. 2013 14:00:02	026/000	12	12	HSC
4. 11. 2013 14:15:03	026/000	6	6	HSC
4. 11. 2013 14:30:03	026/000	52	52	HSC
4. 11. 2013 14:45:03	026/000	38	38	HSC
4. 11. 2013 14:58:02	026/000	36	36	HSC
4. 11. 2013 14:59:02	026/000	6	6	HSC
4. 11. 2013 15:15:03	026/000	18	18	HSC
4. 11. 2013 15:30:03	026/000	42	42	HSC
4. 11. 2013 15:45:03	026/000	30	30	HSC

Now I need to add three new columns which should contain cumulative sum of "Picks" and "Placements". Last column will be used to count fail rate.
I know I have to use DSum function to make cumulative sum, but I have problems with condition of cumulative sum...

CumSumPicks: DSum("Picks";"dbo_PickPlaceEntry";"[ProductionDate]<="???????????)

Can you advice please?

Thank you very much.

[pbaldy]

Use the form syntax, but just use the field name in place of the form reference:

DLookup Usage Samples

[Dal Jeanis]

Assuming this table layout

tblPickPlace
  ProdDate     Date
  CompName     Text
  MachName     Text
  Picks        Number
  Places       Number

This query gives you cumulative picks and places

SELECT 
  T1.ProdDate,
  T1.CompName,
  T1.MachName,
  First(T1.Picks) As Picks,
  First(T1.Places) As Places,
  Sum(T2.Picks) AS CumPicks,
  Sum(T2.Places) AS CumPlaces  
FROM
   tblPickPlace AS T1 
   INNER JOIN
   tblPickPlace AS T2
   ON T1.CompName = T2.CompName
   AND T1.MachName = T2.MachName
WHERE
   T2.ProdDate <= T1.ProdDate
GROUP BY 
  T1.ProdDate,
  T1.CompName,
  T1.MachName;

But I don't know where you're getting fail rate, unless it's the difference between picks and places.
FYI - I made the assumption that you want to compare fails only against work done on the same machine.

[TriFith]

Use the form syntax, but just use the field name in place of the form reference:

DLookup Usage Samples

Thank you for answer, I tried all of this syntax and none of this is working
Can you advice me what exact condition should be there? I am not sure ProductionDate <= ProductionDate condition is OK

[TriFith]

Assuming this table layout

tblPickPlace
  ProdDate     Date
  CompName     Text
  MachName     Text
  Picks        Number
  Places       Number

This query gives you cumulative picks and places

SELECT 
  T1.ProdDate,
  T1.CompName,
  T1.MachName,
  First(T1.Picks) As Picks,
  First(T1.Places) As Places,
  Sum(T2.Picks) AS CumPicks,
  Sum(T2.Places) AS CumPlaces  
FROM
   tblPickPlace AS T1 
   INNER JOIN
   tblPickPlace AS T2
   ON T1.CompName = T2.CompName
   AND T1.MachName = T2.MachName
WHERE
   T2.ProdDate <= T1.ProdDate
GROUP BY 
  T1.ProdDate,
  T1.CompName,
  T1.MachName;

But I don't know where you're getting fail rate, unless it's the difference between picks and places.
FYI - I made the assumption that you want to compare fails only against work done on the same machine.

Fail rate will be calculated like (Picked-placed)/(picked)
And also in a reference control there are machine choosed by user...
Unfortunatelly your query is not working on my database

I have modified names, because you changed some of them, but it is not working... Access is calling many errors.
Any thoughts?

[Dal Jeanis]

Post the names of your tables and fields, please.

[pbaldy]

"ProductionDate <= #" & ProductionDate & "#"

[TriFith]

"ProductionDate <= #" & ProductionDate & "#"

CumSum: DSum("Picks";"dbo_PickPlaceEntries";"ProductionDate <= #" & [ProductionDate] & "#")

This is not working. As a result there is a field "CumSum" and every value is #error

[TriFith]

Post the names of your tables and fields, please.

Original tables are:

dbo_PickPlaceEntries:
ID - automatic number
MachineID - number (foreign key to dbo_Machines)
ComponentRID - number (foreign key to dbo_Components)
ProductionDate - date&time
Picks - number
Placements - number

dbo_Components:
ID - automatic number
ComponentName - text


dbo_Machines:
ID - automatic number
MachineName - text


In a form I am using text boxes to define dates as a criterium for ProductionDate (first and last day), ComponentName and MachineName.

[Dal Jeanis]

Try This:

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,
  (Sum(T2.Picks) -  Sum(T2.Placements))/Sum(T2.Picks) AS CumFail
FROM
   ((dbo_PickPlaceEntry AS T1 
    INNER JOIN
    dbo_PickPlaceEntry AS T2
    ON T1.ComponentRID = T2.ComponentRID
    AND T1.MachineID = T2.MachineID)
      INNER JOIN dbo_Components AS TC
      ON TC.ID = T1.ComponentRID)
        INNER JOIN dbo_Machines AS TM
        ON TM.ID = T1.MachineID
WHERE
   T2.ProductionDate <= T1.ProductionDate
GROUP BY 
  TC.ComponentName,
  TM.MachineName,
  T1.ProductionDate;

If that works, you can add another test to the WHERE clause to limit the results to the desired ComponentName or ComponentID and MachineName or
MachineID.

WHERE T2.ProductionDate <= T1.ProductionDate
AND TC.ComponentName = Forms!MyForm!MyComponentControl
AND TC.MachineName = Forms!MyForm!MyMachineControl

[pbaldy]

Can you post the db here?

[TriFith]

I had to upload DB to free storage, because file uploading on this site is not working to me

http://ulozto.net/xtNEYNPD/temp-accdb

I deleted from DB many datas to reduce it.
There is only one querry, where I need to make cumulative sum...

Thank you once more.

Edit:
I´ve just find out, why I am not able to upload DB on site... My DB is too large :-D

[Dal Jeanis]

Have you tried the code in post #10?

[TriFith]

Have you tried the code in post #10?

Of course I did, but Access is still asking me about parametr entry (TC.ComponentName and other...)
You can download piece of db and try your querry if you want.

[Dal Jeanis]

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.