Cumulative totals from individual records

R

Richard

I have labor data for employees given by hours worked on a given day.
Eg. Tom, 5-hrs, 5-Jan-2001
What I need is cumulative hours worked by month:
E.g. Tom, 100-hours-to-date, Jan-2001

How can I use Access to generate cumulative-hours-to-date by month from
hours-worked-on-given-days?

Note: It’s something I could get from Excel Pivot table, but I have well
over 60,000 records (in the millions).
 
R

Richard

Allen,
I'm comfused by the example you referenced. It includes the code below.
However, I can't figure out the difference between [Orders] and [Order
Details]. Are there two tables (I only have one)? Or is [Order Details] the
'dummy' table that is really just [Orders], and if so is there an error in
the syntax below?

Also, what if I want a sum-to-date that spans multiple years of data?
Richard

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD

FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID =
OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)

AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),

Month([Orders].[OrderDate]) + 1, 1))
AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
 
A

Allen Browne

Yes: Orders and Order Details are 2 tables. The Orders table holds the
header info (order number, date, client), and the Order Details holds the
line items (quantity, product, unitprice), since one order can contain many
rows. To see an example, open the Northwind sample database that installs
with Access.

The 2nd part of the question may become clearer after you see the example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
Allen,
I'm comfused by the example you referenced. It includes the code below.
However, I can't figure out the difference between [Orders] and [Order
Details]. Are there two tables (I only have one)? Or is [Order Details]
the
'dummy' table that is really just [Orders], and if so is there an error in
the syntax below?

Also, what if I want a sum-to-date that spans multiple years of data?
Richard

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS
MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD

FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID =
OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)

AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),

Month([Orders].[OrderDate]) + 1, 1))
AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
--
Richard


Allen Browne said:
Use a totals query to get the totals for a period (e.g. a month.)

Then use a subquery to get the progressive total.

The Year-to-date example should illustrate how:
http://allenbrowne.com/subquery-01.html#YTD
Click to expand...
Click to expand...
 
R

Richard

Allen,
Can you tell me how the year-to-date would be written if all the data were
in just one table, like the original example I sited where on row looks like:

EMPLOYEE HOURS-WORKED DATE
Tom 5 5-Jan-2001
--
Richard


Allen Browne said:
Yes: Orders and Order Details are 2 tables. The Orders table holds the
header info (order number, date, client), and the Order Details holds the
line items (quantity, product, unitprice), since one order can contain many
rows. To see an example, open the Northwind sample database that installs
with Access.

The 2nd part of the question may become clearer after you see the example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
Allen,
I'm comfused by the example you referenced. It includes the code below.
However, I can't figure out the difference between [Orders] and [Order
Details]. Are there two tables (I only have one)? Or is [Order Details]
the
'dummy' table that is really just [Orders], and if so is there an error in
the syntax below?

Also, what if I want a sum-to-date that spans multiple years of data?
Richard

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS
MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD

FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID =
OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)

AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),

Month([Orders].[OrderDate]) + 1, 1))
AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
--
Richard


Allen Browne said:
Use a totals query to get the totals for a period (e.g. a month.)

Then use a subquery to get the progressive total.

The Year-to-date example should illustrate how:
http://allenbrowne.com/subquery-01.html#YTD

I have labor data for employees given by hours worked on a given day.
Eg. Tom, 5-hrs, 5-Jan-2001
What I need is cumulative hours worked by month:
E.g. Tom, 100-hours-to-date, Jan-2001

How can I use Access to generate cumulative-hours-to-date by month from
hours-worked-on-given-days?

Note: It’s something I could get from Excel Pivot table, but I have
well
over 60,000 records (in the millions).
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Create this query, and save as (say) qryMonthHours:
SELECT Employee,
Year([Date]) AS TheYear,
Month([Date]) AS TheMonth,
Sum([Hours-Worked]) AS MonthHours,
FROM [Table1]
GROUP BY Employee, Year([Date]), Month([Date]);

