Totals and subtotals

[kujito]

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:

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.

 

[jzwp22]

I would recommend creating separate queries to get your totals (Sums and counts) making sure you have the projectID in each of those queries. Then create a final summary query that joins the various totals queries via the projectID

 

[kujito]

Thanks, I think that's where I'm headed, but I may need more than two queries to get it all. I'll try to remember to post my solution.

 

[jzwp22]

From the query you posted, I'm guessing that you will need for totals query and then the summary query. If your criteria are the same you may be able to reduce the number of the totals queries required.

 

[kujito]

Not done, but on track(knock on wood). I needed three queries to get everything to calculate properly. I also need three more (versions of the first 3) to find the same info for mines categorized as PrimaryMO = 1 and PrimaryMO = 2. 'Bout time to go home for the day. I'll try to post updates.

 

[kujito]

OK. So, here's what I came up with. It took 3 queries to get the different pieces I needed and a 4th query to get it all together. I had to run another seiries of queries for PrimaryMO = 2 as well, so 8 total queries.

summaryQuery 1

SELECT Project.ProjectID, Project.ProjectName, Country.CountryName, Project.PrimaryMO, Sum(Production.Produced1) AS SumOfProduced1, Production.MeasuredMetal1ID, Count(Production.Year) AS YearsMO, Max(Production.Year) AS LastMO, Min(Production.Year) AS FirstMO
FROM (Country INNER JOIN Project ON Country.CountryID = Project.CountryID) INNER JOIN Production ON Project.ProjectID = Production.ProjectID
WHERE (((Project.PrimaryMO)=1))
GROUP BY Project.ProjectID, Project.ProjectName, Country.CountryName, Project.PrimaryMO, Production.MeasuredMetal1ID;

summaryQuery2

SELECT Project.ProjectID, Project.ProjectName, Production.MeasuredMetal1ID, Count(Production.Year) AS YearsOp, MIN(Production.Year) AS FirstOp, MAX(Production.Year) AS LastOp, Sum(Production.Produced1) AS SumOfProduced1
FROM Project INNER JOIN Production ON Project.ProjectID=Production.ProjectID
WHERE Project.PrimaryMO=1 And Production.MeasuredMetal1ID=485
GROUP BY Project.ProjectID, Project.ProjectName, Production.MeasuredMetal1ID;

summaryQuery3

SELECT Project.ProjectID, Project.ProjectName, summaryQuery1.LastMO, Production.Produced1 AS LastProd
FROM ((Project INNER JOIN Production ON Project.ProjectID=Production.ProjectID) INNER JOIN summaryQuery1 ON Project.ProjectID=summaryQuery1.ProjectID) INNER JOIN summaryQuery2 ON Project.ProjectID=summaryQuery2.ProjectID
WHERE Production.MeasuredMetal1ID=485 And Production.Year=summaryQuery1.LastMO
GROUP BY Project.ProjectID, Project.ProjectName, summaryQuery1.LastMO, Production.Produced1;

summaryPrimaryMO1

SELECT Project.ProjectID, Project.ProjectName, summaryQuery1.CountryName, summaryQuery2.YearsOp, summaryQuery1.YearsMO, summaryQuery1.LastMO, (summaryQuery1.SumOfProduced1) AS TotalMO, summaryQuery2.LastOp, summaryQuery3.LastProd
FROM ((Project INNER JOIN summaryQuery1 ON Project.ProjectID = summaryQuery1.ProjectID) INNER JOIN summaryQuery2 ON Project.ProjectID = summaryQuery2.ProjectID) INNER JOIN summaryQuery3 ON Project.ProjectID = summaryQuery3.ProjectID;

I'm sure that there is a more elegant solution, but this worked.
One final question though. How do I get "summaryPrimaryMO1" to have a generic "queryID" field that is based on a descending sort of the "TotalMO" field? The project with the largest "TotalMO" should have "queryID" of 1 and the project with the smallest "TotalMO" should have "queryID" of 9 in this case.

 

[jzwp22]

It sounds like you want to have a rank/row number for each row of the query. If that is the case, you might want to check out this link

 

[kujito]

I'll check it out.
Thanks

 

[jzwp22]

You're welcome.

 

[JRG0620]

I have a slightly different issue but I think this is the proper thread for the post. I have a form bound to a table. The table has a primary key of Project and Bldg.
The table has the following fields:

Project Bldg GenCon% SitePrep% Roof% HVAC% Plumb%

