Combine/Update two tables

[javee]

I'm not very familiar with access in regards to SQL...I have two tables
with physician info. The first table (OCphysicians) I've kept and
updated as we receive new physician info and have had it a few years
now. The second table (MedBoardData) I recently received from the state
containing all current and updated physician info.

I want to combine the two and have the MedBoardData overwrite older
addresses and phone numbers in OCPhysicians, but don't want to lose
other field information in the older table such as specialty, email and
preferred. The following fields are in the both tables:

LIC_NUM LASTUPDATE MISC EMAIL EMAIL2 LNAME FNAME PREFERRED
SPECIALTY ADDRESS CITY COUNTY STATE ZIPCODE1 PHONENUM1 FAXNUM1


MedBoardData does not have any information under specialty, email, fax
#, and preferred which I want to keep from OCphysicians. I want the
query to update ONLY addresses and phone numbers on records before date
20050930 (date under LASTUPDATE) yet keep email, specialty data, and so
on.

How can I go about doin this? I've been stuck on it a while now.

 

[JethroUK©]

if you only want 2 fields overwriting - why not:

create a query which includes both tables
include all fields you want from old table & just address, email from new
table
tie two unique id fields together (zip codes should be unique to each
record)
untick old address &, email (or any old fields you dont want)
run a make table - result of which should be a third table including the
best of both

tip:

use direct tie if both tables have exactly the same records
use inner tie if old table has more than the new (to produce any additional
old records)
use outer tie if new table has more than the old (to produce any additional
new records)

whenever you are experimenting - never use the original tables - use a
copy - just in case )

 

[JethroUK©]

p.s. scrap that idea - if the addresses have change - then so will their zip
code - sorry

 

[John Spencer]

Assumption:
Lic_Num is unique and is in both tables.
-- Add both tables to the query grid
-- link Lic_Num to Lic_Num
-- put the following fields from OCPhysicians into the grid LastUpdate,
Address, City, County, State, ZipCode1, PhoneNum1, and FaxNum1
-- Select Query: Update Query from the Menu

Under LastUpdate set criteria to < #2005-09-30#

-- Under each field you will have to type the tablename and field name in
the update to
Sample - Update To under LastUpDate
[MedBoardData].[LastUpdate]
under Address
[MedBoardData].[Address]

IF you want to be extra careful and not replace something that exists in
OcPhysicians with blanks from MedBoardData then you can enter something a
bit more complex.
IIF(Len(Trim( [MedBoardData].[Address] & ""))>0,
[MedBoardData].[Address],[OCPhysicians].[Address] )
That will only update the [OCPhysicians].[Address] if there is something in
[MedBoardData].[Address]

 

[javee]

Thanks JethroUK© for taking the time to assist me with the above
problem.

John, Thanks for responding to my topic and giving me great detail to
accomplish my task. MANY GRACIAS! It worked great. I did use the more
complex IIF criteria for my query.

I do have a question...the MedBoardData(8611) database does have more
records than my OCphysician(7759) database. How can i go about adding
the additional records from MedBoardData to OCphysicians, and deleting
records from OCphysicians that are not in MedboardData??

 

[John Spencer]

Two queries should do that.
FIRST: MAKE A BACK UP COPY of your database.

DELETE DISTINCTROW OCPhysicians.Lic_Num
FROM OCPhysicians
WHERE Lic_Num,NOT IN
(SELECT Lic_Num
FROM MedBoardData)

Open a new query.
Close the dialog asking which tables
Switch to SQL view
Paste in the above
Select Query: Run Query from the menu

To APPEND the other records
Open a new query
Select MedBoardData and OCPhysicians as the tables
Add all the fields you want to copy to from MedBoardData and put them into
the grid
Join Medboarddata and OcPhysicians on the Lic_Num
Double-Click on the join line and choose All records from MedBoardData and
only ...OCPhysicians
Select Query: Append Query from the menu
In the Append dialog, select OcPhysicians (again)
Set the Append to "cells" to the appropriate field names
Add OCPhysicians.Lic_Num to the grid; make append to blank; set its criteria
to Is Null
Select Query: Run Query from the menu





Thanks JethroUK© for taking the time to assist me with the above
problem.

John, Thanks for responding to my topic and giving me great detail to
accomplish my task. MANY GRACIAS! It worked great. I did use the more
complex IIF criteria for my query.

I do have a question...the MedBoardData(8611) database does have more
records than my OCphysician(7759) database. How can i go about adding
the additional records from MedBoardData to OCphysicians, and deleting
records from OCphysicians that are not in MedboardData??

 

[javee]

The append query worked like a charm. However the DELETE DINSTINCTROW
did not. An error messaged appeared with "Invalid Syntax (missing
operator) in query expression, and then points to OCPhysicians.LicNum.
Is there a way to accomplish it in the design query grid?

 

[John Spencer]

Looks like I had an error in the SQL statement.

 

[John Spencer]

Looks like I had an extraneous comma in there.

DELETE DISTINCTROW OCPhysicians.Lic_Num
FROM OCPhysicians
WHERE Lic_Num NOT IN
(SELECT Lic_Num
FROM MedBoardData)

Try using the unmatched query wizard to build a query to identify the
records you want to delete. Then change that to a delete query by selecting
Query: Delete Query from the menu.


The append query worked like a charm. However the DELETE DINSTINCTROW
did not. An error messaged appeared with "Invalid Syntax (missing
operator) in query expression, and then points to OCPhysicians.LicNum.
Is there a way to accomplish it in the design query grid?

 

[javee]

Just wanted to thank you John for taking the time to assist me with the
tables. I really really appreciate it. It looks and works perfect.
You're an awesome guy!

 

[John Spencer]

You are welcome.