Adding records "not in list"

[artanis50]

Hi all, I need some help adding some records that are not found in a list. I'll try to keep this short and to the point -
I have a database that imports several tables of physician data. One table is a list of all active MDs with their service and specialty (Surgery / Orthopedics for example). Each service has their own table, listing each provider and a variety of data elements. In a form, a user selects the service they want to run a profile for - I have a button that imports all the tables and runs queries and the final profile.
The piece I need to add:
If a provider does not have any data in the provider table, they will not show on the final profile. I need to reference the list of MDs in the final profile against the table with ALL MDs and add all MDs not found on both lists. Does this make sense?

Right now, what I'm doing is opening both files and using VLOOKUP from the all provider file and the final profile. When I find the MDs in the service that are not found, I copy and paste them into the profile. I'd like to automate this if I can.

I'm sorry if it's confusing and if I didn't give enough information please let me know. Your help is greatly appreciated!

 

[HiTechCoach]

Since this is in an Access forum I assume you are working with tables in access.

You should be able to use an append query to add the records.

First I would use the access query wizard to create an unmatched query to find the records to app. Then use this query as the base for an append query to add the records.

If the doctors have a unique ID that is index and not allowing dups then you can simple append all the records to the table. Access will automatically discard any duplicates.

Each service has their own table, listing each provider and a variety of data elements.

That is typically how you would handle this in a spreadsheet. In a relational database, like Access, you should keep all the data in a single table. You use queries to filter down to the desired records.

 

[artanis50]

Thanks - I'll give it a shot. And regarding having a separate table for each service, I believe it's needed as the measures are all different. For example, we don't measure the same data for a surgeon as we would a cardiologist.

 

[SydneyGeek]

True, but you are still thinking in columns. Databases work best when you think in rows.

For example, you could have 3 fields: Specialisation, Measure, Value

Building a different profile is then just a case of adding the right measures to each specialisation (1 row per measure).
You could view / edit these in a subform which filtered on the doctor's specialisation. And if you want to break it into columns for reporting, a crosstab query will do that.

Denis

 

[HiTechCoach]

Thanks - I'll give it a shot. And regarding having a separate table for each service, I believe it's needed as the measures are all different. For example, we don't measure the same data for a surgeon as we would a cardiologist.

I totally agree with Denis.

In a well design/propery normalized relational database the measurement data would be in a separate table. With each measurement on a separate record.

In properly designed/normalized database, add a new measurement should not require adding a column to a table. It should require just a new record.

 

[artanis50]

The way the data comes out of the source system does not allow for this to happen easily. Thanks for the suggestions but one table for all physicians won't work for what i need to do.