Help with a Count Query

[Chimp8471]

i am tring to carry out a query, that will display the number of occurances that a specific event happens, sorted by machine.

i have attached and excel spreadshead to show how i am trying to get a report to end up looking like.

in the table, i the columns labelled:

minorstop
majorstop
breakdown
cip
productchange
maintenance

refer to time lost through each stoppage.

i am trying to find out how many time each machine has a fault that falls into a specific stoppage time, eg, minor stop etc..

hope this makes some sense.

Please help

Andy

 

[neileg]

Your table structure is wrong. By having a field for each type of stoppage, you have created a non-normalised structure. The only way round this is to have a series of queries that extract the data by stoppage type, and do the counts in these queries, then join them all together.

You can create a pivot table based on the data that works OK if you want to sum the times, but when you start counting, the records with zero values get counted as well.

 

[Jon K]

Alternatively, you can build a Totals query to group by Machine, and then build a Union query from the Totals query.

 

[Chimp8471]

JonK,

That look exactly like what i am after, but and there must always be a But....

in my example i only had 1 line called Line 1, how would i modify the sql code to display other lines also, for example line2, line 3.

i would also like to be able to choose between a range of daycodes.

I cant thank you enough for this.

 

[neileg]

But Jon, your query sums the stoppage times, not counts the events. As I said, if all you want is a sum of the times, a pivot table will give exactly what is wanted. However, Chimp is asking to count the stoppages as events.

 

[Jon K]

Thanks Neil. You are right. I misread the question.


Andy, try Query2. Hope it's what you needed. I duplicated the records in tblEvents for Line 2. The query now counts instead of summing.

 

[Chimp8471]

Thanks....Perfect