I’m having trouble making a form in which I can edit the information of two tables while drawing information from three. I am designing a personnel database with information on the different projects people are involved with. The problem arises when I want to design a form which displays and allows editing all the relevant info about a given project. Simply put: In a subform, how do I replace PersonID field from one table with PersonName field from another table? I think the fix shouldn’t be that hard but I just can’t seem to get my head around it.
The tables:
- tbl-PersonnelInfo (fields PersonID, Name, Address, etc.)
- tbl-Projects (fields ProjectID, Description, StartDate, etc.)
- tbl-PersonnelProjects (fields PersonnelProjectsID, PersonID, ProjectID, JobDescription, etc.)
Relationships between those are logically PersonID-PersonID and ProjectID-ProjectID.
HSQLDB database, LibreOffice Base 7.2.5.2 (x64), working in Windows 10 pro.
What I have tried so far:
- I created a main form drawing data from tbl-Projects, with a subform containing tbl-PersonnelProjects data (shown in a table control field for convenience). This basically works just fine, but since the tbl-PersonnelProjects table includes only the numerical PersonID it is not sensible for usage in this case. I want to add/edit/remove people in the project one by one by name. Preferrably from a drop-down list.
- I created a query picking data from both tbl-PersonnelProjects and tbl-PersonnelInfo which remedies the above problem, and show that in the subform instead of tbl-PersonnelProjects. However, in this case I can not edit that info. I suspect it’s because some query feature.
Any suggestions? I’ll take a specific fix to my problem, or a work-around by altering the table structure. Thanks in advance!