query calculations aren't working

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

Guest

I'm keeping a database with the following items: Date, Formula, Batches Produced, and Batches Reworked. My end goal is to be able to enter in a date range and formula code and have the query calculate the % Reworked. I made a first query using Date, Formula, Batches Produced, and Batches Reworked. From here, I simply used a summation with the Batched Produced and Reworked fields to come up with totals. This query properly shows the dates and ~10 different formulas with the 10 different sums of batches produced and reworked. I then made a 2nd query using the first query to calculate the % Reworked. Using criteria, I can select a date range and formula and get all of the sampled dates with each batches produced and reworked, and a calculated % reworked. What I want, however, is just the SUM. So, if I enter between 01/01/04 and 01/01/05 with a formula of XYZ123, I only want to see Formula: XYZ123, Batches Produced: 100, Batches Reworked: 10, % Reworked: 10%. Any ideas on how I can do this, or should I just use a report

Thanks!
 
Something like the following untested SQL statement should work

SELECT
Formula,
Sum([Batches Produced]) as BatchCount,
Sum([Batches Reworked]) as ReworkCount,
Sum([Batches Reworked])/ Sum([Batches Produced]) as Percentage
FROM YourTable
WHERE [Date] Between #01/01/04# and #01/01/05#
GROUP BY Formula

Open a new query in SQL mode and paste the above into it. Obviously you will
need to substitute your table and field names.
 
Back
Top