reporting and formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ... I have a flat file database of machines identified by their serial
number (not the primary key). I have several meter readings for each
machine, with two columns, beginning and ending meter read. There are also
dollar values associated with these meter reads. I'm looking to to take the
last meter read minus the FIRST ever meter read. And divide the sum of the
dollar values by the difference in meter reads for an overall cost per unit
value. Would I need a macro or vba programming to get these values for each
change in machine serial number?
Or, should I maybe just take the unit cost of each individual meter read and
take the average of these in an Access report with formulas?
I'm new to access, so any advice would be appreciated.
Happy 4th!
 
Change the table name to what you have and use this query.

SELECT chrissyb.Serial, Min(chrissyb.Beginning) AS MinOfBeginning,
Max(chrissyb.Ending) AS MaxOfEnding, Sum(chrissyb.Dollars) AS SumOfDollars,
Sum([chrissyb].[Dollars])/(Max([chrissyb].[Ending])-Min([chrissyb].[Beginning])) AS [Cost per Unit]
FROM chrissyb
GROUP BY chrissyb.Serial;
 
Back
Top