Add to query

[aw23]

I have two tables. One is company and 2 is action. Everytime an employee calls or emails a client there is a new action in the action table. So basically I want to select all companies which have no action or no action associated with them for the last x amount of days. I now have a new action called Fax. I want to ignore those companies that have received a fax, and not include it in the action. So that if a company has not recieved any avtion it will be included in the recordset as well as a company who has recieved only a fax and not any other action. The following is my sql statement. How can I add that?
Basically I need to say either there is no action, or if there is than the atype is anything but fax.

"SELECT c.company_id, c.com_name, c.Country_ID, Max(a.On_Date) As LastDate, c.com_rep" _
 & " FROM company AS c LEFT JOIN actions AS a ON c.company_id = a.company_id" _
 & " WHERE c.Country_ID Is Not Null and c.com_name is not null and c.cominfo_status = 'Active'" _
 & " GROUP BY c.company_id, c.com_name, c.Country_ID, c.com_rep, a.company_id" _
 & " HAVING (a.company_id Is Null  Or Max(a.On_Date) < Date() - " & numDays & ")"

 

[aw23]

hmmm...Any suggestions?

 

[ZmrAbdulla]

Not sure this will help you..
Try Xor Operator in the VBA help file

If one, and only one, of the expressions evaluates to True, result is True. However, if either expression is Null, result is also Null

regards

Zameer Abdulla
Visit Me
Where to pay your [purple]Tsunami[/purple] relief donations?

 

[aw23]

How about this maybe this could help. How can I select all those companies that only have atype=fax.
Maybe then I could figure out how to compbine the two recordsets

the following selects all that have atype=fax but I want it to be those that have fax exculsively.

SELECT Company.*, Actions.aType
FROM Company INNER JOIN Actions ON Company.Company_ID = Actions.Company_ID
WHERE (((Actions.aType)="fax"));

 

[PHV]

those that have fax exculsively
SELECT Company_ID
FROM Actions
GROUP BY Company_ID
HAVING Count(*)=1 AND Max(Actions.aType)="fax";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244