How would I create an SP from this Access query?

[1DMF]

Hi I have an append query I want to turn into an SP so it runs faster on the server, only I am using some access functions.

How do I convert this SQL into an SP ?

INSERT INTO Search_Contacts ( CompanyName, ContactID )
SELECT Contacts.CompanyName, Contacts.ContactID
FROM Contacts
WHERE (((Contacts.MembershipLevel)="Appointed Rep")) OR (((Contacts.ContactTypeID) Like '*- AR')) OR (((Contacts.ContactTypeID) Like 'Prospect - *'))
GROUP BY Contacts.CompanyName, Contacts.ContactID
HAVING (((Contacts.CompanyName) Is Not Null And (Contacts.CompanyName)<>')) OR (((Contacts.CompanyName) Is Not Null And (Contacts.CompanyName)<>')) OR (((Contacts.CompanyName) Is Not Null And (Contacts.CompanyName)<>') AND ((Nz(DLookUp("ContactID","Contacts","CompanyName = '" & [companyname] & "' AND (ContactTypeID = 'Prospect - AR' OR MembershipLevel = 'Appointed Rep')"),0))=0));

Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[markros]

Do you want to convert it to T-SQL syntax?

create procedure Add_Contacts 
AS
  SET NOCOUNT ON;
  INSERT INTO Search_Contacts (CompanyName, ContactID)
  SELECT Contacts.CompanyName, Contacts.ContactID
FROM Contacts
WHERE (Contacts.MembershipLevel='Appointed Rep' OR Contacts.ContactTypeID Like '%- AR' OR Contacts.ContactTypeID Like 'Prospect - %') AND CompanyName IS NOT NULL AND CompanyName <> '

GROUP BY Contacts.CompanyName, Contacts.ContactID

The complex HAVING clause is not clear to me, but I think you need to get these types of contacts.

PluralSight Learning Library

 

[PWise]

something like this

INSERT INTO Search_Contacts ( CompanyName, ContactID )
SELECT Contacts.CompanyName, Contacts.ContactID
FROM Contacts
inner join (
            Select isnull(ContactID,0)ContactID
            From Contacts
            Where (ContactTypeID = 'Prospect - AR' or MembershipLevel = 'Appointed Rep')
            And isnull(ContactID,0)=0
            )nullcontacts
on nullcontacts.ContactID=Contacts.ContactID
And (((Contacts.MembershipLevel)="Appointed Rep")) 
OR (((Contacts.ContactTypeID) Like '*- AR')) 
OR (((Contacts.ContactTypeID) Like 'Prospect - *'))
And Contacts.CompanyName>'
GROUP BY Contacts.CompanyName, Contacts.ContactID

 

[PWise]

Sorry forgot to change
wildcard char to '%'

INSERT INTO Search_Contacts ( CompanyName, ContactID )
SELECT Contacts.CompanyName, Contacts.ContactID
FROM Contacts
inner join (
Select isnull(ContactID,0)ContactID
From Contacts
Where (ContactTypeID = 'Prospect - AR' or MembershipLevel = 'Appointed Rep')
And isnull(ContactID,0)=0
)nullcontacts
on nullcontacts.ContactID=Contacts.ContactID
And Contacts.CompanyName>'
GROUP BY Contacts.CompanyName, Contacts.ContactID

i think your dlookup will limit you to null contact ids with
ContactTypeID = 'Prospect - AR' or MembershipLevel = 'Appointed Rep' and a CompanyName >'

 

[1DMF]

what's nullcontacts?

is there an 'AS' missing in that , or is that the syntax for naming a table?

the dlookup could be changed to a count...

dcount("Contacts","CompanyName = '" & [companyname] & "' AND (ContactTypeID = 'Prospect - AR' OR MembershipLevel = 'Appointed Rep')") = 0

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

I've rtied just a simple query to start with using

SELECT     Contacts.CompanyName, Contacts.ContactID
FROM         Contacts INNER JOIN
                          (SELECT     isnull(ContactID, 0) ContactID
                            FROM          Contacts
                            WHERE      (ContactTypeID = 'Prospect - AR' OR
                                                   MembershipLevel = 'Appointed Rep') AND isnull(ContactID, 0) = 0) nullcontacts ON nullcontacts.ContactID = Contacts.ContactID AND 
                      Contacts.MembershipLevel = 'Appointed Rep' OR Contacts.ContactTypeID LIKE 'Prospect - %' AND Contacts.CompanyName > '
GROUP BY Contacts.CompanyName, Contacts.ContactID

it returns no results , it should return over 13k records?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

looking at this, it seems as though you think I want null contactID's?

The contacts table has multiple records for the one company, this select is to get either those who are member AR's (MembershipLevel = 'Appointed Rep') OR prospects (ContactTypeID = 'Prospect - AR')

However, some prospect entries do not have a master 'AR' record, so i want to include those records that are just 'Prospect - %', but what I don't want is duplicate records for the same company.

so if they have two records 'Prospect - AR' & a 'Prospect - xxx' , I ony want the 'Prospect - AR' however, if there is only a 'Prospect - xxx' then I want to include that record in the results.

Does that make it any clearer?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

ok, i think I've cracked it with this...

SELECT      Contacts.CompanyName, Contacts.ContactID
FROM         Contacts INNER JOIN
                          (SELECT     isnull(ContactID, 0) ContactID
                            FROM          Contacts
                            WHERE      (ContactTypeID = 'Prospect - AR' OR
                                                   MembershipLevel = 'Appointed Rep')) nullcontacts ON nullcontacts.ContactID = Contacts.ContactID
WHERE     (Contacts.MembershipLevel = 'Appointed Rep') AND (Contacts.CompanyName > ') OR
                      (Contacts.CompanyName > ') AND (Contacts.ContactTypeID LIKE 'Prospect - %')
GROUP BY Contacts.CompanyName, Contacts.ContactID

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta