I'm looking for a way to search my database by looking in many tables. However, the user may enter multiple words (variables) into the search and each word (variable) may get results from the different tables.

So my question is how do you search multiple variables from 1 column in many tables and then prioritize the results?

An example:

I have a database holding information on Cars.
It has a search box that will allow a user to search for cars. the tables look like this:

Table: Car
Column 1: Car_ID PK
Column 2: Car_Name
Column 3: Man_ID FK
Column 4: Eng_ID FK

Table: Manufacturer
Column 1: Man_ID PK
Column 2: Man_Name

Table: EngineSize
Column 1: Eng_ID PK
Column 2: Eng_Size

The key search result is always going to be for the Car(s).
So if a user types into the search box "Ford"
The results list will display all the names of all the Ford cars.
An important detail in the table: Cars, Column: Car_Name, is that the data is always just the car model name and never the manufacturer- So it would just be 'Focus', 'Puma', 'Ka', etc- not 'Ford Focus'

Ok, so if a user typed in "Ford Focus" the system currently treats FORD and FOCUS as 2 differnt variables to be searched independently of each other.
The search for FORD will return nothing from table CAR, but 1 result from table Manufacturer and nothing from EngineSize. With the result from table Manufacturer it will then retrieve all the values from Column 'Name' in Table Cars WHERE Man_id = Man_id from the matching result in table manufacturer - so in other words it will return the name of every ford car in the data base.

The search will then search for FOCUS and will return 1 result from Car, no result from Manufacturer and no result from EngineSize.
It will return the correct desired result which is the car the 'Ford Focus'.

So here is the problem- how do i get it to prioritize the results and not display every ford car in the data base?

You can see how this becomes more complicated with more tables and more variables.

I would really appreciate any help any one can give me,

Thanks.