Hi

I am trying to make a lookup query, which will compare a field inside the same table with another table which the data is located in.

I attempted to write a SQL query how ever it has been un successfull.


SELECT Occupants.[Unit#] FROM Occupants WHERE Occupants.Residence=Inspection.Residence;


I keep on getting an input query box with "Inspection.Residence" displayed. Residance is a field in Inspection which is the open Table being edited.

I have a table which contains all the Residence information such as the occupant name, Residance location, and the Unit number inside the residance. I then have an inventory table where you select which residence, then you select a unit number inside that specific residence.

I'm not sure if my problem is because the Inspection table is being edited.

Please help!

You actually need a join between the two field in your query.

You actually need a join between the two field in your query.
Do you mean a relationship? I have tried each of the relationship settings and got the same result each time.

I found what i was looking for that should be in the form section though... but its exactly what i was looking for

http://www.blueclaw-db.com/download/dependent_combo_box.htm

Hope it helps some one else.