combining tables with duplicate records

[Paige]

I have a database that has the following fields:

Doctor UPIN License
Smith A55555 12345

All of the records have the Doctor, Some of the records
have the UPIN, some have the license, some have both. I
have been given some text files with records that have
those fields that may fill in my blank fields. How can I
combine the files together and not have duplicates? I
want to make sure I keep the record that has values in
all three files.

Example: I have Doctor Jones, UPIN T33333 but no
license. My text file has Doctor Jones, the UPIN and the
License. How do I add that to the table and delete the
other record?

Any help is appreciated. I feel like it's something
simple that I'm just missing on the query...

Thanks!
Paige Clements

 

[Michel Walsh]

Hi,


SELECT Doctor, Max(UPIN), Max(License)
FROM myTable
GROUP BY Doctor
HAVING Max(upin)=MIN(upin) and MAX(License)=MIN(license)




I added the having clause to be safe. You may get "troublesome" doctor
(those with more than one not null UPIN or more that one not null license)
with:


SELECT Doctor, Max(UPIN), Min(UPIN), Max(License), MIN(License)
FROM myTable
GROUP BY Doctor
HAVING Max(upin) <> MIN(upin) or MAX(License) <> MIN(license)




Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

Do you want to import the data and permanently update the records in the table?

If so, I would link to the text file and then try doing two update queries as follows.

UPDATE MainFile as M Inner Join TextFile as T
ON M.Doctor = T.Doctor AND M.License = T.License
SET M.UPIN = T.UPIN
WHERE M.UPIN Is Null AND T.UPIN Is Not Null

UPDATE MainFile as M Inner Join TextFile as T
ON M.Doctor = T.Doctor AND M.License = T.License
SET M.License = T.License
WHERE M.License Is Null AND T.License Is Not Null

If you have records in the TextFile that aren't in the Mainfile then you could
run an append query to get the records in the textfile that don't have a match
in the Mainfile and append only those.