Prev: IIF Statement Query??
Next: Find matching records based on two fields
From: Jerry Whittle on 22 Mar 2010 15:29
Now that makes sense! As Jeff Boyce replied, SSANs aren't a very good primary
key.

John Spencer's solution should work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Marc" wrote:

> 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" wrote:
>
> > 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.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> > "Marc" wrote:
> >
> > > 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!
From: Marc on 24 Mar 2010 17:11
THANK YOU!!

"John Spencer" wrote:

> 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
>
> Marc wrote:
> > 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!
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: IIF Statement Query??
Next: Find matching records based on two fields