Simplify patient data records

eyeman303,
You might consider using the Richtext memo field to store the complaints, etc.. That way it's all in one field and you can highlight/colorize important items in the richtext field.

Your application is very similar to a CRM (customer relationship management) with which I am pretty familiar.

First, the master key is the patient ID, without it, you have only a confusion in relationships. Then this patient ID is linked to its personal medical records and after that the visit(s) are linked to the patient ID and all required information.

I am pretty sure there was a patient table, this one is going to be your master source. You have multiple unique patient coming to the office maybe multiple times. The patient is recorded once at first visit and then information is added to its "file", whenever he/she comes back or whoever during the visit handles the patient, so why are there name, middle name and first name in OPD_visit_record?

Regarding the summary form, I would rather use whether a grid or a report to display everything at once as this multitude of tiny tiny boxes is confusing at most, then there would be an option with other forms should you need to add more information. For the amount of information required, I wold design it as a wizard like an install wizard ( showing information, next, next, next...) and maybe I would add a dockable form to be able to return super quickly to wherever I need.

Creating reports is feasible, yet challenging, people here could help you with that.

OK, so you have two main keys, the PID and OPD_ID, it becomes clearer how you want your app to work.

You should keep your OPD_ID so you know when and what happened at a point in time which is good, it is a main feature of your app. You should display the name of your patient by using foreign keys taping into patient table to avoid duplicates, do not forget the patient_ID as many people can share the same name.

Yes, you can create a grid with data from multiple tables, this is the purpose of a database, you just need to have the proper foreign key(s) to link the data Just switch tables in the dropdown during creation/edit of the grid or use sql queries.

Hi Eyeman and TC,
Rather than using a script to show the patient's name (first, middle and last), why don't you concatenate those fields in the drop-down list itself (see screenshot1 in attachment epl2);  this has a number of benefits.
1.  no script
2.  you can search the drop down list not only by PID, but also by any part of the name so picking the correct patient in the first place is much easier.
3.  the patient and all the details selected in the drop down list stays visible.
Please see attached (epl2) a simplified example of what you might do (you can also search within the drop down list by date of birth and the number of outpatient visits made).
I also attach an alternative version (epl1) which is a much more traditional approach (starting with the patient, then drilling down into the outpatient visits record);  doing it this way, you are forced to maintain the link between the patient and the outpatient visit which I believe is a much better approach (and much simpler), but maybe there is a business reason why you need to 'de-link' patient and outpatient visit.
Derek.

Derek, you're the boss!