Strange Filter Issue (2K)

[bobdog]

I have a form/subform that tracks contributions by donor. Each contribution is a record on the subform. The client wants to ask a simple and very reasonable question: "Show me all donors who contributed more than $100 last year." The "last year" part I have figured out, building and setting a filter on the main form that reads:

RID In (SELECT RID FROM MLRLINES WHERE (Year(MlrLines.DateRcd) = 2000))

Where: RID is the common Record ID, MLRLINES is the child table, and DateRcd is, well, date received.

The problem is that I can't figure out how to aggregate the dollar amounts. The following works, but it only returns parent records that contain a single item >= $100. A parent record qualifies as long as it contains at least one child record with an amount >= $100.

RID In (SELECT RID FROM MLRLINES WHERE (MlrLines.Amt >= 100))

The problem is that a contributor with, say, three $50 contributions does not qualify, because none of them is >= $100. In psuedocode, what I want to do is aggregate MlrLines.Amt

RID In (SELECT RID FROM MLRLINES WHERE (SUM(MlrLines.Amt) >= 100))

Anyone have an idea how to do this? You can do this pretty easily with a query, but it requires a HAVING clause, and that doesn't work for a filter statement.

[WendellB]

I don't think in this case you can do what you want with a filter, since you need to do aggregate queries to sum the values for each contributor. I think what you want to do is actually modify the data source for the subform. But in that case, if you show aggregates for a contributor, the subform will no longer be updateable, and that may be a challenge to explain to the client.

[bobdog]

Neat idea, despite the limitation you mentioned. I'll poke around at it and post back.

Thx

[MarkD]

You could use a subquery based on an aggregate query to select only those records where some aggregate criteria is met. An example using Northwind.mdb Customers & Orders tables: Aggregate query (qryCustOrderTotalsByYearCheap):

PARAMETERS [Enter Year] Short;
SELECT Year([OrderDate]) AS OrderYear, Orders.CustomerID, Sum([UnitPrice]*[Quantity]) AS OrderYearTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([OrderDate]), Orders.CustomerID
HAVING (((Year([OrderDate]))=[Enter Year]) AND ((Sum([UnitPrice]*[Quantity]))<1000))
ORDER BY Year([OrderDate]), Orders.CustomerID;

This selects those customers whose orders total less than $1000 for the specified year ("Enter Year" parameter). To keep things simple Discount is left out of the equation. Second query:

PARAMETERS [Enter Year] Short;
SELECT [Enter Year] AS OrderYear, CCur(DLookUp("OrderYearTotal","qryCustOrderTotalsB yYear","[CustomerID] = '" & [Customers]![CustomerID] & "'")) AS OrderYearTotal, CustomerID, CompanyName, ContactName, Address, City
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT CustomerID FROM qryCustOrderTotalsByYearCheap)))
ORDER BY Customers.CustomerID;

This query is updatable; note use of subquery to limit which customer records are returned. DLookup function is used to get the Order Total for year (calculated fields are not updatable). The SQL for qryCustOrderTotalsByYear is same as qryCustOrderTotalsByYearCheap, except w/o the parameter & "Having" criteria. To display details for specific orders, with Customers selected based on aggregate order total for year, use this lookup query (qryCustOrderTotals):

SELECT Orders.CustomerID, Orders.OrderID, Sum([UnitPrice]*[Quantity]) AS OrderTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderID;

This simply totals all orders by CustomerID & OrderID. Then create Select query:

PARAMETERS [Enter Year] Short;
SELECT Year([OrderDate]) AS OrderYear, Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate, CCur(DLookUp("OrderTotal","qryCustOrderTotals","[CustomerID] = '" & [Customers]![CustomerID] & "' And [OrderID] = " & [OrderID])) AS OrderTotal
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Year([OrderDate]))=[Enter Year]) AND ((Customers.CustomerID) In (SELECT CustomerID FROM qryCustOrderTotalsByYearCheap)))
ORDER BY Customers.CustomerID, Orders.OrderDate;

This query is updatable, except the lookup field. Note that DLookups tend to slow down a query. A better approach would to be use lookups on form on single-record basis rather than in query, and use subform to display order details for given year. The form could provide the Year parameter value. You may be able to adapt this by replacing Customers with Donors, and Orders with Contributions, if practical.

HTH

[bobdog]

Thanks for the reply. I'll explore this further.

What makes all this so strange is that the user wants to edit detail records, but review them in aggregate right on the data entry form. What the client wants is perfectly reasonable, if you think about it. The problem is tricking Access into doing unnatural acts. I'm trying to use filters to do this to permit popping up a datasheet view, pass the filter to reports, and also permit query by example in as seamless an interface as possible. I've been able to do this up until I started looking into this aggregate feature.

