Nested And/Or query

[kagoodwin13]

Hi all. I've made some pretty significant progress on my database, thanks to all the help I've received here. I'm stuck on something right now, and I'm wondering if I can get a quick fix.



I am pulling trucking data to create queries that will calculate trucking cost per mile. Sometimes the trucks run in a round-trip (RT), and other times they only run one way (1-Way or 1W). This formula also includes number of each trip per week to create a weighted average. I am also creating a query for each year so that I can create a summarized report for round-trip and one-way cost per mile per year.

I'd like to query for data on trucking lanes with round-trips over 200 miles, or one-ways over 100 miles. This is so I can eliminate small runs that would unfairly skew cost-per-mile results. I also need to filter the data by year.

So far I have this query:

SELECT [Truckload].[RT Miles], [Truckload].[RT Rates], [Truckload].[RT per Week], [Truckload].[1-Way Miles], [Truckload].[1W Rates], [Truckload].[1W Per Week], [Truckload].[Start Date], [Truckload].[End Date]
FROM [Truckload]
WHERE ((([Truckload].[RT Miles])>200)) AND ((([Truckload].[1-Way Miles])>100) AND (([Truckload].[Start Date])<#12/31/2010#) AND (([Truckload].[End Date])>#1/1/2010#));

The tricky part is that sometimes there is a shipping lane with both round-trip and one-way rates, and other times they only have one of each. Therefore I'd like to capture [RT Miles]>200 OR [1-Way Miles]>100. When I change this:

WHERE ((([Truckload].[RT Miles])>200)) AND ((([Truckload].[1-Way Miles])>100)

to this:

WHERE ((([Truckload].[RT Miles])>200)) OR ((([Truckload].[1-Way Miles])>100)

the query returns results that are outside of the date range.

I've tried switching around the two halves of the WHERE statement as well so that it reads like this:

SELECT [Truckload].[RT Miles], [Truckload].[RT Rates], [Truckload].[RT per Week], [Truckload].[1-Way Miles], [Truckload].[1W Rates], [Truckload].[1W Per Week], [Truckload].[Start Date], [Truckload].[End Date]
FROM [Truckload]
WHERE (([Truckload].[Start Date])<#12/31/2010#) AND (([Truckload].[End Date])>#1/1/2010#)) AND ((([Truckload].[RT Miles])>200)) OR ((([Truckload].[1-Way Miles])>100);

but I get a syntax error that I can't solve then.

Any suggestions?

[kagoodwin13]

Solved:

WHERE (([Truckload].[Start Date])<#12/31/2010#) AND (([Truckload].[End Date])>#1/1/2010#) AND ([Truckload].[RT Miles]>200)

OR ((([Truckload].[Start Date])<#12/31/2010#) AND ([Truckload].[End Date])>#1/1/2010#) AND ([Truckload].[1-Way Miles]>100);

[pbaldy]

This thread is marked as solved; is it? If not, you need to parentheses the items in the WHERE clause when you mix AND and OR:

A And (B Or C)