Creating Queries using relationships
I manage a database of patient files for a private clinic. I needed to create queries so that users could see a list of new records they had not reviewed yet.
The architecture is relatively simple, with a central patient file related by a one-to-many relationship to different kinds of record tables, i.e. medical records, lab tests, radiology, patient visits, etc. I'll call these peripheral record tables, as oppose to the central patient table.
For every peripheral record table, I created a field "UsernameViewed" for every user.
I created a new table titled "UserViewed", and established a one-to-many relationship with the central patient table.
By creating a single record in the "UserViewed" table and linking it to every record in the central patient table, I was then able to create portals for every different peripheral record table, with filtering based on whether the "UsernameViewed" field = to "Yes". Thus, if the field was empty, the record would show in the portal. I added the "UsernameViewed" field in the portal so that users could simply click a checkbox "Yes" after having viewed the record, which would then no longer appear in the portal...
The solution worked beautifully. Every user has their own "UserViewed" table that links to all the records in the central patient table, and thus can see only the records that they have not checked off as "Viewed".
There is one big problem: the information I am displaying in the portals are fields from the original peripheral record along with the patient name field found in the central patient table. For some reason, the associated patient name is not showing up.
In the screenshot, you can see the patient name "Penny" being displayed, but that is not the name of the patient associated with each of those records...
How can I fix this problem?
Thank you in advance for any help! This has been buggin me all day...