Help on patient/physician case management file

[manito2000]

Hello,

I need some help.

I have the following tables:

1. Patient demographic info (Patient ID, First Name, Last Name, Address, date of birth, etc...)
2. Physician demographic info (Physician ID, Name, Last Name, Specialty, Phone, Fax

I also have another table with patients that are being case managed. I have those ID#'s. However, I want to be able to auto-populate a form with the patient's demographic info by only typing the patient ID. Also, there are cases when the patient ID will not be in the patient demographic info table. Therefore, I will need to enter all their demographic info manually.

Same with the physician. I want to be able to select the physician and auto-populate the demographic info. However, there are occassions where the physician will not be in the main file...therefore, I'd like to add that info manually.

Please help.

 

[RuralGuy]

Create the form and use ComboBoxes to select both the patients and the physicians. Use the NotInList event of the ComboBoxes to launch a "New patient/physician" form. Your table should contain *only* the patient or physician's ID Number as ForeignKeys and no other data. Joining this table with both of the other two tables will allow you to display any field from the other two tables automatically once the selection is complete.

 

[manito2000]

Thanks...however, I am having a huge dilemma. I've got 700 patients (that are going to be case managed)...I also have another table with patient demographic info (90,000 records).

I've already done a quick match in excel to see how many of the 700 patients are in the (90,000 table). However, only 660 of the case managed patients are in the 90,000 record patient demographic table.

What the based way for me to load the 660 patients...then load the remaining 40 patients that do not have a match in the 90,000 records.

 

[manito2000]

So basically two tables...

One of the tables only has patient id for case managed patients (I need to extract their demographic info) from the 90,000 record file for at least 660 of the patients. The other 40 I need to enter their demographic info seperately because they are not in the 90,000 record file.

 

[RuralGuy]

What are the FieldTypes of the patient and physician ID numbers? Since they would be ForeignKeys in your table then appending the patient/physician ID numbers into each record would not create an error. Can you create such a list from Excel?

 

[RuralGuy]

So basically two tables...

One of the tables only has patient id for case managed patients (I need to extract their demographic info) from the 90,000 record file for at least 660 of the patients. The other 40 I need to enter their demographic info seperately because they are not in the 90,000 record file.

If all of the demographics for each of the patients already exists in the 90,000 record file then have that file as it is in your db. All you need in the next table is their ID Number.

 

[RuralGuy]

I believe you will need three tables. Patients, Physicians and then Managed cases.

 

[manito2000]

Not all the demographics exist. There is only demographic info for 660 patients.

 

[RuralGuy]

OK, then have that as the patients table and create a form to add a new patient which you can call automatically the NotInList event of the ComboBox that fills in your cases.

 

[manito2000]

Ok...

However, how can I append the demographic info to the first 660 patients...

This is what I've done...

1st table has patient ID# (700 case managed patients)
2nd table has all patients (90,000, however, missing 40 patients from my 700 case managed patient file)

I do a query to show all records where patient id# in table 1 (case manged patients) is equal to patient id# in 2nd table (90,000 records) + to show the demographic info fields.

This works...but I loose the 40 records of case managed patients that are not in the 90,000 record file. Those I need to add manually, but like I said I loose im with that query... Any thoughts? I'm so frustrated right now

 

[RuralGuy]

If I understand you correctly, you have not "lost" the 40 additional patients, it is just that there are 40 PatientID numbers in the 1st table that do not seem to exist in the 2nd table. Would that be an accurate statement? Is it OK that those PatientID numbers are not in the 2nd table yet or is this an error?

 

[manito2000]

Correct...I have those ID # in the case managed patients file. However, not in the new file from the query.

Those 40 patients will never show up in the patient demographic file. They are "extras" that simply need to be case managed but will never show up in the patient demographic file...does that make sense?

Thanks for all your help

 

[RuralGuy]

Now it sounds like when you do create the demographic data for the 40 extra patients, you do not want that to be done in the Demographic table. It that right? Are you planning to put these 40 patients in yet another table?

 

[manito2000]

Nope...I just need them to show up in the managed patients table.

 

[RuralGuy]

Does the current Patients table belong to someone else and you are not allowed to update it?

 

[manito2000]

I'm not allowed to update it

 

[RuralGuy]

OK, now things are starting to make a little sense. I'll think on this for a few minutes.

 

[RuralGuy]

How did the 40 get their PatientID numbers and what is the format of the number? Is it text or numeric? Is the ID number in the same format as the 90,000 table? You are probably going to want some other fields in your ManagedCase table other than demographics correct? Notes, Actions Needed, Actions Taken... etc.