The user enters the Project Number and all associated Bldg information is displayed on the continuous form. There can be zero records for the Project or there can be many. There is no limit to the number of records for the Project.

On the form, I have added a BldgTot%. This field is not on the table and it is supposed to be calculated as the user enters the percentages. The BldgTot% has a formula of BldgTot% + GenCon% + SitePrep% + Roof% + HVAC% + Plumb%

I have two issues.
1) The BldgTot% is not calculated until I am off the current record.
2) The BldgTot% for record two inherits the total from record 1 until I’m off of record 2.

How do I calculate the BldgTot% as I’m going from one percentage to another? I’ve tried entering the formula on, the field event ‘on exit’, ‘on change’, ‘on update’.
How do I keep the BldgTot% to the specific records information instead of to a complete total?

Thanks for any advise/direction.

 

[jzwp22]

I'm a little unclear as to what information you are capturing. What do you mean by %? Are you entering costs for the project and splitting them up by various areas roof, HVAC etc.? Also, it is not a good idea to have spaces or special characters in your table or field names (%, #, etc.). Can you explain what your application is designed to do in more detail?

 

[JRG0620]

JZWP22,

I'll see if I can be more specific. My table includes the following fields:

Project, Bldg, GenCon, SitePrep, Roof, HVAC, Plumb

The key to the table is Project

The user enters the Project Number and all associated Bldg information is displayed. There can be zero records for the Project or there can be many. There is no limit to the number of records for the Project.

On the form, which is bound to the table, I have added a BldgTot. This field is not on the table and it is supposed to be calculated as the user enters each fields information. The BldgTot has a formula of (BldgTot + GenCon + SitePrep + Roof + HVAC + Plumb)

I have to keep a running total of all the information entered for that Project. These fields represent a percentage of the Costs for that Building (Bldg) for that Project. The total cannot exceed 100.

I have two issues.
1) The BldgTot is not calculated until I am off the current record.
2) The BldgTot for record two inherits the total from record 1 until I’m off of record 2.

How do I calculate the BldgTot as I’m going from one field to another? I’ve tried entering the formula on, the field event ‘on exit’, ‘on change’, ‘on update’.
How do I keep the BldgTot to the specific records information instead of to a complete total?

I hope this is more clear. Thanks for any advise.

 

[jzwp22]

I'm confused by this statement:

There can be zero records for the Project or there can be many. There is no limit to the number of records for the Project.

If you have zero records for a project, then you don't have a project, right?

Also, since GenCon, SitePrep, Roof, HVAC, Plumb are all types of costs associated with a project they should be records in a related table. A project has many costs --i.e. one-to-many relationship

tblProjectCosts
-pkProjCostsID primary key, autonumber
-fkProjID foreign key to tblProjects
-fkCostTypeID foreign key to tblCostTypes
-currCost (the cost value)

tblCostTypes (this table will have 1 record for each cost type:GenCon, SitePrep, Roof, HVAC, Plumb)
-pkCostTypeID primary key, autonumber
-txtCostType

Back to your project table... Can a project involve more than 1 building or 1 and only 1 building?

 

[JRG0620]

I understand what you're saying about the relationships and if I designed the application, that's how I would have done it. The problem is that I have to maintain/update the existing processes.

A Project can exist without a Building.
For a Building, the fields exist: GenCon, SitePrep, Roof, HVAC, Plumb.

There is a one (Project) to many (Building) relationship. For each building, the fields can have data or be null. These fields exist for every building entered because that's the way the current database is defined.

All I'm trying to do is calculate the buildings total as the fields are entered.

 

[jzwp22]

If you are now responsible for the application, you will save yourself a bunch of hassles in the long run if you correct the table structure. But, in short just add an unbound textbox control to your form and set its control source as follows

=GenCon+SitePrep+Roof+HVAC+Plumb

If you want to show the sum as you enter new values, set the default value for each of the fields you are summing to zero at the table level.

 

[JRG0620]

JZWP22,

I do have an unbound field on the form with that calculation. The issue is it does NOT do the calculation until the record is written back to the database. I need/want the calcuation to be done as the user enters information into each of the fields. This provides the user with the up-to-date numbers instead of waiting until the record is re-written.

 

[jzwp22]

I was experimenting and noticed the same thing when adding a new record. To correct that I set the default value property in each of the fields in the underlying table to zero. After I did that, the control with the expression updated each time I entered a value in one of the fields.