Update Queries
in [Queries]
|
From: Marc on 22 Mar 2010 13:44 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: Jeff Boyce on 22 Mar 2010 14:20 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. "Marc" <Marc(a)discussions.microsoft.com> wrote in message news:06EA8BFA-4D27-46AD-95AA-78B6F3035DF6(a)microsoft.com... >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: John Spencer on 22 Mar 2010 14:24 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!
From: Jerry Whittle on 22 Mar 2010 14:40 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 22 Mar 2010 14:49
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! |