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 [url removed, login to view] from inventory a, additionalinfo b where a.id=[url removed, login to view] and min([url removed, login to view])>'1975' and max([url removed, login to view])<'1975' and b.make=10 and a.whatpart=1 group by [url removed, login to view]
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.
The SQL Statement to find matches for the span of years. Here are a few examples
1. 66-67 for the search
64-65 and 69-70
in this case, nothing would show up because none of the years fit.
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.
MS Access, SQL Server 7.0