how do i get 1 table to autofill info from another

[Anne]

I have a table with 2 fields - Code + (relevant) school name. I have Imported
a table from excel with various fields including Code and relevant school
name. However, not all the school names have been filled in but codes are. I
need to somehow tie up the codes from the Codes List and have it fill in the
the missing schools on my imported data table. The codes and schools appear
more than once on the imported table. PLEASE...how on earth do i do this

 

[Maarkr]

if you don't have too many codes, you could run an update query...start a new
query, select query...update. Put the Code and SchoolName field in the query
grid with, ie, Code '001' in the 'Criteria' and the school name in the
'Update To: ' then 'Run' the query.

How many records/different schools do you need to update?

 

[Anne]

Unfortunately I have 898 schools each with their own unique code. My
imported excel table has 12500 records. I need to update the 'missing'
school names where the codes appear.

Many thanks for your response thus far

 

[John Spencer]

If you are just going to update the school table and discard the
imported table, then

UPDATE School INNER JOIN Imported
ON School.Code = Imported.Code
SET School.SchoolName = [Imported].[SchoolName]
WHERE School.SchoolName is Null and
Imported.SchoolName is not Null

To do this using the query design view
-- Add both tables to the query
-- Click on School.Code field and drag to Imported.Code field
-- Add SchoolName from both tables to the fields
-- UNder School.SchoolName enter critera
is Null
-- Under Imported.SchoolName enter criteria
is Not Null
-- Select Query: Update from the menu
-- In the UPDATE TO under School.SchoolName enter
[Imported].[SchoolName]
-- Select Query: Run from the menu

WARNING: BACKUP your data first. If this goes wrong, there is no other
way to restore the data to its original state.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================