Looking for a Better Method...

[kenneth2k1]

Hey:

Ok, here's the setup:

2 files - Agents and Clients, related. An agent can have many clients assigned to him.

I needed a report that shows a count of how many client records an agent has created. I also needed these stats for the week and month. So I set up some calc fields that return 1 or 0 if the rec was created in the last week and month. Then I find records for each agent and set fields in the agent file with the total numbers. In order to hit all the agents, I have to loop through all the agents with this process.

It works, but the whole process seems more difficult than it should be. Does anybody have a better (maybe conventional) way of doing this? I'm sure this is a typical problem and should have a typical solution. If you need more info, let me know.

Thanks

Ken

[-Queue-]

What about using something like client id & " " & WeekOfYear(dateCreated) & " " Year(dateCreated) and client id & " " & Month(dateCreated) & " " & Year(dateCreated) as your calc fields and match a global or dynamic calculation to them for your relationships?

[kenneth2k1]

Alright. Thanks for the suggestion. I'll give it a try. So I'll create a relationship based on this calc?

Thanks

Ken

[-Queue-]

I would use these as the right hand side of the relationship and two globals or dynamic calcs for the left side. Then either some form of GTRR's with a set field or GetSummary by these relationships should be workable.

I'm not quite sure if you're wanting an automatic calculation or if you want to make the results static.