Enclosed is a sample database. The problem is there is a SQL Statement I cannot get to work right The statement I was trying to make is this
select [login to view URL] from inventory a, additionalinfo b where a.id=[login to view URL] and min([login to view URL])>'1975' and max([login to view URL])<'1975' and b.make=10 and a.whatpart=1 group by [login to view URL]
In this case, it should select at least one because 1975 is after 1968 which is the minimum of the earliest year possible. Plus 1983 is less then 1985 which is the maximum year possible. However, when I try to use the max or min in the where clause, I get an error. It says a subquery is necessary. I need the statement modified so the min and max functions will work when trying to find what records to show. I don't know if there is still something wrong with the logic or not. The purpose of this is to what parts fit the years of vehicles. In this example, for the inventory item, it fits 68-78 and 79-85. That would also mean if the numbers 66-86 was used, this would still work because part of the years used it fits. However, if the numbers being searched was 64-65, then it nothing would show up. Let me know if you have any questions. The relationships are inventory.id=additionalinfo.newpart. That relates the parts to the years it fits.
## Deliverables
The SQL Statement to find matches for the span of years. Here are a few examples
1. 66-67 for the search
years found
64-65 and 69-70
in this case, nothing would show up because none of the years fit.
2. 65-90
years found
66-88
in this case, it should find the record because 66 to 88 is between the span of years of 65 to 90.
Let me know if you have any further questions. I will need the query that would be needed with SQL Server 7.0 and MS Access.
## Platform
MS Access, SQL Server 7.0