DSUM and DMAX Nesting using Domain functions??? 1

[ordendelfai]

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).

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:

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

 

[ordendelfai]

Ack, I made a mistake. The Access Calculated field and SQL code is below. Interestingly enough, when I changed it to "TOP 25" and eliminated DISTINCT, this code works and does not hang. However, it needs to be DISTINCT, and run about..oh..100 times faster... ;-)

Test: DSum("[Total_Subs]","qry_VIEW_Membership","[Year_Month] = '" & DMax("[Year_Month]","qry_VIEW_Membership","[PHID] = " & [PHID] & "") & "' AND [PHID] = " & [PHID] & "")

and the Access SQL is:

SELECT TOP 25 qry_VIEW_Membership.PHID, DSum("[Total_Subs]","qry_VIEW_Membership","[Year_Month] = '" & DMax("[Year_Month]","qry_VIEW_Membership","[PHID] = " & [PHID] & "") & "' AND [PHID] = " & [PHID] & "") AS Test
FROM qry_VIEW_Membership;

 

[scriverb]

This will take two saved queries:

Save as MaxYearMonthByPHID

Select qVM.[PHID], Max(qVM.[Year_Month]) as MaxYearMonth  
FROM qry_View_Membership as qVM  
GROUP BY qVM.[PHID];

Save this SQL as whatever name you wish:

Select qVM.[PHID], SUM(qVM.[Total_Subs]) as SumOfTSubs 
FROM qry_VIEW_Membership as qVM INNER JOIN MaxYearMonthByPHID as MYM ON (qVM.[PHID] = MYM.[PHID]) and 
(qVM.[Year_Month) = MYM.[MaxYearMonth]) 
GROUP BY qVM.[PHID] 
ORDER BY qVM.[PHID];

Now just run the second query. Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ordendelfai]

Thanks Bob!!! This really helped, and runs very fast. I modded the solution to fit my exact final needs. My final code is below in case anyone else needs help with something like this.

3 queries

Query 1: VIEW_Membership (pass-through query holds the summarized membership totals for all Year_Months for each PHID)

SELECT DISTINCT tbl_ClientSuffix_Count.PHID, tbl_ClientSuffix_Count.Year_Month, Sum(tbl_ClientSuffix_Count.Total_Subs) AS Total_Subs, Sum(tbl_ClientSuffix_Count.Total_Members) AS Total_Members
FROM tbl_ClientSuffix_Count
GROUP BY tbl_ClientSuffix_Count.PHID, tbl_ClientSuffix_Count.Year_Month;

Query 2: VIEW_MembershipMaxYearMonth (pass-through Query holds the max Year_Month for each PHID)

SELECT  tbl_ClientSuffix_Count.PHID, Max( tbl_ClientSuffix_Count.Year_Month) AS MaxYearMonth
FROM  tbl_ClientSuffix_Count
GROUP BY  tbl_ClientSuffix_Count.PHID;

Query 3: VIEW_MembershipMaxSubsMembers (combines the two pass-through queries to display the max membership for the max Year_Month for each PHID)

SELECT VIEW_Membership.PHID, VIEW_Membership.Year_Month, Sum(VIEW_Membership.Total_Subs) AS Total_Subs, Sum(VIEW_Membership.Total_Members) AS fTotal_Members
FROM VIEW_Membership INNER JOIN VIEW_MembershipMaxYearMonth ON (VIEW_Membership.Year_Month = VIEW_MembershipMaxYearMonth.MaxYearMonth) AND (VIEW_Membership.PHID = VIEW_MembershipMaxYearMonth.PHID)
GROUP BY VIEW_Membership.PHID, VIEW_Membership.Year_Month;

~Joel

 

[scriverb]

Joel: Glad to be of assistance in this matter. Thanks for the Star. It is appreciated.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TheAnimal]

I have a Db that tracks users logins. I have the time field set to 24 hours. I need to print a report from a query of the current day and the latest(highest) time a user logged in, FOR EACH USER.

I found this on the internet and attempted to use it in my query and in my report. Neither worked. I still a syntax or too complex error.

dtDate is actually the 24 hour time field.

DMax("dtDate","qrySingleDateFindAll","[UID1LastName]= '" & [qrySingleDateFindAll].[UID1LastName] & "''")

Any help would be appreciated. Thanks
The Animal

 

[scriverb]

Well my first question is you said:

I need to print a report from a query of the current day and the latest(highest) time a user logged in, FOR EACH USER.

Current date: Is you query selecting by the current date?
Each User: Just the last name is not sufficient to guarantee that you are looking at the correct records.

Please post your entire SQL for your query so that I may see what you are selecting and trying to report on. We may want to use an additional query to select by todays date, group by a userID, and select the Max Date. Then we would join that query results to your query through common fields and identifiers.

Post back with your info.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TheAnimal]

Thanks for trying to help. Found what I needed. I don't have SQL problem. What I needed to do was turn on Totals in the query. What I was looking for was the last TIME (highest in 24 hour). Using the Totals MAX function I got just what I was after the last Time they logged in.

Thanks for trying, apprciate the time you spent.

The Animal