Update query to populate a column

  • Thread starter Thread starter Byteguy
  • Start date Start date
B

Byteguy

I have a table containing membership data for a professional
organization. I was asked to add data that gives the date of a
professional certification. I added the field to the table which is
blank to start with. I was given a list of names along with the
certification dates.
I now have 2 tables to work with--the original with the blank
"certification_date" and a second one that contains the names and the
dates. I wish to use a query to match the names and insert the
certification dates in the main table.

I used the query design mode--chose the "certification_date" field from
the table containing names and dates and told it to update the
"certification_date" field in the membership table.
When I run the query, it tells me that 41 records will be updated. But
nothing happens. I check the main table after I run it and the
"certification_date" field is still blank.

I must be having a brain fart, 'cause I am obviously missing something
here.

Art
 
Are both fields date type? If one or the other is string, the update won't
work. Use CDate() (Convert to Date) or CStr() (Convert to String) functions
to effect the update.

HTH
 
H'mm, interesting.

Please post your code/SQL statement you're using, so we can figure out where
the problem is.

Thanks,
 
here it is.

UPDATE MemberList INNER JOIN certdates ON (MemberList.[Name Last] =
certdates.[Name Last]) AND (MemberList.[Name First] = certdates.[Name
First]) SET MemberList.[Certification Date] = certdates.[Certification
Date];

note that memberlist is populated with about 460 records and certdates
has a lesser number. At the moment, the certification date field is
blank.

The query runs and says it will update 41 records, but no value is ever
placed in [memberlist].[certification date].

Art
 
Add criteria to screen out null dates in CertDates.[Certification Date]. If
the field is null in both tables, you will get an update, you just won't see
any difference

UPDATE MemberList INNER JOIN certdates
ON (MemberList.[Name Last] =certdates.[Name Last])
AND (MemberList.[Name First] = certdates.[NameFirst])
SET MemberList.[Certification Date] = certdates.[Certification Date]
WHERE CertDates.[Certification Date] is Not Null
OR MemberList.[Certification Date] <> certdates.[Certification Date]
 
There are no null fields in CertDates, so that expression should be
unnecessary. when I pasted in the above SQL code, it wouldn't
run--says it will update "0" rows. Also asks for the parameter
certdates.namefirst. Now why would it ask that?

Art
 
If it asks for a parameter like that, it usually means someone has misspelled
something. I believe I did have NameFirst instead of Name First. Note the
missing space in NameFirst

So the query should have read.

UPDATE MemberList INNER JOIN certdates
ON (MemberList.[Name Last] =certdates.[Name Last])
AND (MemberList.[Name First] = certdates.[Name First])
SET MemberList.[Certification Date] = certdates.[Certification Date]
WHERE CertDates.[Certification Date] is Not Null
OR MemberList.[Certification Date] <> certdates.[Certification Date]

If his comes back and says it is about to update 41 records and updates none,
then I don't have any further ideas. If it comes back and says it will update 0
records then I believe that you need to closely examine the data in your two tables.
 
OK, fixed that. But the response when I run it is "0" rows will be
updated. I know that there are at least 41 rows in the certdates where
there are matched records in MemberList (matching first and last
names).

I seem to recall seeing somewhere that if there is not a current value
in the field being updated, then no update will occur. This seems odd,
as it is actually a field I'm adding to the record, as it wasn't
available when the database was created.

Art
 
Since it says it is going to update zero records, then I would look at the 41
records that match and see what values they contain.

SELECT MemberList.[Name Last],
MemberList.[Name First],
MemberList.[Certification Date] as CurrentInfo
certdates.[Certification Date] as NewInfo
FROM MemberList INNER JOIN certdates
ON (MemberList.[Name Last] =certdates.[Name Last]
AND (MemberList.[Name First] = certdates.[Name First])

IF this returns 41 records and the two Certification dates are different then
you have a problem that I cannot diagnose.

UPDATE MemberList INNER JOIN certdates
ON (MemberList.[Name Last] =certdates.[Name Last])
AND (MemberList.[Name First] = certdates.[Name First])
SET MemberList.[Certification Date] = certdates.[Certification Date]
WHERE CertDates.[Certification Date] is Not Null
 
Further messing with this reveals the following:

The certdates table has 134 names with certification dates in it. For
some reason, the person who passed the information to me left out the
dates for 41 of the names. So, when I run the update query and it
tells me it is going to update 41 records, what it is finding is the 41
records without a date and, of course, placing the null date into the
memberlist which also has a null date. Now that is just plain weird!
I've joined the two tables on first name and last name, so it should
not be looking at the certification dates to decide what info to
update. It should just find the first name and last name and plug in
the certification date in the memberlist, shouldn't it?

Art
 
If you've joined on the first name and last name fields and you are only
getting the records where the certification date is null, I suspect that
source table has something different about the name fields. Perhaps there
are leading spaces (or trailing spaces) or some other character(s) to cause
the non-match.

It definitely appears to be data-related. Try checking the lengths of the
data in the fields on two of the records you expect to match but aren't?
 
OK--I'll do that. Really seemed that way to me, also. FYI, I have had
problems with the data I received from these people. Usually the
culprit is spaces in the wrong place.

Thanks
Art
 
Guessing that the problem is spaces before or after the name fields
(probably after), you can try a non-equi join. Use the TRIM function in the
join criteria to match on the fields.

UPDATE MemberList INNER JOIN certdates
ON (MemberList.[Name Last] =Trim(certdates.[Name Last]))
AND (MemberList.[Name First] = Trim(certdates.[Name First]))
SET MemberList.[Certification Date] = certdates.[Certification Date]
WHERE CertDates.[Certification Date] is Not Null
OR MemberList.[Certification Date] <> certdates.[Certification Date]


To be doubly certain, you could Trim MemberList.[Name Last] and [Name First]
 
Back
Top