I already have a Visit ID being created by an auto-entered serial number, validated always, field never empty, always unique. If PatientID is auto –entered, I will have difficulties in my main layout. Eg- I may want to type 121 as an ID several times to enter a new visit record and to have certain fields fill automatically.
PatientID should be defined as an auto-entered serial number ONLY in the patients table. In the Visits table, it should be a number field with no auto-entered serial number setting. There are several options that can be used to link a new Visits record to an existing Patients record. All, in one way or another enter a matching ID number in the Visits::PatientID field to link it to the correct Patients record. The simplest to implement--though often not the best option once you have a large list of values, is a drop down list or popup menu of names and ID numbers with the PatientID specified as the first field of the value list.
The Patients table (which exists but which I do not use)
Actually, you DO use the Patients table, though you may not be using the Patients layout.
I assume this is because of the multiple records associated with one Patient ID in the main layout-
This is probably not the case.
The trick is to set up your database so that you can easily determine whether the new Visit record is for a new or existing patient. When you wanted to type in the PatientID, was that a value from another system? or a Value you arbitrarily assigned each new patient?
Actually I have an excel list with serial numbers and patient names. Unfortunately I cannot enter names in the database (privacy protocol). So I use that serial number as a PatientID. Lets say ID 1 for Patient A, ID 2 for Patient B etc. this list increases in size everyday. these patients are entered in my database. as the patients are entered, we also record in an excel sheet that Patient A's particular visit has been entered.
So when I have a new visit from a patient, I know the name of the patient and the serial number from the Excel file. If I type in 1, some data like Sex, Race will appear and if I type in 3...no data will appear. so I have to fill in sex, race etc.
On your advice, I considered having an automated Patient ID entered. This would mean that we record the automated ID next to the patient name in the Excel sheet. Do you think I can avoid creating an automated ID altogether and continue to use the serial number as was being done? I mean, know that you know the full background? Can you predict any problems I may face?
You can add a second field for this externally produced ID and use it to determine if a visit is from a patient that is already in your database or not. This allows you to stick with auto-entered serial numbers as your patient ID number and yet use the number from your spread sheet to either find an existing record in Patients or start a new record in Patients with that value.