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]

It's saying "give me all the ORGs whose Org_Id is not returned by the following query."

(SELECT Org_Id FROM Call WHERE Call.OpenBy<>"ESUPPORT");

This is basically a sub-query...
If I were you, I'd run the above subquery on it's own, and look at what's returned. (If it's clearer, you could join the Org table just to see what's going on.)

I'm off home now...so bon courage...I'll look tomorrow to see if there's a resolution.

Dave.

 

[Carrie]

Okay, I thought of something else...the location cannot be CMEDS OR USA...so I added that and ran the query and got nothing.

Here is the sql so you can see it...

SELECT Org.OrgName
FROM (Org INNER JOIN Region ON Org.Region_ID = Region.Region_ID) INNER JOIN Location ON (Org.Location_ID = Location.Location_ID) AND (Region.Region_ID = Location.Region_ID)
WHERE (((Org.WebAccess)=-1) AND ((Org.IsCustomer)=-1) AND ((Org.Inactive)=0) AND ((Region.RegionCode)="MMSW") AND ((Org.Org_ID) Not In (SELECT Org_Id FROM Call WHERE Call.OpenBy<>"ESUPPORT")) AND ((Location.LocationCode)<>"CMEDS" And (Location.LocationCode)="USA"));

 

[dmckinney]

looks like a problem in your criteria.

AND Location.LocationCode<>"CMEDS" And Location.LocationCode<> "USA"

(if this gives error try adding brackets / parentheses to end.)

...definitely off now!!

 

[Carrie]

Okay, for whoever can help me next...I ran the querey and I got syntax error in union query.

Sorry but SQL is another language to me.

 

[dmckinney]

SELECT Org.OrgName
FROM (Org INNER JOIN Region ON Org.Region_ID = Region.Region_ID) INNER JOIN Location ON (Org.Location_ID = Location.Location_ID) AND (Region.Region_ID = Location.Region_ID)
WHERE (((Org.WebAccess)=-1) AND ((Org.IsCustomer)=-1) AND ((Org.Inactive)=0) AND ((Region.RegionCode)="MMSW") AND Org.Org_ID Not In (SELECT Org_Id FROM Call WHERE Call.OpenBy<>"ESUPPORT") AND Location.LocationCode<>"CMEDS" AND Location.LocationCode<>"USA");

 

[Carrie]

I appreciate your continued help.

I did what you said and there are so few. Is there anyway we can do the reverse and find the organizations that have logged an ESUPPORT call?

That will tell me if the other is correct.

 

[dmckinney]

mornin'

I think I misunderstood what the esupport value in the OpenBy field meant so...
in the sql below I've changed
WHERE Call.OpenBy<>"ESUPPORT"
to
WHERE Call.OpenBy="ESUPPORT"
(we're still looking for orgs not in this list.)

I've layed out the SQL more clearly so that you should see yourself what's going on - and make modifications where necessary.

Hope this returns more coherent results.

dave.


SELECT
Org.OrgName
FROM
(Org INNER JOIN Region ON Org.Region_ID = Region.Region_ID) INNER JOIN Location ON (Org.Location_ID = Location.Location_ID) AND (Region.Region_ID = Location.Region_ID)
WHERE
Org.WebAccess=-1
AND
Org.IsCustomer=-1
AND
Org.Inactive=0
AND
Region.RegionCode="MMSW"
AND
Location.LocationCode<>"CMEDS"
AND
Location.LocationCode<>"USA"
AND
Org.Org_ID Not In
(SELECT Org_Id FROM Call WHERE Call.OpenBy="ESUPPORT");

 

[Carrie]

Thank you very much. I got what I needed and now I can dazzle my boss. You are my knight in shining armor.

I still don't understand SQL very well but laying it out the way you did helped me to understand it a bit more.

Thank you.

Carrie

 

[dmckinney]

Glad I could help....if you look at a query in SQL view and it looks like ancient greek here's a couple of tips.

1) Access has a habit of liberally scattering brackets - these are the first things I ditch (except when they actually do something).

2) Don't be afraid to add your own 'carriage returns' or spaces to help see what's going on. Access will ignore them.

3) Once you've made your SQL look nice and tidy, put it somewhere safe! At the first opportunity Access will undo all your hard work, and lose your formatting and throw brackets everywhere. So save it with Notepad or another raw text editor. (Not Word.) ....or why not have a table which contains your sql? hmmm...maybe I should do that.

Enjoy,

Dave.