Results 1 to 12 of 12
  1. #1
    marnelle is offline Novice
    Windows XP Access 2002
    Join Date
    Dec 2010
    Posts
    5

    How to create query that change data on the specific field in the table

    Hello, im new in access. I got a problem on this, i have a lots of data in my masterlist table subject to modification. Actually i only need to get rid on the two specific field in masterlist table, I have to insert data let say col1 and col2 (these are col under masterlist table) i need to input dta one by one but it takes a lot of time for me.

    is there any good and easy solution for this?,.im really glad i found this forum, and i hope you guys will help me with this. Really appreciate your time spending with me to solve this problem..Godspedd

  2. #2
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    What about Find and Replace (ctrl+H)?

    Also have you tried an update query?

  3. #3
    marnelle is offline Novice
    Windows XP Access 2002
    Join Date
    Dec 2010
    Posts
    5
    What about Find and Replace (ctrl+H)?

    Also have you tried an update query?
    yeah,.i think that way but what would be my expression?.i think it takes a lot of time too because every field in a column in every ID has a different data.

    what if i will make a table (with the same fields as the masterlist) and feed all the changes on it then and we will make a query that compares both table and if the data from the masterlist is not the same in the new table it will replace it.

    does it make sense?.is it possible?.i have no idea,.huh!

  4. #4
    William McKinley is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    59
    Quote Originally Posted by marnelle View Post
    yeah,.i think that way but what would be my expression?.i think it takes a lot of time too because every field in a column in every ID has a different data.

    what if i will make a table (with the same fields as the masterlist) and feed all the changes on it then and we will make a query that compares both table and if the data from the masterlist is not the same in the new table it will replace it.

    does it make sense?.is it possible?.i have no idea,.huh!
    Can you shed some light on what you are trying to update? Is it records containing certain information?

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you didn't tell us about you new values list, are they in a table,or Excel sheet, how are they organized?

    you need to have a unique key column in your masterlist, and same unique key in you new values list.

    if you have the new value table , you can :
    1 open this table, sort by the key column, hightlight and copy the two value columns.
    2 open masterlist table, sort by the key column, hightlight the two columns, paste
    3 close the 2 tables

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by marnelle View Post
    yeah,.i think that way but what would be my expression?.i think it takes a lot of time too because every field in a column in every ID has a different data.

    what if i will make a table (with the same fields as the masterlist) and feed all the changes on it then and we will make a query that compares both table and if the data from the masterlist is not the same in the new table it will replace it.

    does it make sense?.is it possible?.i have no idea,.huh!

    If the updates are in another table and you have a way to link the records by using some field then you can use an update query.

  7. #7
    marnelle is offline Novice
    Windows XP Access 2002
    Join Date
    Dec 2010
    Posts
    5
    you didn't tell us about you new values list, are they in a table,or Excel sheet, how are they organized?

    you need to have a unique key column in your masterlist, and same unique key in you new values list.

    if you have the new value table , you can :
    1 open this table, sort by the key column, hightlight and copy the two value columns.
    2 open masterlist table, sort by the key column, hightlight the two columns, paste
    3 close the 2 tables
    thanks for your reply guyz, that quote in above is that what the situation i mean BUT there are primary keys in the masterlist table which is not included in the new value list table

    example

    MasterlistTable
    Primarykey---name---- Country
    1------------ bob------Cuba
    2------------ robert----Panama
    3------------ Martha---Iceland
    4------------ John---- -Japan
    5------------ Shane----USA

    New_value_List_Table
    PrimaryKey----name----country
    1-------------bob----- Greenland
    2-------------robert----Panama
    4-------------John-----North Korea
    5------------ Shane----Autralia

    ok guyz, thats an example, you see the primary key #3 in the new_value_list_table is gone compared to masterlistTable.

    so, i just wanna ask, Is there any script that will compare two tables and look for the same primary keys and change the data of one column of the table you specify?..glad that i hear a lot from you guyz,..........many thanks

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by marnelle View Post
    thanks for your reply guyz, that quote in above is that what the situation i mean BUT there are primary keys in the masterlist table which is not included in the new value list table

    example

    MasterlistTable
    Primarykey---name---- Country
    1------------ bob------Cuba
    2------------ robert----Panama
    3------------ Martha---Iceland
    4------------ John---- -Japan
    5------------ Shane----USA

    New_value_List_Table
    PrimaryKey----name----country
    1-------------bob----- Greenland
    2-------------robert----Panama
    4-------------John-----North Korea
    5------------ Shane----Autralia

    ok guyz, thats an example, you see the primary key #3 in the new_value_list_table is gone compared to masterlistTable.

    so, i just wanna ask, Is there any script that will compare two tables and look for the same primary keys and change the data of one column of the table you specify?..glad that i hear a lot from you guyz,..........many thanks
    As previosuly suggested, you would use an update query joinng on the primary key field.

    Example:
    Code:
     
    UPDATE tblMasterList INNER JOIN tblNewValuesList ON tblMasterList.MasterLisrID = tblNewValuesList.NewValueLisrID SET tblMasterList.MasterListCountry = [tblNewValuesList].[NewValueListCountry]
    WHERE (((tblMasterList.MasterListCountry)<>[tblNewValuesList].[NewValueListCountry]));

  9. #9
    marnelle is offline Novice
    Windows XP Access 2002
    Join Date
    Dec 2010
    Posts
    5

    As previosuly suggested, you would use an update query joinng on the primary key field.

    UPDATE tblMasterList INNER JOIN tblNewValuesList ON tblMasterList.MasterLisrID = tblNewValuesList.NewValueLisrID SET tblMasterList.MasterListCountry = [tblNewValuesList].[NewValueListCountry] WHERE (((tblMasterList.MasterListCountry)&lt;&gt;[tblNewValuesList].[NewValueListCountry]));
    ok sir,.i will try that when i got home,.i appreciate your help,.thanks a lot

  10. #10
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    You're welcome.

    Let us know how it goes.

  11. #11
    marnelle is offline Novice
    Windows XP Access 2002
    Join Date
    Dec 2010
    Posts
    5
    You're welcome.

    Let us know how it goes.
    wow,.that's great sir ,.this is what i want.,.i owe you one sir,.thanks a lot, you really solve my problems.

  12. #12
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by marnelle View Post
    wow,.that's great sir ,.this is what i want.,.i owe you one sir,.thanks a lot, you really solve my problems.
    You're welcome.

    I am glad I could assist.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2010, 08:01 AM
  2. Replies: 1
    Last Post: 05-17-2010, 12:21 PM
  3. Replies: 15
    Last Post: 02-16-2010, 10:58 AM
  4. Replies: 7
    Last Post: 12-11-2009, 01:44 AM
  5. Replies: 1
    Last Post: 11-10-2009, 03:20 PM

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