Where, OR, AND (1 Viewer)

[shafh]

Hello People,
I am having problems with a WHERE(…OR..AND) that I am trying to implement in a Query. I am trying to write the query so only those records that have been updated in the last 30 days and updated only by John Smith will show up. I’ve tried a lot of combinations but nothing seems to be working. I would really appreciate some help.
Here’s how I’ve written it up right now:

WHERE (((IndustryCurrentEnvironment.UpdateDate)>Date()-30)
OR (((IndustryOutlook.UpdateDate)>Date()-30))
OR (((IndustryPositives.UpdateDate)>Date()-30))
OR (((ParentCreditFundamentals.UpdateDate)>Date()-30))

AND ((IndustryCurrentEnvironment.AnalystName)="John Smith")
AND ((IndustryOutlook.AnalystName)="John Smith")
AND ((IndustryPositives.AnalystName)="John Smith")
AND ((ParentCreditFundamentals.AnalystName)="John Smith")
)

Thanks,
Sam

 

[Peter D]

Try this:

WHERE (

(
((IndustryCurrentEnvironment.UpdateDate)>Date()-30)
OR (((IndustryOutlook.UpdateDate)>Date()-30))
OR (((IndustryPositives.UpdateDate)>Date()-30))
OR (((ParentCreditFundamentals.UpdateDate)>Date()-30))
)

AND

(
((IndustryCurrentEnvironment.AnalystName)="John Smith")
AND ((IndustryOutlook.AnalystName)="John Smith")
AND ((IndustryPositives.AnalystName)="John Smith")
AND ((ParentCreditFundamentals.AnalystName)="John Smith")
)

)

But then "John Smith" has to be the value of AnalystName in ALL AnalystName fields. It sounds like that's what you want but I'm not sure.

Hope this helps,

 

[Pat Hartman]

You have two separate complex conditions. The first tests several dates and the second tests several text fields. You need to enclose the first complex condition (the four date tests) within a set of parentheses and do the same for the second complex condition (the four AnalystName tests) and connect the two with the AND operator. To satisfy this condition, any one of the date tests must be true AND any one of the AnalystName tests must be true. Is this what you want?
WHERE
(IndustryCurrentEnvironment.UpdateDate > Date() -30
OR IndustryOutlook.UpdateDate > Date() -30
OR IndustryPositives.UpdateDate > Date() -30
OR ParentCreditFundamentals.UpdateDate > Date() -30)
AND
(IndustryCurrentEnvironment.AnalystName ="John Smith"
OR IndustryOutlook.AnalystName ="John Smith"
OR IndustryPositives.AnalystName ="John Smith"
OR ParentCreditFundamentals.AnalystName ="John Smith")

OR is it that you want the records that were updated by John Smith within the the past 30 days? In this case you have 4 complex conditions any one of which can be true. Each of the complex conditions contains two tests BOTH of which must be true for the condition to be true.
WHERE
(IndustryCurrentEnvironment.UpdateDate > Date() -30 And IndustryCurrentEnvironment.AnalystName ="John Smith")
OR
(IndustryOutlook.UpdateDate > Date() -30 AND IndustryOutlook.AnalystName ="John Smith")
OR
(IndustryPositives.UpdateDate > Date() -30 AND IndustryPositives.AnalystName ="John Smith")
OR
(ParentCreditFundamentals.UpdateDate > Date() -30 AND ParentCreditFundamentals.AnalystName ="John Smith")

The parentheses are critical in both of these possibilities.

 

[shafh]

Thanks a lot Pat and Peter.
Pat your second suggestion is almost exactly what i want. However it still has one slight problem. What I want to records from within the last 30 days only. Within these records I want records created by John Smith only.
When I use your second query then I get all records in the last 30 days however the problem is I also get (for example) where the IndustryCurrentEnvironment was created by John Smith but the IndustryOutlook was updated by Jane Doe in the same row.
Is there a way to get just the stuff that John Smith did in the last 30 days and perhaps have the fields left blank for the rest of the row if Jane Doe did something else on that date.
Thanks

 

[Pat Hartman]

Looking at the column names, it appears that you have joined 4 tables. If that is the case, perhaps what you want is really a union. That will produce a recordset that includes only activity by John. Union the 4 tables and use the separate OR conditions for the appropriate select.

 

[shafh]

is it possible

Can one have a

SELECT
FROM
WHERE
statement followed by a
SELECT
FROM
WHERE
followed by a
UNION SELECT
FROM
WHERE

to union the second and third SELECT's but not the first SELECTFROMWHERE statement.

 

[Pat Hartman]

No. A union query takes the separate resultsets created by its component queries and returns them as a single resultset. What you are asking doesn't make any sense. Rather than trying to project a solution, try phrasing your question in english.

 

[shafh]

Thanks

Thanks a lot Pat and Peter. I really appreciate both your help. I was able to figure it out thanks to your help and advice. Have a great weekend!