How to display data from a relation in a List Box?

[zivotopisac]

Hi, I wonder if anyone could help me with this.

I made a database with 3 tables:
"city"
"company"
"person"

Each company has a director (I choose from a list that's bound to "person" by its ID field)

Each person lives in a city (I choose from a list that's bound to "city" by its ID field)

So:

- a person's city is stored in "person" as the ID of "city", so I only have numbers (1, 2....)
- there can be two persons in "person" with the same name (the only thing that differs them is the city they live in)

The problem is:

When I try to choose a director in "company" from a list, it shows me the name, surname and the ID of the city the person lives in, all from the table "person". The trouble is, since there can be two persons/directors with the same name, I really need to see their cities (the real city name, not just its ID). But I still want the city in "person" to be stored as ID of "city".

I hope this makes sense, I would appreciate your advice very much. I attached my database so you can see what I'm writing about.



practice.accdb

[helloworld]

Let us say we had three tables:

tblCompany
CompanyCode - Primary Key
CompanyName
Director - Foreign Key (SSN)

tblPersonnel
SSN - Primary Key
PersonName
City - Foreign Key (CityID)

tblCity
CityID - Foreign Key
CityName

You would use the following SQL for your listbox:

SELECT tblPerson.SSN, tblPerson.PersonName, tblCity.CityName AS City
FROM tblPerson INNER JOIN tblCity ON tblPerson.City=tblCity.CityID
ORDER BY tblPerson.PersonName;

[zivotopisac]

Thank you so much!