Then create another query using qryMonthHours as an input "table", and type
this into the Field row in query design:
YrToDate: (SELECT Sum([MonthHours] AS YTD
FROM qryMonthHoursAS Dupe
WHERE Dupe.Employee = qryMonthHours.Employee
AND Dupe.TheYear = qryMonthHours.TheYear
AND Dupe.TheMonth <= qryMonthHours.TheMonth)

Substitute your table name for Table1 in the first query. Hopefully that
will give correct results even though the DATE field name is a reserved
word.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
Allen,
Can you tell me how the year-to-date would be written if all the data were
in just one table, like the original example I sited where on row looks
like:

EMPLOYEE HOURS-WORKED DATE
Tom 5 5-Jan-2001
--
Richard


Allen Browne said:
Yes: Orders and Order Details are 2 tables. The Orders table holds the
header info (order number, date, client), and the Order Details holds the
line items (quantity, product, unitprice), since one order can contain
many
rows. To see an example, open the Northwind sample database that installs
with Access.

The 2nd part of the question may become clearer after you see the
example.

Richard said:
Allen,
I'm comfused by the example you referenced. It includes the code below.
However, I can't figure out the difference between [Orders] and [Order
Details]. Are there two tables (I only have one)? Or is [Order Details]
the
'dummy' table that is really just [Orders], and if so is there an error
in
the syntax below?

Also, what if I want a sum-to-date that spans multiple years of data?
Richard

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS
MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD

FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID =
OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)

AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),

Month([Orders].[OrderDate]) + 1, 1))
AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
--
Richard


:

Use a totals query to get the totals for a period (e.g. a month.)

Then use a subquery to get the progressive total.

The Year-to-date example should illustrate how:
http://allenbrowne.com/subquery-01.html#YTD

I have labor data for employees given by hours worked on a given day.
Eg. Tom, 5-hrs, 5-Jan-2001
What I need is cumulative hours worked by month:
E.g. Tom, 100-hours-to-date, Jan-2001

How can I use Access to generate cumulative-hours-to-date by month
from
hours-worked-on-given-days?

Note: It’s something I could get from Excel Pivot table, but I have
well
over 60,000 records (in the millions).
Click to expand...
Click to expand...
Click to expand...
 
R

Richard

Allen,
Thanks for being patient with me.
Last iteration worked perfect (after minor corrections)


--
Richard


Allen Browne said:
Create this query, and save as (say) qryMonthHours:
SELECT Employee,
Year([Date]) AS TheYear,
Month([Date]) AS TheMonth,
Sum([Hours-Worked]) AS MonthHours,
FROM [Table1]
GROUP BY Employee, Year([Date]), Month([Date]);

Then create another query using qryMonthHours as an input "table", and type
this into the Field row in query design:
YrToDate: (SELECT Sum([MonthHours] AS YTD
FROM qryMonthHoursAS Dupe
WHERE Dupe.Employee = qryMonthHours.Employee
AND Dupe.TheYear = qryMonthHours.TheYear
AND Dupe.TheMonth <= qryMonthHours.TheMonth)

Substitute your table name for Table1 in the first query. Hopefully that
will give correct results even though the DATE field name is a reserved
word.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
Allen,
Can you tell me how the year-to-date would be written if all the data were
in just one table, like the original example I sited where on row looks
like:

EMPLOYEE HOURS-WORKED DATE
Tom 5 5-Jan-2001
--
Richard


Allen Browne said:
Yes: Orders and Order Details are 2 tables. The Orders table holds the
header info (order number, date, client), and the Order Details holds the
line items (quantity, product, unitprice), since one order can contain
many
rows. To see an example, open the Northwind sample database that installs
with Access.

The 2nd part of the question may become clearer after you see the
example.

Allen,
I'm comfused by the example you referenced. It includes the code below.
However, I can't figure out the difference between [Orders] and [Order
Details]. Are there two tables (I only have one)? Or is [Order Details]
the
'dummy' table that is really just [Orders], and if so is there an error
in
the syntax below?

Also, what if I want a sum-to-date that spans multiple years of data?
Richard

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS
MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD

FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID =
OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)

AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),

Month([Orders].[OrderDate]) + 1, 1))
AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
--
Richard


:

Use a totals query to get the totals for a period (e.g. a month.)

Then use a subquery to get the progressive total.

The Year-to-date example should illustrate how:
http://allenbrowne.com/subquery-01.html#YTD

I have labor data for employees given by hours worked on a given day.
Eg. Tom, 5-hrs, 5-Jan-2001
What I need is cumulative hours worked by month:
E.g. Tom, 100-hours-to-date, Jan-2001

How can I use Access to generate cumulative-hours-to-date by month
from
hours-worked-on-given-days?

Note: It’s something I could get from Excel Pivot table, but I have
well
over 60,000 records (in the millions).
Click to expand...
Click to expand...
Click to expand...
 
