Community
Showing results for 
Search instead for 
Do you mean 
Reply

Query not giving desired results

New Member
Posts: 7
Country: United_Kingdom

Query not giving desired results

Hi.

I have created an advanced query to filter the contact opportunities. The type of query is a contact query.

I am searching for records that are older than 200days but added within the last 400 days and the product name is "PAT SURVEY".

 

I am using the report "Opportunities by Contact" to show the results.

 

But, if the query finds 1 record matching the criteria, it will show every single record for that contact, even ones that don't match the criteria.

 

Am i doing something wrong?

New Member
Posts: 7
Country: United_Kingdom

Re: Query not giving desired results

This is how i setup my query:

 

Search for - Contact

 

Type - Opportunity  Field Name - Open Date Operator - Older than Value - 200

Type - Opportunity  Field Name - Open Date Operator - Within Last Value - 400

Type - Opportunity  Field Name - Product Operator - equal to Value - PAT survey

Copper Contributor
Posts: 16
Country: United States

Re: Query not giving desired results

The query is working fine.  The problem is the structure of the Report.  The report first looks a the Contact, then lists all opportunities.  You have limited ability to filter what opps you can view.  Here are some options.

 

1.  After the running the Lookup, export the the results to Excel.  In Excel you can slice and dice the opportunities any way you want.  

2.  Purchase an ACT! Add-On report writer like Stonefield Query.  These programs offer much more control over the output of your report.

3.  Install the ACT Reader.  This will give you the ability to view the underlying opportunity table.  I like viewing the data both in Excel and Access.  Here's a KB to get you started 17538.

 

Regards,

 

Tom Koller, "The ACT Guy"

info@computingconnections.com

Platinum Elite Contributor
Posts: 6,653
Country: USA

Re: Query not giving desired results

The problem isn't the query, but you can't carry the query results into the oppertunity subreport of a contact based report. However, there are filters that you can use on the oppertunity subreport. The report filters for date range in oppertunities uses the estimated close date for any open oppertunity and the actual close date for all other oppertunity status settings. It's likely that if you use the proper report filter settings you won't even need to do the query.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 7
Country: United_Kingdom

Re: Query not giving desired results

How would i go about doing it without the query?

I need to show all records matching the criteria, regardless of whether the status is open or closed.

Platinum Elite Contributor
Posts: 6,653
Country: USA

Re: Query not giving desired results

 

the query would not help. You'll get all the oppertunities for the contact with in the date range. Your best bet for that would be a third party report editor like the Durkin Action reports.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Query not giving desired results

The product Roy is referring to is named Impact Report Designer v5. It comes with over 60 pre-designed reports. You can create your own or duplicate an existing report to modify.

 

Here is the link: http://www.durkincomputing.com/

 

-- Jim Durkin