Wendell suggested above that I fiddle with the recordsource to do this. I've figured out the SQL statements to do it, but as soon as totals are required, I'm forced into using HAVING statements, which rules out filter statements.

I'm currently looking into using a tab control, with the first tab containing the detail, and the second tab containing aggregates. Surpisingly enough, it works, and it's pretty fast as well. I usually avoid using a tab control because of the performance hit, but the query only deals with the current parent record, so it seems to work.

The new subform uses this as a recordsource to give totals by year and by fund, and the form/subform handles the connection back to the parent form:

SELECT MLRLINES.RID, Year([DateRcd]) AS [Year], MLRLINES.Item, Sum(MLRLINES.Amt) AS Total, Count(MLRLINES.CTR) AS CTR FROM MLRLINES
GROUP BY MLRLINES.RID, Year([DateRcd]), MLRLINES.Item;

So far, this looks viable. Next step is to jigger the recordsource to permit different levels of aggregation: grand totals by year, and grand totals by fund, which should simply require leaving out one of the GroupBy fields. This gives totals by year, for example, with a spacer for Item ("" as Item) so I don't have to start rearranging the form objects on the fly:

SELECT MLRLINES.RID, Year([DateRcd]) AS [Year], "" AS Item, Sum(MLRLINES.Amt) AS Total, Count(MLRLINES.CTR) AS CTR FROM MLRLINES
GROUP BY MLRLINES.RID, Year([DateRcd]), "";

Anyway, I'm chuggin along. I'll post back when I figger this out.

[WendellB]

<hr>I usually avoid using a tab control because of the performance hit, but the query only deals with the current parent record, so it seems to work. <hr>

Actually the native Access tab control imposes very little performance penalty - performance penalties from subforms typically outweigh them significantly. We often use multiple embedded tabs for complex forms - it makes them much simpler and easier to understand what you are seeing. I presume by using filters you mean what you can do with a right-click from the UI - in cases where we want that and have totals or expressions, we build the entire query for the subform on the fly in code, and change the Data Source for the subform. But it can lead to lots of complicated code. An alternative when you can reasonably predict what kind of view the user wants, you can use a drop-down combo to set either the filter or the data source.

[bobdog]

Slipping in a new recordsource is exactly what I'm doing, and it seems to be a good approach so far. I got into trouble using tabbed dialogs several years ago, and ended up using a Tab Strip instead, which was a good solution. The problem with Tab Dialogs is that every object is populated when you run the form, which on a slow network, is a project killer. By using a Tab Strip control instead, only the first page was loaded at startup, and then subsequent pages were substituted as they were selected. "Late Binding", I think they called it, but it was a pretty snappy form by the time we got done with it.

In this case, things are much simpler (small database, fast network, simpler forms).

[charlotte]

That just means you were loading all the subforms when the form opened. If you only load a sourceobject into the subform when that particular tab is clicked, you eliminate virtually all speed issues with an Access tab control. In fact, it behaves like the tabstrip.

[WendellB]

Agreed - we do that on nearly all subforms where the data source is more than a few records.

[bobdog]

I said I'd post back when I figured this out. A big thanks to Mark and Wendell for their help.

What I was trying to do was to filter on a sum, which you just can't do with a filter.

The trick is to switch out the recordsource on the fly, just like Mark and Wendell suggested. This is what I ended up with (and without crutching back to DLOOKUP():

In the Form Open event:
Dim strSQL As String
strSQL = "SELECT MlrTops.RID, MlrTops.PFName, MlrTops.PLName, " & _
"MlrTops.FullName, MlrTops.Addr1, MlrTops.Addr2, MlrTops.City, MlrTops.St, " & _
"MlrTops.Zip, MlrTops.Phone1, MlrTops.HPhone, MlrTops.SFName, " & _
"MlrTops.SLName, MlrTops.EnvSal, MlrTops.LetSal, MlrTops.PWTitle, " & _
"MlrTops.ChgDt, MlrTops.ChgBy, MlrTops.Tagged, MlrTops.NoMail, MlrTops.NoAct " & _
"FROM MlrTops " & _
"WHERE (MlrTops.RID IN (SELECT RID from MlrLines group by RID, year([dateRcd]) having (sum([Amt]) >= 200)))" & _
"ORDER BY MlrTops.FullName;"
Me.RecordSource = strSQL

What I get is 58 donors who contributed $200 or more in any year, and it's fully updateable. The rest is just changing strSQL to parameterize things: $100 or more in 2003, $100 or more to a specified fund in 2004, and so on. I don't know how efficient this is on big tables, but it works fine for my immediate needs.

The actual constraints are provided by a pop-up form, which allows the user to change things around as needed. An "OK" push-button assembles the new SQL statement and changes the recordsource on the main form. A "Cancel" button clears the "filter" and displays all records.

Thanks, guys.