Somebody Please Help

[JS]

I have a main table tblOHInst. Fields Match and Zip are blank.

I have a secondary table tblInstNew that has the same fields as the
tblOHInst but the Match and Zip fields has data.

What I need to do is update the OHInst table with information from the
InstNew table that has missing information in the fields Match and Zip that
only applies to the state OH.

Main Table - tblOHInst - (Has records for many states)
Match City State Zip
xxxxx PA
xxxxx OH
xxxxx OH

Secondary Table - tblInstNew (Only has records for the state OH)
Match City State Zip
xxxxxx xxxxx OH xxxx
xxxxxx xxxxx OH xxxx


I hope this makes sense and someone can help. I tried appending but that
doesn't work. I do not know visual basic. I was trying to do it through a
query without a sql statement.

JS

 

[John Spencer]

STANDARD Advice. BACK up your data first. There is no undo

SQL for this might look like:

UPDATE TblOhInst INNER JOIN tblInstNew
ON TblOhInst.City =tblInstNew.City
AND TblOhInst .State = tblInstNew.State
Set TblOhInst.Match = [tblInstNew].[Match],
TblOhInst.Zip = [tblInstNew].[Zip]
WHERE TblOhInst.Match is Null or TblOhInst.Zip is Null

IN the query grid
-- add both tables
-- drag from city to city to set up the join
-- drag from zip to zip to set up the join
-- add the fields Match and Zip to the grid from tblOhInst
-- Select UPDATE from Query: Update menu
-- In Update to "cells" enter [tblInstNew].[Match] and [tblInstNew].[Zip]
under the appropriate field.
-- Set your criteria to Is Null under Match and then go down one line and
set Is null for Zip
-- Run the query

STANDARD Advice. BACK up your data first. There is no undo

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[JS]

Sorry John, not working.

When I try to do the query method, when I view in datashee, no data is
there. When I try the SQL query, message states the Update is missing
syntax. You would think this would be a simple thing to do.

Getting frustrated.

STANDARD Advice. BACK up your data first. There is no undo

SQL for this might look like:

UPDATE TblOhInst INNER JOIN tblInstNew
ON TblOhInst.City =tblInstNew.City
AND TblOhInst .State = tblInstNew.State
Set TblOhInst.Match = [tblInstNew].[Match],
TblOhInst.Zip = [tblInstNew].[Zip]
WHERE TblOhInst.Match is Null or TblOhInst.Zip is Null

IN the query grid
-- add both tables
-- drag from city to city to set up the join
-- drag from zip to zip to set up the join
-- add the fields Match and Zip to the grid from tblOhInst
-- Select UPDATE from Query: Update menu
-- In Update to "cells" enter [tblInstNew].[Match] and [tblInstNew].[Zip]
under the appropriate field.
-- Set your criteria to Is Null under Match and then go down one line and
set Is null for Zip
-- Run the query

STANDARD Advice. BACK up your data first. There is no undo

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a main table tblOHInst. Fields Match and Zip are blank.

I have a secondary table tblInstNew that has the same fields as the
tblOHInst but the Match and Zip fields has data.

What I need to do is update the OHInst table with information from the
InstNew table that has missing information in the fields Match and Zip
that
only applies to the state OH.

Main Table - tblOHInst - (Has records for many states)
Match City State Zip
xxxxx PA
xxxxx OH
xxxxx OH

Secondary Table - tblInstNew (Only has records for the state OH)
Match City State Zip
xxxxxx xxxxx OH xxxx
xxxxxx xxxxx OH xxxx


I hope this makes sense and someone can help. I tried appending but that
doesn't work. I do not know visual basic. I was trying to do it through
a
query without a sql statement.

JS

 

[JS]

Working now! In query design view, I did step by step of your instructions.
However I was being thrown when I was trying to view the records that were
going to be updated. I decided to go ahead and update. It said the correct
number of records to append and it worked?

I also forgot when you append records, you can't see in the datasheet view.
It just came to me.

I thank you so much for your help! Struggling all morning and would not of
been able to accomplish without your generosity and support.

JS

Sorry John, not working.

When I try to do the query method, when I view in datashee, no data is
there. When I try the SQL query, message states the Update is missing
syntax. You would think this would be a simple thing to do.

Getting frustrated.

STANDARD Advice. BACK up your data first. There is no undo

SQL for this might look like:

UPDATE TblOhInst INNER JOIN tblInstNew
ON TblOhInst.City =tblInstNew.City
AND TblOhInst .State = tblInstNew.State
Set TblOhInst.Match = [tblInstNew].[Match],
TblOhInst.Zip = [tblInstNew].[Zip]
WHERE TblOhInst.Match is Null or TblOhInst.Zip is Null

IN the query grid
-- add both tables
-- drag from city to city to set up the join
-- drag from zip to zip to set up the join
-- add the fields Match and Zip to the grid from tblOhInst
-- Select UPDATE from Query: Update menu
-- In Update to "cells" enter [tblInstNew].[Match] and [tblInstNew].[Zip]
under the appropriate field.
-- Set your criteria to Is Null under Match and then go down one line and
set Is null for Zip
-- Run the query

STANDARD Advice. BACK up your data first. There is no undo

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a main table tblOHInst. Fields Match and Zip are blank.

I have a secondary table tblInstNew that has the same fields as the
tblOHInst but the Match and Zip fields has data.

What I need to do is update the OHInst table with information from the
InstNew table that has missing information in the fields Match and Zip
that
only applies to the state OH.

Main Table - tblOHInst - (Has records for many states)
Match City State Zip
xxxxx PA
xxxxx OH
xxxxx OH

Secondary Table - tblInstNew (Only has records for the state OH)
Match City State Zip
xxxxxx xxxxx OH xxxx
xxxxxx xxxxx OH xxxx


I hope this makes sense and someone can help. I tried appending but that
doesn't work. I do not know visual basic. I was trying to do it through
a
query without a sql statement.

JS