CODEWITHSUNDEEP
ms-accessvbarelationship
moritzgrede
moritzgrede

Reputation: 3

MS Access - Link two fields in one table

In Advance: I have two tables, Einnahmen and Personen. Each person in Personen has a name and an abbreviation. These two fields are also present and linked in Einnahmen. Here you can see the relationship of the two tables.

relationship

Onto my question: Is it possible to have the corresponding value appear if I enter just one. So for example if I enter the abbreviation, I want the corresponding name to be entered and if I enter the name I want the corresponding abbreviation to appear.

Why? The abbreviations solely exists to make form inputs easier and faster. This is especially used in this subform. As you can see both the name and abbreviation are visible and if I choose either of them the other one should update accordingly.

If you want to have a look at the tables:

Table Einnahmen:

Einnahmen

Table Personen:

Personen

Upvotes: 0

Views: 3736

Answers (1)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Your database schema should look like this, where the primary key PersonID could by an AutoNumber field:

Database SChema

In your form, you can use 2 comboboxes, both bound to the foreign key field PersonID but with different lookup queries:

Design View

SELECT Personen.PersonID, Personen.Person FROM Personen ORDER BY Personen.Person;
SELECT Personen.PersonID, Personen.Kürzel FROM Personen ORDER BY Personen.Kürzel;

If one of the 2 comboboxes is used to select a Person, the other one is updated accordingly:

Immediate Update

Upvotes: 2

Related Questions