ordendelfai
Technical User
Access 97
I have a feeling that I am over complicating and thinking this and hope someone has a better solution.
qry_VIEW_Membership FIELDS:
PHID (Number)
Year_Month (text field)
Total_Subs (Number)
Total_Members (Number)
I have to sum this query's [Total_Subs] for each [PHID] using only the most recent [Year_Month] for each PHID. The problem is that some PHIDS may have "2004/06" as their most recent Year_Month, while others may have "2004/01" (and so on).
My attempt to use a calculated field in a query as below just hangs (the query never resolves, or maybe it is just taking a LONG time).
Here is the Access SQL behind the query:
Thanks for any advice or help!
~Joel
I have a feeling that I am over complicating and thinking this and hope someone has a better solution.
qry_VIEW_Membership FIELDS:
PHID (Number)
Year_Month (text field)
Total_Subs (Number)
Total_Members (Number)
I have to sum this query's [Total_Subs] for each [PHID] using only the most recent [Year_Month] for each PHID. The problem is that some PHIDS may have "2004/06" as their most recent Year_Month, while others may have "2004/01" (and so on).
My attempt to use a calculated field in a query as below just hangs (the query never resolves, or maybe it is just taking a LONG time).
Code:
Test: DSum("[Total_Subs]","qry_VIEW_Membership","[Year_Month] = '" & DMax("[Year_Month]","qry_VIEW_Membership","[PHID] = " & [PHID] & "") & "'")
Here is the Access SQL behind the query:
Code:
SELECT DISTINCT qry_VIEW_Membership.PHID, DSum("[Total_Subs]","qry_VIEW_Membership","[Year_Month] = '" & DMax("[Year_Month]","qry_VIEW_Membership","[PHID] = " & [PHID] & "") & "'") AS Test
FROM qry_VIEW_Membership;
Thanks for any advice or help!
~Joel