Populating field based on comparison of two lists

  • Thread starter Thread starter cyberbenali
  • Start date Start date
C

cyberbenali

I am using Access 2003. I have four fields (genus, species, subspecies,
variety) that I need to compare between two lists (speciesCodes and
herbariumList). If all of the fields match, then I want the field
speciesCodes.symbol to populate the herbariumList.symbol field of the
matching records.

Is this possible? I know that when entries are being entered, I can do an
afterUpdate command in VBA to compare but I am baffled as to how to do it for
data that is already entered into the database.
 
You say two "Lists". Do you mean you have two tables? Assuming that is the
case the easiest methoud would be an UPDATE QUery

UPDATE Herbarium as H INNER JOIN SpeciesCodes as S
ON H.Genus = S.Genus
AND H.Species = S.Species
AND H.SubSpecies = S.SubSpecies
AND H.Variety = S.Variety
SET H.Symbol = .[Symbol]

Optionally you can add a WHERE clause at the end

<<< only update the fields that are null >>>
WHERE H.Symbol is Null or H.Symbol = ""

or <<< only update the fields that are null or differ between the two tables>>>
WHERE H.Symbol is Null or H.Symbol <> S.Symbol


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I may not have explained myself properly the first time. Let's see if this
helps.

Yes, I did mean tables (I am a ecologist, I am new to the terminology). I
tried the INNER JOIN suggested on the second post as is. It does not update
any records. Each record has Genus, Species, subspecies and variety as
fields. These fields are atomized because later on we will need to be able to
select records based on certain criteria based on any combination of these
fields. There are 3499 accepted combinations of these four fields with an
associated 5th field called S.symbol. Right now, H.symbol field is blank but
the H.genus, H.species, H.subspecies, H.variety are populated with data. I
want to populate H.symbol based on a query that find the record in which
H.genus=S.genus AND H.species=S.species AND H.subspecies=S.subspecies AND
H.variety=S.subspecies.

The UPDATE query makes sense. I am just learning to bring it all together.

Thanks for the patience!
 
Did you run the query or did you just switch to the datasheet view? If you
did the latter, then all you will see is what WILL be updated if you actually
run (execute) the query.

Try using Query: Run from the menu.

If you still don't update any records then change the query to a select query
(Query: Select from the menu) and add in additional fields to see what is
returned. If no records are returned, then the fields used in the join are
not finding any matches. If that is the case, then you need to closely
examine the data to determine what is different between records in the tables.

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