Community
Showing results for 
Search instead for 
Do you mean 
Reply

Having a problem joining to the Activity table

New Member
Posts: 2
Country: United_Kingdom

Having a problem joining to the Activity table

Hi

 

I am trying to get a list of activity's for a contact in Excel - however I cannot join to the Activity table.

 

So I have used the OLD DB connector in Excel and am able to get all opportunity, company and contact details however as soon as I try and join the activity table I get an error.

 

The following query returns lots of records showing that the CONTACT_ACTIVITY is linked to the CONTACT.

 

select CONTACT.ADDRESS_1, CONTACT.ADDRESS_2, CONTACT.ADDRESS_3, CONTACT.CITY, CONTACT.STATE from CONTACT
inner join CONTACT_ACTIVITY on CONTACT.CONTACTID = CONTACT_ACTIVITY.CONTACTID

 

However as soon as I add the activity table in the query below (have tried ACTIVITY AND TBL_ACTIVITY) it returns an error:

 

select CONTACT.ADDRESS_1, CONTACT.ADDRESS_2, CONTACT.ADDRESS_3, CONTACT.CITY, CONTACT.STATE from CONTACT
inner join CONTACT_ACTIVITY on CONTACT.CONTACTID = CONTACT_ACTIVITY.CONTACTID
inner join ACTIVITY on CONTACT_ACTIVITY.ACTIVITYID = ACTIVITY .ACTIVITYID

 

Any ideas on why I cannot connect. 

 

I did also find that when running ACTDIAG and looking at the db reports that the activity table does not list any of the field names.

 

Many thanks

 

Myles

Administrator
Posts: 568
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Any more information on what the error is?

What version of Act! are you connecting to?
New Member
Posts: 2
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hi Jon

 

Apologies for the delay been off enjoying the Christmas festivities.

 

The error in Excel is:

 

"The query did not run, or the database table could not be opened.

 

Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again."

 

The database is fine as the previous query runs, however it just cannot open the ACTIVITY table.

 

Connecting to ACT v18.

 

Let me know if there is anything else I can provide to solve.

 

Many thanks

 

Myles

 

 

 

 

Administrator
Posts: 568
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Can I ask what method of OLE DB connection you're using?

It might be useful to take a look at this KB:
http://kb.act.com/app/answers/detail/a_id/25837

The attached document has some sample queries, which I've confirmed DO work.

Including:

SELECT CONTACT.*, ACTIVITY.*
FROM CONTACT_ACTIVITY INNER JOIN
CONTACT ON CONTACT_ACTIVITY.CONTACTID = CONTACT.CONTACTID INNER JOIN
ACTIVITY ON CONTACT_ACTIVITY.ACTIVITYID = ACTIVITY.ACTIVITYID

I can confirm that this query worked, returning the full contents of the both contact and the activity tables.

I did have some issues when trying to narrow down the columns being selected though, including data initialisation errors as you've mentioned.

Take a look at that KB and see if it's of any help.