Ok, so I have mine production data from numerous years and numerous mines. Some years have 0 (zero) production. I'm trying to come up with columns that display:
1. Total years in operation(good)
2. Years of production(stuck)
How do I get a field that will give me a count of only the years for which Production.Produced1 = 0, AND field which counts all years of operation? If I add the RED text below, I get two columns with identical data(correct for producing years). Without the red, I get the proper data for operational years.
So far my query looks like:
I'd also like to display the range of operational years for each mine. How would I do that?
Thanks in advance.
1. Total years in operation(good)
2. Years of production(stuck)
How do I get a field that will give me a count of only the years for which Production.Produced1 = 0, AND field which counts all years of operation? If I add the RED text below, I get two columns with identical data(correct for producing years). Without the red, I get the proper data for operational years.
So far my query looks like:
Code:
SELECT Project.ProjectID, Project.ProjectName, Project.CountryID, Project.PrimaryMO,
Sum(Production.Produced1) AS SumOfProduced1, Production.MeasuredMetal1ID, Count(Production.Year) AS YearsOp,
[COLOR=Red]Count(Production.Year) AS YearsMO,[/COLOR] MAX(Production.Year) AS LastYear
FROM Project INNER JOIN Production ON Project.ProjectID = Production.ProjectID
WHERE (((Production.MeasuredMetal1ID)=485)
AND ((Project.[PrimaryMO])=1)
[COLOR=Red]AND ((Production.[Produced1]) <> 0)[/COLOR] )
GROUP BY Project.ProjectID, Project.ProjectName, Project.CountryID, Project.PrimaryMO, Production.MeasuredMetal1ID;
I'd also like to display the range of operational years for each mine. How would I do that?
Thanks in advance.