Community
Showing results for 
Search instead for 
Do you mean 
Reply

Advanced Query

Accepted Solution Solved
Astute Commentator
Posts: 30
Country: USA
Accepted Solution

Advanced Query

We are a company that does rehab treatments over the web and have therapists in remote locations. With each contact we have user 10 user fields labeled  treatment 1 and till treatment 10 also a filed for the therapists name. In the treatment user field we put the treatment date. Is there a way to query the treatments done between a certain date range. Now we query by therapist then turn into a spreadsheet and exclude treatments outside the date field were looking for but there are quite a few steps. 

Suggestions?


Accepted Solutions
Solution
Accepted by topic author rizzo66
‎09-25-2015 03:20 AM
Copper Elite Contributor
Posts: 514
Country: USA

Re: Advanced Query

Don't forget to save your query.  That way you won't have to redo all those mouse clicks the next time you want to run it or modify it.

Mark Hammer
ACT! Fanatic

View solution in original post


All Replies
Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Advanced Query

[ Edited ]

Is the Treatment Date field set up as a date-type field? If, so you should be able to run an advanced query on that field for records on/before and on/after selected dates. Once you have the records in a contact list, then you can use the Export to Excel option on the contact list toolbar to create a spreadsheet. You can also save the advanced query so you can run it whenever you need, just changing the date range each time.

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.

Copper Elite Contributor
Posts: 514
Country: USA

Re: Advanced Query

You could also use the OLEDB2 provided to hook your ACT! database up to a live excel sheet that would refresh data when you wanted to see the latest and greatest.

Mark Hammer
ACT! Fanatic
Astute Commentator
Posts: 30
Country: USA

Re: Advanced Query

Yes it is a date field treatments 1 through 10 I imagine you do a before and after a date but trying to get an or so can search multiple treatments - I'm really a rookie at this we had a consultant set it up but after a lot of money we still are short of what we want it to do

Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Advanced Query

What exactly are you trying to look up? I may be able to suggest a query you can try.

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.

Astute Commentator
Posts: 30
Country: USA

Re: Advanced Query

We have 10 fields treatments 1 through 10  that our therapists fill in a date when they give a treatment to a contact. We are trying to query treatments from a date range such as 4/1/2011 to 4/15/2011 we then can turn it into a excel spread sheet and sort by therapist to easily get the number of treatments a given therapist did  within  that date range. 

Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Advanced Query

[ Edited ]

If those fields are date-type fields, then you should be able to get the info you need using the following steps:

- Go to Lookup > Advanced > Advanced Query

-Criteria:

Search for: Contact

Type: Contact

Field Name: [Date field]

Operator: On or After

Value: 4/1/2011

(click Add to List)

Field Name: [same Date Field as above]

Operator: On or Before

Value: 4/15/2011

(And/Or value after first criteria should be "And")

- Save this query so you can run it in the future. Would just need to double-click on each line item to change the dates. You can also add other criteria, such as therapist name, to narrow the results.

 

This should capture all date values between 4/1/2011 and 4/15/2011, inclusive. Once the resulting contact list is displayed, you can click the Export to Excel button on the toolbar of the contact list to create a spreadsheet. You can then sort the spreadsheet in Excel as desired. Prior to exporting, click the Options button in the upper right of the Contact List and choose Customize Columns. Add or remove columns to get the fields you want exported as the Export to Excel will only export the columns you have chosen to display. You can also use the File/Export option to export to a .csv file and open that in Excel.

 

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.

Astute Commentator
Posts: 30
Country: USA

Re: Advanced Query

Thanks

I would do that for each treatment firld correct?

Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Advanced Query

You can do a separate query for each field or you can do a combined query for all the treatment date fields.

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.

Solution
Accepted by topic author rizzo66
‎09-25-2015 03:20 AM
Copper Elite Contributor
Posts: 514
Country: USA

Re: Advanced Query

Don't forget to save your query.  That way you won't have to redo all those mouse clicks the next time you want to run it or modify it.

Mark Hammer
ACT! Fanatic