Results 1 to 7 of 7
  1. #1
    tanyalee123 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    51

    Unhappy I am stumped...adding field to existing table

    I just cannot figure out how to do this...I hope I can explain properly, and thank you in advance if you have any ideas.



    I have 2 tables, let's call them NEW DECEASED and ALL DECEASED.

    Both tables have a SSN field that match up. ALL DECEASED has years of data (including the information from the NEW DECEASED table); NEW DECEASED has data only from the last few months. This is the table that I need to work with.

    ALL DECEASED table has a field called alias, but when I was given the NEW DECEASED table, it didn't have that field, and I need it. I need to match up, via SSN, the two tables and have the alias field populate from the ALL DECEASED table into the NEW DECEASED table. Does that make sense? So wheverever a SSN matches up in both tables, I want the ALL DECEASED alias field to populate into the NEW DECEASED table. (Basically I was given 2 tables, but unfortunately I need all of the data from the NEW DECEASED table, but only the alias from the ALL DECEASED table).

    Is this an update query? Any ideas???

    Thank you everyone!!
    Tanya

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    Why are there two tables at all? Use a query to filter records for the 'new deceased' based on a date criteria. What defines 'new' - last year, last 6 months?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tanyalee123 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    51
    The reason why there are 2 tables is that I am getting these tables from another person, and apparently where she is getting her information, all the fields I need are not on one table. Frustrating yes. The NEW DECEASED table has ALL of the informatinon I need, except for that alias field. I don't really want all of the old deceased information. "New" is about 8 months. Truthfully, I don't even care if I "merge" these 2 tables together...I can find my new data afterward, delete columns I don't need, etc. That would be fine, I just don't know how how to do this basic procedure of putting the information together into one table with the SSN fields matching.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    Build a query that joins the two tables on the SSN fields, drag the SSN and alias fields to the grid, click "UPDATE" on the design tab, under the New Deceased [alias] field, on the Update To row: [All Deceased].[alias]

    Run the query.

    First, test on copy of the New Deceased table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    First, I want to second June's suggestion to do all your testing on a test copy of your data. Back up your database early and often, and do your fumbling in a junk copy.

    Second, I wanted to point out that social security numbers are not unique. Two people can have the same one. It's not common, but it can happen, because the first five digits encode meaningful information, and they reuse the numbers after people die (and sometimes before).

    As such, you should use your date of death (or month and year of death) as an additional qualification on your update. the SQl for that should look something like this:
    Code:
    UPDATE tblNewDeceased AS TNew, tblAllDeceased AS TAll
    SET TNew.MyAlias = TAll.MyAlias
    WHERE TNew.MySocial = TAll.MySocial
    AND Month(TNew.MyDeathDate) = Month(TAll.MyDeathDate)
    AND Year(TNew.MyDeathDate) = Year(TAll.MyDeathDate);
    Obviously, you'll need to substitute your field names and table names, or do it in the query designer.

  6. #6
    tanyalee123 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    51
    Thank you both so much, I will try everything. And yes Dal Jeanis, you're right, they are not alwasy unique. Also, I may have a SSN more than once in my table because it's possible that more than one person related to my participant (that is the person with the SSN) passed away (i.e. the spouse and child, or even the participant and child, etc.)

  7. #7
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Wow. Okay, you'll have to figure out your business rules for updating the alias, then. Be sure to test in a junk database, because update queries are like loaded weapons. once you have a query that you think is working, create a fresh junk database and copy ONLY that query into it. run it and verify your results. If it still works, then you can do it to your production data. AFTER making a backup of the production data.

    If I sound paranoid, it's because I am.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-20-2013, 09:14 AM
  2. Adding data from linked table to existing table???
    By claysea in forum Import/Export Data
    Replies: 3
    Last Post: 02-21-2013, 12:23 PM
  3. Replies: 1
    Last Post: 01-31-2012, 11:54 AM
  4. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 PM
  5. Replies: 0
    Last Post: 10-14-2009, 02:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums