Update Queries

[Marc]

I have two tables an Employee table and a Gap Table. Both Tables have the
Social Security Field (SSN) in them. I also have a UPI field (unique personal
ID) in both tables. In the Gap table the UPI's are null. What I need to know
how to do is How do I update the UPI in the GAP table with the UPI in the
Employee table when the SSN fields from both tables equal each other. Please
Help! Thank you!

 

[Jeff Boyce]

Marc

If you know the SSN, doesn't that mean you (already) know the UPI? If so,
why store it twice?

By the way, SSNs are not particularly good choices for a Primary Key. They
aren't guaranteed unique and not everyone has one... (both of these are
basic requirements for a Primary Key).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

UPDATE Gap INNER JOIN Employee
ON GAP.SSN = Employee.SSN
SET GAP.UPI = [Employee].[UPI]
WHERE Gap.UPI is Null

In query design view
== add both tables
== drag from SSN to SSN
== If there is a join between UPI delete it
== Add Gap.Upi to field list
== Set criteria to Is Null
== Select Query: UPdate from the menu
== TYPE the following into the Update to using your employee table name.
[Employee].[UPI]
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

You don't. Any time that you need the UPI information, you join the tables by
SSN. Storing the same data in two different tables is usually a bad idea
unless that data is the Primary Key of Foreign Key. From your description,
the SSAN does that.

 

[Marc]

We don't use SSN's anymore. We are not allowed to use SSN's anymore.
Everything is going by UPI. This is why I need to update the UPI.

 

[Jerry Whittle]

Now that makes sense! As Jeff Boyce replied, SSANs aren't a very good primary
key.

John Spencer's solution should work.

 

[Marc]

THANK YOU!!

UPDATE Gap INNER JOIN Employee
ON GAP.SSN = Employee.SSN
SET GAP.UPI = [Employee].[UPI]
WHERE Gap.UPI is Null

In query design view
== add both tables
== drag from SSN to SSN
== If there is a join between UPI delete it
== Add Gap.Upi to field list
== Set criteria to Is Null
== Select Query: UPdate from the menu
== TYPE the following into the Update to using your employee table name.
[Employee].[UPI]
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have two tables an Employee table and a Gap Table. Both Tables have the
Social Security Field (SSN) in them. I also have a UPI field (unique personal
ID) in both tables. In the Gap table the UPI's are null. What I need to know
how to do is How do I update the UPI in the GAP table with the UPI in the
Employee table when the SSN fields from both tables equal each other. Please
Help! Thank you!

.