R

Richard

Allen,
I spoke too soon. I forgot to mention that the YearToDate has to have
entries for every month past which labor was first worked.

So, if Jim worked 10 hours in Feb and 5 in Mar, your queries return:
2007 2 = 10
2007 3 = 15
but no entries for Apr-Dec.

I need there to be data for every month or a given year after work was
started, i.e.
2007 2 = 10
2007 3 = 15
2007 4 = 15
..
..
2007 12 = 15 Then back to zero in 2008 (assuming no work in 2008)
2008 1 = 0 or no entry for this row

Richard

--
Richard


Allen Browne said:
Create this query, and save as (say) qryMonthHours:
SELECT Employee,
Year([Date]) AS TheYear,
Month([Date]) AS TheMonth,
Sum([Hours-Worked]) AS MonthHours,
FROM [Table1]
GROUP BY Employee, Year([Date]), Month([Date]);

Then create another query using qryMonthHours as an input "table", and type
this into the Field row in query design:
YrToDate: (SELECT Sum([MonthHours] AS YTD
FROM qryMonthHoursAS Dupe
WHERE Dupe.Employee = qryMonthHours.Employee
AND Dupe.TheYear = qryMonthHours.TheYear
AND Dupe.TheMonth <= qryMonthHours.TheMonth)

Substitute your table name for Table1 in the first query. Hopefully that
will give correct results even though the DATE field name is a reserved
word.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
Allen,
Can you tell me how the year-to-date would be written if all the data were
in just one table, like the original example I sited where on row looks
like:

EMPLOYEE HOURS-WORKED DATE
Tom 5 5-Jan-2001
--
Richard


Allen Browne said:
Yes: Orders and Order Details are 2 tables. The Orders table holds the
header info (order number, date, client), and the Order Details holds the
line items (quantity, product, unitprice), since one order can contain
many
rows. To see an example, open the Northwind sample database that installs
with Access.

The 2nd part of the question may become clearer after you see the
example.

Allen,
I'm comfused by the example you referenced. It includes the code below.
However, I can't figure out the difference between [Orders] and [Order
Details]. Are there two tables (I only have one)? Or is [Order Details]
the
'dummy' table that is really just [Orders], and if so is there an error
in
the syntax below?

Also, what if I want a sum-to-date that spans multiple years of data?
Richard

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS
MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD

FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID =
OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)

AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),

Month([Orders].[OrderDate]) + 1, 1))
AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
--
Richard


:

Use a totals query to get the totals for a period (e.g. a month.)

Then use a subquery to get the progressive total.

The Year-to-date example should illustrate how:
http://allenbrowne.com/subquery-01.html#YTD

I have labor data for employees given by hours worked on a given day.
Eg. Tom, 5-hrs, 5-Jan-2001
What I need is cumulative hours worked by month:
E.g. Tom, 100-hours-to-date, Jan-2001

How can I use Access to generate cumulative-hours-to-date by month
from
hours-worked-on-given-days?

Note: It’s something I could get from Excel Pivot table, but I have
well
over 60,000 records (in the millions).
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

The missing months will need to come from somewhere.

Create a table with 2 Number fields for TheYear and TheMonth. Enter values
for all the years and months you want returned. Then create another query,
using this and the results of the last query. Use an outer join between the
2 in the upper pane of query design, so you get all records from this new
table, and any matches from the previous query.

If outer joins are new, they are mentioned here:
http://allenbrowne.com/casu-02.html
 
R

Richard

Allen,
I haven't been able to get an outer join to create new entries for each
name. Are you sure I don't have to create a table with all possible
year-months and names?

Right now my outer joint creates a new entry for each year-month, but not
for each year-month and name.

If I have to create a table that lists every possilbe name + year + month
things could get difficult. My query actually involves unique names and
funding sources. I didn't mention second breakout before for simplicity.
Anyway, there are 300 names, 50,000 funding sources, and 48 year/months so
table would be large.
 
A

Allen Browne

Richard said:
Are you sure I don't have to create a table with all possible
year-months and names?
Click to expand...

Yes: you do have to create such a table (though month numbers would be
better than month names.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cumulative totals 8
Countif or sumif for cumulative sums,pivot table & chart 2
Calculating occupational sick pay entitlement 0
calculate average values for 100 records in the shortest way? 5
Display missing records? 5
Show cumulative totals 0
Choosing a field's record based on date 1
End of Month: Parameter Query? 3

Top