Hello, I am currently at a dead end and I am not sure where I went wrong. I have a form and I am being asked to make the attorney name populate their respective firms. I have a table of the attorney names and their corresponding firm ids along with a table with just the firm names. I have a combo box set up for the attorney names in the form but I cant figure out the rest. Any help would be appreciated.
Many to One Population (1 Viewer)
- Thread starter cambarn80
- Start date
mike60smart
Registered User.
- Local time
- Today, 09:14
- Joined
- Aug 6, 2017
- Messages
- 1,905
Can you upload a copy of your database?
- Local time
- Today, 03:14
- Joined
- Feb 28, 2001
- Messages
- 27,186
What you need to know in this situation:
You have a PARENT form (the list of firms) and a CHILD form (the list of attorneys who work for those firms). A formal relationship can be built if you have a firm ID that acts as the primary key of the list of firms, and you need a corresponding field for the firm ID in the list of attorneys. It is not good practice to use the same names in two different fields when forming a relationship, so the firm list might have a field called FirmID and the attorneys would have a field called FirmFK (FK = foreign key). A foreign key is NOT a primary key though it COULD (if you wished) have a non-unique index. You cannot build a formal relationship if the parent field isn't a unique key (which a primary key IS).
If you wanted to see everything aligned once you fill in the appropriate FirmID values to the FirmFK field, you MIGHT create a query looking something like this:
Obviously it would take more than this to fill in all the info you might want, but this gives you a basic idea of how such a thing would work. The ... INNER JOIN ... ON sequence shows the columns, one from each table, that are related to each other.
You have a PARENT form (the list of firms) and a CHILD form (the list of attorneys who work for those firms). A formal relationship can be built if you have a firm ID that acts as the primary key of the list of firms, and you need a corresponding field for the firm ID in the list of attorneys. It is not good practice to use the same names in two different fields when forming a relationship, so the firm list might have a field called FirmID and the attorneys would have a field called FirmFK (FK = foreign key). A foreign key is NOT a primary key though it COULD (if you wished) have a non-unique index. You cannot build a formal relationship if the parent field isn't a unique key (which a primary key IS).
If you wanted to see everything aligned once you fill in the appropriate FirmID values to the FirmFK field, you MIGHT create a query looking something like this:
Code:
SELECT F.FirmName, A.AttorneyName FROM Firm AS F INNER JOIN Attorney AS A ON F.FirmID = A.FirmFK GROUP BY F.FirmName ORDER BY FirmName, AttorneyName ;Obviously it would take more than this to fill in all the info you might want, but this gives you a basic idea of how such a thing would work. The ... INNER JOIN ... ON sequence shows the columns, one from each table, that are related to each other.
