Getting Max of Sum per Date

[Jeff Boyce]

Consider "chaining" queries.

Start with what you have, the calculated total and the related date.

Now create a new (Totals) query, based on that one, that finds, per date
(GroupBy) the largest total (Max).

Good luck

Jeff Boyce
<Access MVP>

 

[NewbieSupreme]

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