Getting Max of Sum per Date

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have a production db for tracking quality control issues:

ProdTable has field DateProd, which is the date products are produced.

QCTable has ProdID (unique number assigned to every item produced), DefectID
(number assigned to particular defect descriptions), and Quantity (which is
the number of occurrences of each defect on the item).

Now, I already have a query that provides a Count of DefectID and an
associated SumOfQuantity of each defect (ie. how many times the defect of
"Cracked Casing" happened in a given date range, and how many occurrences of
that defect happened). So, if 5 different items had 2 cracks in their
casings, the Count of "Cracked Casing" would be 5, while the Quantity of
"Cracked Casing" would be 10. Each Item can potentially have numerous
diferent defects, with different quantities of each.

So NOW what I need to do is somehow extract, in a given date range, the
highest QUANTITY defect per date, and show the Sum, the defect, and the date
it is from. I have tried several combinations of using the Max query
feature on my existing queries, but nothing will just show the highest
Quantity per date. For instance, a query on 7 days should just show 7
records; the defect that had the maximum quantity of occurrences for each
day.

Can someone help me with this? It would be greatly appreciated.


Thanks for reading


.

Relevant Pages

  • Re: Getting Max of Sum per Date
    ... Now create a new (Totals) query, based on that one, that finds, per date ... > the number of occurrences of each defect on the item). ... > "Cracked Casing" happened in a given date range, ...
    (microsoft.public.access.queries)
  • Re: incorrect totals for parts
    ... Your union query will probably quadruple the ... Have you looked at the datasheet view of this query? ... Defect_Code1, Defect1_Quantity, totalpartsran ... FROM [TBL defect count] ...
    (microsoft.public.access.reports)
  • Re: sorting and summing across different fields
    ... Perhaps, as your supervisor is telling you "how" to design the table, he/she ... your work about five times harder than it has to be to get the report. ... create a UNION query that will normalize the data for you. ... > i want the Details Section part to only include defect codes for however ...
    (microsoft.public.access.reports)
  • Aquinas got it right
    ... "As regards the individual nature, woman is defective and misbegotten, for the active power of the male seed tends to the production of a perfect likeness in the masculine sex; while the production of a woman comes from defect in the active power...." ... "All diseases of Christians are to be ascribed to demons; chiefly do they torment freshly-baptized Christians, yea, even the guiltless ...
    (alt.religion.christian)
  • Re: Running Total in SQL query not working as needed
    ... "Randall Arnold" wrote: ... > next by defect ID. ... I have a query that aggregates data from another query ... run update query to assign value to runningsum ...
    (microsoft.public.access.queries)