Re: Combine/Update two tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <javeecom@xxxxxxxxx> wrote in message
news:1148407341.379322.145780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Create query - a question for guru.
    ... LastUpdate, NOT NULL ... a selected plan and for a required date just typing this date into query. ... so that the RequiredDate should be>= than the nearest ...
    (microsoft.public.sqlserver.programming)
  • Re: Combine/Update two tables
    ... Open a new query. ... Add all the fields you want to copy to from MedBoardData and put them into ... Join Medboarddata and OcPhysicians on the Lic_Num ... Append Query from the menu ...
    (microsoft.public.access.queries)
  • Re: Combine/Update two tables
    ... Open a new query. ... Add all the fields you want to copy to from MedBoardData and put them into ... Join Medboarddata and OcPhysicians on the Lic_Num ... Append Query from the menu ...
    (microsoft.public.access.queries)
  • Re: Combine/Update two tables
    ... The append query worked like a charm. ... Add all the fields you want to copy to from MedBoardData and put them into ... Join Medboarddata and OcPhysicians on the Lic_Num ...
    (microsoft.public.access.queries)
  • Re: Combine/Update two tables
    ... Try using the unmatched query wizard to build a query to identify the ... The append query worked like a charm. ... Add all the fields you want to copy to from MedBoardData and put them into ... Join Medboarddata and OcPhysicians on the Lic_Num ...
    (microsoft.public.access.queries)