count of events by duration

[Chimp8471]

in my database, we track stoppage on production lines.

the stopages a re stored in TblEvents... the numbers under the minor and major categories are in downtime minutes.

i need to now how many times an eventcode has happened between a timeframe

for example,

i need to now how many time code s301 has stopped between

>1 <3
>= 3 <5
>= 5 <7
>= 7 <10
>= 10+ minutes

looking at my database, the answer should be

4 minor stops and 1 major stop

i need this foe each catagory minor and major stops,

so the display would be

line( Hidden) -- daycode(hidden) -- Event code -- Minor stop >1 <3 -- minor stops >= 3 <5-- etc then Major Stops >1 <3 etc

cheers

Andy

 

[namliam]

Sorry to crack down on you but your table is not fully normalized... tho it works you shouldnt have major and minor i think... just 1 field minutes and a tickbox or something making the distinction between major and minor...

Any way i build some sample for you so you can see how i would do it, you can build on it...

Check the query's and see if you can find what you like....

Regards

 

[Chimp8471]

Sorry to crack down on you but your table is not fully normalized... tho it works you shouldnt have major and minor i think... just 1 field minutes and a tickbox or something making the distinction between major and minor...

Any way i build some sample for you so you can see how i would do it, you can build on it...

Check the query's and see if you can find what you like....

Regards

Thanks for that, i am aware about the table issue, i didn't design it and have not had time to modify the table structure so am going to have to live with this one for the time being.

What you did was exactly what i am after....

but i now also want to use the category thing to sum the time for each event code instead of the count used previously.

I have tried to just modify the one you sent to see if i could just do that but, i get a Jet error come up.

 

[namliam]

Its simple if you know how i guess...

try this query

TRANSFORM Sum([MinorStop]+[MajorStop]) AS CountOfEventCode
SELECT Query2.EventCode, Sum([MinorStop]+[MajorStop]) AS Total
FROM Query2
GROUP BY Query2.EventCode, [Query2].[Expr1] In ("Major 1","Major 2","Major 3","Major 4","Major 5","Minor 1","Minor 2","Minor 3","Minor 4","Minor 5")
PIVOT Query2.Expr1 In ("Major 1","Major 2","Major 3","Major 4","Major 5","Minor 1","Minor 2","Minor 3","Minor 4","Minor 5");


Regards

 

[Chimp8471]

Simple....

Thanks

Andy