SQL Query to list Accounts with particular reference code in history over a period of time

[mharkin]

Hi,
I am trying to build a list of all emails that have been sent in the past 3 months with the reference “Confirmation of Debt Subm” and createdby = Admin and key1 =SGPDC

I thought to do this i should input the following:

[highlight #E9B96E]SELECT ContHist.Ref,ContHist.Createby, ContHist.Createon, Contact1.Key1
FROM ContHist, Contact1
WHERE Ref LIKE 'Confirmation%' AND ContHist.Createby = 'Admin' AND Contact1.Key1 = 'SGPDC'AND ContHist.Createon >= 25/10/12
ORDER BY Createon[/highlight]

It does produce a list However it is a incorrect list; its is showing CreateOn dates from 2009 and there is hundreds of Duplicates.

Can anyone please help???
Any help with this would be very much appreciated.
Thank You
Mark

 

[DJHunt]

That's because you do not understand the construct of GoldMine. Whenever one receives an email message in GoldMine, there is a CAL and MAILBOX record created. Once that email message has been Filed/Replied To the CAL record is converted to a CONTHIST record. In both cases there is a relational link between the MAILBOX and the other two records.

Hence, you may want to look for your information in the MAILBOX table as opposed to the CONTHIST table.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US

 

[mharkin]

Thank you for that advise, I have now changed the query to look at the mailbox table and i have got the info i need.

Thanks

 

[DJHunt]

That's excellent.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US