Community
Showing results for 
Search instead for 
Do you mean 
Reply

Complicated Queries Question

New Member
Posts: 2
Country: USA

Complicated Queries Question

Our Alcoholics Anonymous group uses ACT! 2011 Pro to keep track of its 239 active members and their activities within the group. Each week we send an e-mail reminder to our members reminding them of who within the AA group is celebrating an AA Birthday, and also alerting them to who is celebrating a Milestone ( 500, 1000, 1500, 2000 etc, DaysSober). Our members are prompted by this reminder to make contact with a celebrating buddy, which encourages all to continue along the path. Frankly, it seems to work, because our 239 members average 10.9 years of sobriety each.

 

I’m looking for help in best using our existing software to do this calculation task each week.

 

In our database we have an AA Birthday field, and a DaysSober field. DaysSober is automatically updated daily using Northwoods Software’s, Sales Automation Manager, a nifty piece of software which does a calculation based on the AABD field.

 

Currently we export the information to Microsoft Access, and then do a detailed query to find that week’s AABD’s and Milestones.

 

Can one of you suggest how we can use ACT! 2011 Pro to do this for us, without Access? Many thanks in advance. 

Silver Super Contributor
Posts: 2,328
Country: USA

Re: Complicated Queries Question

Larry,

 

The best way is probably to create dynamic groups (Create Group|Right click|Group Membership|Add/Remove Contacts|Edit Query) and then pull up the group to find the members that meet the attributes (Right Click on Group|Choose Create Lookup).

 

So you would create a group called "January Birthday" and the query for the group would be Birth Date Month = 1.  You would create 12 groups and then you could just pull up a list of members with birthdays in the coming month, etc.  You could do the same thing with a group for 500 Days Sober and the query would be "DaysSober > 499 and DaysSober < 1000".

 

Once you have your list you can export to Excel, etc.

 

Stan

P.S.  I admire the work your oganization does.  Keep up the good work!


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
New Member
Posts: 2
Country: USA

Re: Complicated Queries Question

Many thanks Stan, if possible I’d like to go a bit further.

 

I took your advice, and even added a bit of a wrinkle to it. I made one Dynamic Group, called “DaysSober Next 7 Days”, with Definitions such as:

 

Field Name (Days Sober), Greater Than or Equal To 493

Field Name (Days Sober), Less Than or Equal To 500

 

And then I repeated that for all the milestones I was looking for (500, 1000, 1500…up to 16,000.

 

This gave me all the milestones within the next week, in one group. Each day it changes as guys drop off or fall on, to be within that seven day window. We would run the report and send out the information to our members on Monday night. So now I have a way to easily find them, but could use your help in creating a report to use the information.

 

I’m looking for a Template Report that I can copy and paste into an e-mail which would look like this:

 

Tues   11/6/12   Kerry XXXXX   AABD 7 Years      (503) XXX-9389   xxxxx@hotmail.com

Tues   11/6/12   Kevin XXXXX   AABD 20 Years   (503) XXX -8906   yyyyyy@gmail.com

Tues   11/6/12   Mike XXXX   AABD 14 Years   (503) XXX -8267   zzzzzzz@gmail.com

Sun   11/11/12   Howard XXXXXX   500 Days   (971) XXX -0655   qqqqqqqq@gmail.com

Mon   11/12/12   Murry XXXXX   3500 Days   (971) XXX -3627   rrrrrrrrrr@yahoo.com

 

For the record, I know that I can find the AA Birthdays the same way as the milestones, and even have them be in this same or another Dynamic Group.

 

Now my questions:

                Can my report template look at the guys in my Dynamic Group, called “DaysSober Next 7 Days”, pull from the Days Sober field; and then do an automatic calculation to give me the exact date, and day of the week, that the target milestone is actually reached? Of course I know how to get the phone and e-mail information.

 

                Have I asked too much?

 

Thanks again for the help, and also for the nice words about AA.

Silver Super Contributor
Posts: 2,328
Country: USA

Re: Complicated Queries Question

Larry,

 

Glad you got it working!

 

As far as the template is concerned I think it could be done with an ACT! report but it would require a somewhat difficult script embedded in the report to get it working.  Another ACC who posts in this newsgroup named Roy Laudenslager can probably create the report for you but it would be billable.  You can reach him at techbenders.com if you're interested.  I didn't post his e-mail address to reduce his chances of getting spammed.

 

It could also be done with Crystal Reports.  Steve Stroz can write the report for you in Crystal Reports which would be billable as well.  You can reach him at goldcoastadvisors.com.

 

In the interest of full disclosure I wrote and sell a software package called Crystal Clear Essentials which is basically an interface between Crystal Reports and ACT! and includes a set of reports.  You would need to purchase it or Crystal Clear Provider which is just the interface piece to run a Crystal Report that would provide the information you want.


Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!