Help with a JOIN Query

[scott56]

Hi,

I am having trouble with a query that is joining two tables...

The first table is

Contact
which has fields ID, CompanyName, AddressLine1, etc

The second table is
Contact History
which has the fields ID, ContactID, CommunicationID, CreateDate

The Contact History table contains a record of all communication sent to a Contact, the communication is referenced by CommunicationID and the Contact is referenced by the ContactID.

I am now trying to create a Query that will tell me for a particular CommunicationID which Contacts have not received that Communication.

I thought I could use the JOIN queries but I can't seem to get it to return the correct result...

The query I am trying that returns only the Contact History records that do not match the CommunicationID rather than the Contact records is....

SELECT Contact.ID, Contact.CompanyName, [Contact History].ContactID, [Contact History].CommunicationID, [Contact History].CreateDate
FROM Contact LEFT JOIN [Contact History] ON Contact.ID = [Contact History].ContactID
WHERE ((([Contact History].CommunicationID)<>9));

Any help or guidance would be appreciated...

Thanks
Scott

 

[Uncle Gizmo]

FROM Contact LEFT JOIN [Contact History] ON Contact.ID = [Contact History].ContactID

I'm not sure....
but this don't look right.....

 

[scott56]

You might be referring to the different name in the Contact History table for the ContactID matching to the ID in the Contact table.

These are the same types of fields and I have a relationship established in the database on those fields with Contact to Contact History being 1 to many

Scott

 

[wazz]

scott56: jump down to datAdrenaline's response. i misread your question and gave you CommunicationIDs that are not being used, NOT what you were asking for.
============

- i removed the contact information; you can easily add it back in when this part is working.
- i don't know the name of the 'communication' table (and main field) so i made it up (in red).

SELECT [Contact History].ContactID, [Contact History].CommunicationID, 
[Contact History].CreateDate, [COLOR=red]tblCommunication.CommunicationType[/COLOR]
[COLOR=black]FROM tblCommunication [/COLOR]
[COLOR=black]LEFT JOIN [Contact History] [/COLOR]
[COLOR=black]ON tblCommunication.CommunicationID[/COLOR][COLOR=#000000] = [Contact History].CommunicationID[/COLOR]
WHERE ((([Contact History].CommunicationID) Is Null));

 

[wazz]

hmm, i think i misread.
but i think you can replace 'Is Null' with <>9 and get the result you're after.

 

[datAdrenaline]

The first think you need to do is remove the spaces from your Table names! ... those square brackets can, and do, create havoc with the suggestion I am going to propose ... The following SQL will return all the Contacts that have NOT had CommunicationID number 9.

SELECT Contact.ID
       , Contact.CompanyName
FROM Contact
     LEFT JOIN (SELECT *
                FROM [Contact History]
                WHERE CommunicationID = 9) As vTbl
     ON Contact.ID = vTbl.ContactID
WHERE vTbl.CommunicationID Is Null

The reason I make the comment about the spaces in the table names is that when you save this SQL statement as a Query Object in A2003, A2002, and A2000, the parenthesis that encapsulate the sub-query are changed to square brackets and a period is added to the closing square bracket. The resultant SQL statement will look like this ...

SELECT Contact.ID
       , Contact.CompanyName
FROM Contact
     LEFT JOIN [COLOR=red][[/COLOR]SELECT *
                FROM [Contact History]
                WHERE CommunicationID = 9[COLOR=red]].[/COLOR] As vTbl
    ON Contact.ID = vTbl.ContactID
WHERE vTbl.CommunicationID Is Null

Which is the sub-query syntax for Access 97 (A97 will not accept the parenthesis syntax) ... the issue is, you can not have nested square brackets. (side note: Access won't bark until you try to modify the statment)

So, it is best to remove spaces and special characters from field names and table names. Also, one should not use reserved words as field names, like "SELECT" would be a really bad name for a field! ...

Now ... if you can not modify your table names and field names to accomodate that best practice, you can work around this by creating a Query Object with an SQL statement that matches the sub-query, then use that new Query Object name in place of the embedd SQL statment ...

SELECT Contact.ID
       , Contact.CompanyName
FROM Contact
     LEFT JOIN [COLOR=red]selCommunications[/COLOR]
     ON Contact.ID = [COLOR=#ff0000]selCommunications[/COLOR].ContactID
WHERE [COLOR=#ff0000]selCommunications[/COLOR].CommunicationID Is Null

Where selCommunications is refering to a Query Object with a SQL statement that looks like this:

SELECT *
FROM [Contact History]
WHERE CommunicationID = 9

......

Now ... if all that seem to be something you don't want to try, or you just want an other alternative, you can negate an IN clause ...

SELECT Contact.ID
       , Contact.CompanyName
FROM Contact
WHERE ContactID NOT IN (SELECT ContactID
                        FROM [Contact History]
                        WHERE CommunicationID = 9)

An IN clause query/sql does NOT suffer from the paren to square bracket issue, but .. a "NOT IN" is tagged with having a HORRIBLE reputation for speed with JET|ACE databases (the engine behind Access). An IN clause works great, but as soon as you negate it with the NOT, JET|ACE is unable to take advantage of indexes.

It is my recommendation to go with what I call the "Frustrated Left Join" (I stole the name from someone else ) options mentioned in the beginning of this post.

 

[scott56]

Thanks for that.....I have taken the frustrated Left join approach and now have it working..

Much appreciated