i need some help with a query that i am trying to set up.

I have a table called "tblEvents"

in which i store the following

Faultlog number - primary key
Daycode - this is the day of the year that the fault occurred (3209 - would represent 29th July 2003)
Line - Links to the machine that the fault occured on
Event Code - each fault is given a specific code A101 = Door fell off
Minor Stop, Major Stop, Breakdowns, Product Change, CIP, Maint - each event is catagorised into a specific stop group depending on the duration or fault type.

what i am trying to do is calculate the number of occurances of all stops on a specific line for each daycode. I am only really interested in the number of Breakdowns the other columns are not that important for this query.

for example

on daycode 3197

H7 - should be 2
H8 - should be 1 and so on

but i would also like it to display the total time for the day

for example

H7 - should read occurances = 2 & duration 114

Cheers

Andy

Query in attached DB.

many thanks

Andy

i am a little confused with this one, i have just set up a query with my actual database, i set it up exactly as the one suggested, the duration calculates correctly,

But

when i try to do the count occurances of the breakdowns, it tallies up all events for that day, in this case i get 32 occurances for daycode 3197

however in the post that Jon K posted in responce to my original post it worked fine

please help

Andy

This really is doing my head in now, i have attached the database, for you to see what is happening.

but the setup of the two query's are identical yet one of the query's tallies up just the number of occurances of breakdowns which is what i want.

yet the other counts all the events for that day not just breakdowns, i have spent hours looking at this and they do both seem to be set up identically...well as far as i can see.

can somebody please have a look and try to explain to me why these to query's should give me such different results.

Many Thanks

Andy

In my query, I overlooked the fact that some records contain a 0 for the Breakdowns. I checked only the query results for daycode 3197.

I think what you really need is to exclude these 0-breakdown records from the query:-

SELECT tblEvents1.DayCode, tblEvents1.Line,
Count(tblEvents1.Breakdowns) AS Occurances,
Sum(tblEvents1.Breakdowns) AS Duration
FROM tblEvents1
WHERE Breakdowns>0
GROUP BY tblEvents1.DayCode, tblEvents1.Line;

Thanks

Andy

daycode should be calculated rather than stored. You should be storing a real date and then just formatting it to look like a code for reporting purposes if that is what your user really wants. I know this isn't causing a problem at the moment but it has potential. Look at all those people who looked really stupid when the year 2000 came around because of the design issues caused by storing 6 digit years. Your datecode has the same potential. I know it's 7 years to the next decade but computer systems frequently last that long and data lasts even longer.