Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Copper Contributor
Posts: 5
Registered: ‎04-25-2012
Accepted Solution

Account Report selecting too much data

Install Info:  We are running Saleslogix Web version 7.54  SQL Server 2008 and Crystal Reports XIr2

 

I am trying to create a report to show Accounts with old products that do not also have a new / replacement product.  The tables I am using in the report are ACCOUNT, ACCOUNTPRODUCT, ADDRESS, and USERINFO.  I have the report grouped on the following fields ACCOUNT.ACCOUNTMANAGERID ==> ADDRESS.STATE ==> ACCOUNT.ACCOUNTID ==> ACCOUNTPRODUCT.ACTUALID.  I need to be able to view all ACCOUNT.ACCOUNTID'S that have products with an ACCOUNTPRODUCT.ACTUALID in list producta, productb, productc, productd but not show accounts if they also have a product in list producte, productf, productg.

 

It would be even better if I could show this as a group in the web client.

 

All Idea's will be appreciated.

 

Thanks in advance

Highlighted
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Account Report selecting too much data

There is no record that joins an AccountID to an Actualid...AccountId points to an Account.....ActualId points to Product.ActualId that actually is the SKU or Ordering Code for a Product. ActualId is no a CHAR(12) ID field, nor is it ProductId.....

RJ Samp
Highlighted
Copper Contributor
Posts: 5
Registered: ‎04-25-2012

Re: Account Report selecting too much data

I am not trying to relate the actualid directly to the accountid.  The account table contains an acccountid and the accountproduct table has an accountid.  I am trying to create a report to show me only accounts that have older accountproducts with specific values in a list of accountproduct.actualid's but not accounts that have a replacement product as well from a separate list of accountproduct.actualid's.

 

Example:  We have approximately 6000 accounts with products from the following list (110,2400,3300,3900,mark2,mark3)  of these 6000 accounts I want to find the ones that don't also have products from the following list. (210,2020,3250,3320,A2O)

 

I hope this is more clear.

Highlighted
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Account Report selecting too much data

what is the query being used?

RJ Samp
Highlighted
Copper Contributor
Posts: 5
Registered: ‎04-25-2012

Re: Account Report selecting too much data

I used a suggestion from users on IT-Toolbox to create a View. 

 

This was the code I used to create 2 separate views I can now use for groups or reports.

 

CREATE VIEW [ACCOUNTSWITHNEW]AS

SELECT DISTINCT ACCOUNTID

FROM ACCOUNTPRODUCT

WHERE ACCOUNTPRODUCT.ACTUALID IN ('3320','3250','2020','210','A2O') 

 

CREATE VIEW [ACCOUNTSWITHOLD]AS

SELECT DISTINCT ACCOUNTID

FROM ACCOUNTPRODUCT

WHERE ACCOUNTPRODUCT.ACTUALID IN ('3MO','3MO+’,‘110','2400','3300','3900',’Mark 2’,’Mark 3’)