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:
Code:
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:
Code:
WHERE ((([Truckload].[RT Miles])>200)) AND ((([Truckload].[1-Way Miles])>100)
to this:
Code:
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:
Code:
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?