Filter report on subtotal?

[Harleightzmom]

Is there a way to filter a report based on the subtotal? It will let filter on individual values, but not the subtotals. I have some customers that place multiple orders in a day, and I am trying to find the ones that have multiple order numbers on any given day.

Am I just approaching this all wrong?

 

[Crystalyzer]

What does your data look like?

 

[alansidman]

How about an aggregate query to get totals by customer?

MS Access: Count Function

 

[Harleightzmom]

The fields show a customer, an order number, and the date ordered. I have a basic report set up by date and customer, with a subtotal for counting the orders for each customer that day.

 

[alansidman]

Based upon the fields you have indicated, the following SQL statement should provide what you want.

SELECT Table1.CustName, Table1.ODate, Count(Table1.Onum) AS CountOfOnum
FROM Table1
GROUP BY Table1.CustName, Table1.ODate;

Use the query as the record source for your report.

 

[Micron]

alansidman; he wants records with more than one order for a customer for a date so he needs criteria for that. My air code may not be right, but I'm thinking

SELECT Table1.CustName, Table1.ODate, Count(Table1.Onum) AS CountOfOnum FROM Table1 WHERE 
Table1.CountOfOnum > 1 AND Table1.ODate = #01/01/2015# GROUP BY Table1.CustName, Table1.ODate;

.
I suspect a control reference or parameter would be needed in place of a hard coded data.

 

[alansidman]

Micron: If he has more than one order per record (is that what you are saying), then don't we have a normalization issue that should be addressed? To be honest, I'm not sure at this point what the OP wants. Let's hope he clarifies for us.

 

[Micron]

Maybe, but not necessarily. It's not uncommon to have related fields in other tables as you no doubt know, but sometimes there are pairs (or even more). In a situation like I allude to, I can't recall if I thought the db was normalized to the nth degree, but I do know that the designers of the multi-million dollar system were IS professionals and I was not. These people seemed content to create many composite primary fields in this huge db, where others say no, create only composite indexes. Anyway, multiple inner joins between a pair of many to many tables did cause some duplication. I can't recall if the solution was to query that query or modify the joins.

Besides all that, if I understand correctly, he has some customers with multiple orders in a day. This must be a common situation in business, so that would make multiple order records for a customer a distinct possibility, correct?