Help with query

[Carrie]

Our clients can now log a support call via email. It shows up as an ESUPPORT call.

I want to do a query that shows all clients that has web access that has not logged an ESUPPORT call.

I am doing Org;Open By,<>ESUPPORT;Web Access,-1.

I am now getting a report of every call that was not logged by ESUPPORT for organizations that have web access.

So, I am getting every call that was logged by an organization with web access that was not logged via the web.

So, I am getting the same organization more than once and they may have logged an ESUPPORT call...I am just getting the calls that weren't open by ESUPPORT.

How do I get a report of all organizations that have web access that have NEVER logged an ESUPPORT call?

_________________
Thanks,

Carrie
This message was edited by Carrie on 2003-01-02 11:28

 

[dmckinney]

can you post your sql please....

it's going to be something like

select * from tblClient where webaccess =true and ClientId NOT IN (Select ClientId from tblLogs)

But I don't know what your tables, fields etc are called.

(or use NOT EXISTS)

 

[Corticus]

It sounds like maybe you need to add distinct, or distinct row right after the SELECT in your SQL, but I can't tell without seeing it. Could you post you SQL, or at least the fields and type of data they contain?

Thanks,
Corticus

 

[Carrie]

There are other fields but the previous post (which I have edited to hopefully make clearer) is the basic idea that I need to do.

SELECT Org.OrgName, Call.OpenBy
FROM (Org INNER JOIN Call ON Org.Org_ID = Call.Org_ID) INNER JOIN Region ON Org.Region_ID = Region.Region_ID
WHERE (((Call.OpenBy)<>"ESUPPORT") AND ((Org.WebAccess)=-1) AND ((Org.IsCustomer)=-1) AND ((Org.Inactive)=0) AND ((Region.RegionCode)="MMSW"));


_________________
Thanks,

Carrie
This message was edited by Carrie on 2003-01-02 11:31

 

[dmckinney]

Try this...I wrote it freehand (i.e. without Access) so no guarantees.

SELECT Org.OrgName
FROM Org INNER JOIN Region
ON Org.Region_Id=Region.Region_Id
WHERE
Org.WebAccess=-1 AND Org.IsCustomer=-1
AND Org.Inactive=0 AND Region.RegionCode="MMSW"
AND Org_Id NOT IN (SELECT Org_Id FROM Call WHERE Call.OpenBy<>"ESUPPORT");

**** EDITED TO SPELL OrgId CORRECTLY ****
This message was edited by dmckinney on 2003-01-02 12:02
This message was edited by dmckinney on 2003-01-02 12:03

 

[Carrie]

Syntax error in JOIN operation.

Thanks for trying.

 

[Carrie]

I changed line 3...

ON Ord.Region_Id=Region.Region_Id

to...

ON Org.Region_Id=Region.Region_Id

and then I get "Enter Perameter Value" Org ID...

What is this? If it is what I think it is there are way too many customers to enter each org id and if it is a range then I still don't know what to put because there id's aren't from 0 - 100 they are very weird.

Help

 

[dmckinney]

change orgid to org_id...see edited version above.
This message was edited by dmckinney on 2003-01-02 12:04

 

[Carrie]

I did this and I only got seven customers.

It seems like this would be to few...I don't understand the last line could you explain it for me?

 

[Corticus]

Hi,

I tried to recreate and ended up with this SQL:
SELECT Org.OrgName, Call.OpenBy, Org.WebAccess, Org.IsCustomer, Org.Inactive, Region.RegionCode
FROM (Call INNER JOIN Org ON Call.Org_ID = Org.OrgID) INNER JOIN Region ON Org.RegionID = Region.RegionCode
WHERE (((Call.OpenBy)<>"ESupport") AND ((Org.WebAccess)=-1) AND ((Org.IsCustomer)=-1) AND ((Org.Inactive)=0) AND ((Region.RegionCode)="MMSW"));

Which I think is what you have...soooo I'm not sure, but it seems like you have it sorted so I'll wait to see what you come up with....

Corticus
This message was edited by Corticus on 2003-01-02 12:11