"Unsupported table" error when running report

[sconlon]

To do a report on all appointments (using Crystal Reports) for Goldmine 6.5 dBase, I am using the following sql query within an Access database that has CAL, CONTHIST and CONTACT1 as linked tables.

SELECT Cal.CREATEON, Contact1.COMPANY, Contact1.Contact, Cal.ONDATE, Cal.ONTIME, Cal.DURATION, Cal.ACTVCODE, Cal.REF, Cal.USERID
FROM Cal INNER JOIN Contact1 ON Cal.ACCOUNTNO=Contact1.ACCOUNTNO
WHERE (((Cal.RECTYPE)="A"));
UNION SELECT Conthist.CREATEON, Contact1.COMPANY, Contact1.Contact, ContHist.ONDATE, ContHist.ONTIME, ContHist.DURATION, ContHist.ACTVCODE, ContHist.REF, ContHist.USERID
FROM ContHist INNER JOIN Contact1 ON ContHist.ACCOUNTNO = Contact1.ACCOUNTNO
WHERE (((ContHist.RECTYPE)="A"));

When I run this in Crystal it works ok but when I run the report from within Goldmine I get an "Unsupported table" error. After some investigation I have determined that this error will occur whenever you use a query on linked tables within Access. (Note that the SQL UNION query within Access was the only way I could find of getting all appointment records from both the CAL and CONTHIST databases.)

Many thanks for any suggestions on getting this working within Goldmine or for an alternative solution.

Seamus

 

[richardy]

Seamus,

Thats one of the limitations of GoldMine dbase - it doesnt like the UNION and JOIN statements. You need to move to SQL for that.

Regards,

Richard.
PRIOR Analytics UK

http://www.prior-analytics.com

Winners of the LBA Customer Service Award.

 

[sconlon]

Thanks for that Richard, I may consider moving to SQL but it does sound like a bit of grief and, of course, I will probably have to change all my existing Crystal Reports.

By the way, I haven't looked at Goldmine's own reporting tool but I would doubt that it would be as powerful as Crystal Reports.

Seamus