Duplicate Data being produced in an append query

[CrakD]

Hi, I'm co-oping in a pediatric rehab center. I am workin on an intranet page/database system. At this time I have 2 Db's, one ("referral_database&quotwhich get's it's info from a frontpage 2002 created db. The other db ("patient_db&quot which contains similar fields, like last_name, first_name, doctor etc. In most cases the patients will not be seen for weeks/months after they have been referred. That's when they have to be entered into the "patient_db". I'd like to take a lot of the footwork out and get most of the information from the "referral_db". I've tried importing the table "results" from the "referral_db", and then created a new table("evals&quot in "patient_db" so that I could add a new field ("evaled&quot in order to discern between patients that are still waiting to be seen and those who are going to be evaluated that day. It's a yes/no. There I can append new records to "patient_db", and it works great. But, when I try to update the referrals from the "referral_db" to the "evals" db then it will give me all of the records again. Effectively doubling, and of course adding the new referrals. Which I don't want. I'd like to be able to have the patients already "evaled" to not be duplicated. And not overwritten because then the end user can't be expected to remember which patients where evaled and which ones weren't. I could use some help, i've been stuck for only 2 days but still frustrating.

chris

 

[tracy1234]

Chris -

Can you outline your structure a little bit more? How does the data flow within the structure?

From what I understand, your duplicates are being caused by not testing to see if a patient has been formerly imported into your referral database.

Have you tried using a patient id as your primary key in your tables? This will help to ensure that you don't have duplicates.

-Tracy

 

[tracy1234]

I'm sorry, what I meant to say is use a patient_id to test to see if you've previously moved records from your referral.mdb tables to your patients.mdb tables.

-Tracy

 

[CrakD]

I have added a "referral_ID" I can't believe I made that mistake. K, let me see if this can be explained better.
nurse calls -> receptionist enters info in intranet page -> data is submitted to "referral.mdb". Then the day the patient is scheduled to come in and patient actually does come in -> Receptionist enters information into the "patients.mdb" (the active patient database). What I'd like to do, is instead of filling out the information from the paperwork being filled out by the parent, i'd like to be able to just click on a button that will send the info that matches, from the "referral.mdb" to the "patient.mdb". Fields like last name, first name, telephone, dr name, ssn, medicaid...etc. It would save time and i'm all about saving time. I need to be able to not create duplicate data, and be able to distinguish the patients coming in and the patients that are still pending their appointment. I hope you understand this better. Thanks
Chris

 

[tracy1234]

Chris - Have you considered just using a checkbox in the referrals table? The receptionist could query the referrals table for the patient name, then place a check in a box. You could append the checked record to your patients table and clear the check.

-Tracy

 

[CrakD]

I guess I had a brain fart. Thanks, I was making it much more difficult than it had to be. I appreciate your help. It's running smoothly thanks.

Chris