running sum in total query 1

[jg85120]

My table is formated as follows:

MTBF data:
Date Entered Ranges from 6/1/2001 to present
WorkOrderNotes Memo Data type
Bearings Checkbox
Starter Checkbox
Motor Checkbox
Other Checkbox

My users goes through a form, read the Work Order Notes, and check all boxes that apply (can be more than one)

What I am trying to do is have a query output the following:


MonthAndYear Failures To Date
------------ ----------------
Jun-2001 4
Jul-2001 3(this month) + 4(previous) = 7
Aug-2001 6(this month) + 7(previous) = 13
Sep-2001 1(this month) + 13(previous) = 14
... ...

what I have so far is:

SELECT Format([MTBF data]![Date Entered],"mmm-yyyy") AS [Month]
FROM [MTBF data]
GROUP BY Format([MTBF data]![Date Entered],"mmm-yyyy"), Format([MTBF data]![Date Entered],"yyyymm")
ORDER BY Format([MTBF data]![Date Entered],"yyyymm");

which just sorts by yyyymm and displays mmm-yyyy (easy part, lol)

"Failures To Date" is the sum of checked boxes for the month + previous

I looked at faq701-5268, but I could not get it to work i think because i am using a total
query

I have access 2003 there is a Runningsum property, but i dont understand how to use it.

I am new to access, I just started monday.
all ideas are welcome, thanks

 

[PHV]

You may try something like this:

SELECT Format(A.[Date Entered],'mmm-yyyy') AS [Month]
,Sum(Abs(B.Bearings+B.Starter+B.Motor+B.Other)) AS [Failures To Date]
FROM [MTBF data] AS A INNER JOIN [MTBF data] AS B
ON Format(A.[Date Entered],'yyyymm')>=Format(B.[Date Entered],'yyyymm')
GROUP Format(A.[Date Entered],'mmm-yyyy'),Format(A.[Date Entered],'yyyymm')
ORDER BY Format(A.[Date Entered],'yyyymm');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jg85120]

THX

Just one problem, for example, there 3 work orders entered on 8/6/2001 so when it runs the query, the running total is multiplied by 3.

If needed, i can post table and query.

 

[PHV]

What is the entire schema of [MTBF data] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jg85120]

i am not sure what schema is, im guessing layout:

ProductID Alpha-numeric
Date Entered Date (6/2/2001)
Date Closed Date
AreaID Number 1-7
Work Order Number Alpha-numeric
Work Order Notes TextMemo
Bearings Checkbox
Starter Checkbox
Motor Checkbox
Other Checkbox
Not Sure Checkbox
Ignore Checkbox


Example Data (using above order)
E0 6/4/2001 6/21/2001 2 CC173 Memo N N Y N N N
O7 7/9/2001 11/30/2001 4 CC778 Memo N N Y Y N N
O3 8/6/2001 2/19/2002 1 CC113 Memo N N Y N N N
O3 8/6/2001 1/29/2002 1 CC114 Memo N Y N N N N
E2 8/6/2001 1/29/2002 1 ... ... N N N Y N N
E20 9/7/2001 10/4/2001 2 ... ... N N Y N N N
E23 9/7/2001 11/3/2001 2 .... ... N Y N N N N

and cuz ther are three 8/6 it multiplys by 3 so i'd get 18 for running sum for august

also, i have like 5000+ records, ag.. , lol, right now im just trying to figure it out with like 20, but with 5000 i dont know how effective this would be.

So lost... Thx for sticking with me.

 

[PHV]

You may try something like this:
SELECT Format(A.[Date Entered],'mmm-yyyy') AS [Month]
,Sum(Abs(B.Bearings+B.Starter+B.Motor+B.Other)) AS [Failures To Date]
FROM [MTBF data] AS A INNER JOIN [MTBF data] AS B ON A.[Date Entered]>=B.[Date Entered]
WHERE B.[Date Entered]<A.[Date Entered]
OR (B.[Date Entered]=A.[Date Entered] AND B.[Work Order Number]<=A.[Work Order Number])
GROUP Format(A.[Date Entered],'mmm-yyyy'),Format(A.[Date Entered],'yyyymm')
ORDER BY Format(A.[Date Entered],'yyyymm');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jg85120]

1 3 15 15

I ran that code ,and got the following with the example data i showed previously:

Jun-2001 1
Jul-2001 3
Aug-2001 15
Sep-2001 15

I sadly cant offer any suggestions as I am totally new to this stuff (and barely understand your sql, lol)


FAQ701-5268 had some interesting stuff
as well as the runningsum property like Bearings.runningsum? I dont understand any of this, but this is pretty much all i can offer, lol

Thanks for sticking with me still.

 

[PHV]

Can you please post the EXACT SQL code you tried ?
What are the missing values of [Work Order Number] in your example data ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jg85120]

The following Work Order Numbers are exactly what are in my database followed by their Date Entered dates:
CC-01-10173-00 6/4/2001
CC-01-11778-99 7/9/2001
CC-01-90113-02 8/6/2001
CC-01-90113-16 8/6/2001
CC-01-90113-04 8/6/2001
CC-01-15200-00 9/7/2001
CC-01-15185-00 9/7/2001

I copy/pasted your SQL and changed "GROUP Format(.." to "GROUP BY Format(.."

 

[PHV]

Another way.
Create a saved query named, say, qryMTBFbyMonth:
SELECT Format([Date Entered],'mmm-yyyy') AS [Month]
,Sum(Abs([Bearings]+[Starter]+[Motor]+[Other])) AS [Failures],Format([Date Entered],'yyyymm') AS SortOrder
FROM [MTBF data]
GROUP BY Format([Date Entered],'mmm-yyyy'),Format([Date Entered],'yyyymm');

And now the running total query:
SELECT A.Month, Sum(B.Failures) AS [Failures To Date]
FROM qryMTBFbyMonth AS A INNER JOIN qryMTBFbyMonth AS B ON A.SortOrder>=B.SortOrder
GROUP BY A.Month, A.SortOrder
ORDER BY A.SortOrder;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jg85120]

INCREDIBLE!!!!

Dude, this thing has been killing me for days!

More than likely I am going to post another question later today or tomorrow, hopefully it will be less confusing.

I can not thank you enough.

 

[jg85120]

Okay, one more quick question:

can i get that to only display Failures to date for a specific Area ID

say "Area ID = 3" only instead of all 7

thx

 

[PHV]

Simply add a where clause in qryMTBFbyMonth:
SELECT Format([Date Entered],'mmm-yyyy') AS [Month]
,Sum(Abs([Bearings]+[Starter]+[Motor]+[Other])) AS [Failures],Format([Date Entered],'yyyymm') AS SortOrder
FROM [MTBF data]
[highlight]WHERE AreaID = 3[/highlight]
GROUP BY Format([Date Entered],'mmm-yyyy'),Format([Date Entered],'yyyymm');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jg85120]

thank you

i know it was simple, i am not familiar with SQL so i did not know where to put it