Results 1 to 5 of 5
  1. #1
    kbouche4 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    3

    Archaeological database - Need help with update query: Replace__with___where____=____


    Hello!

    I'm currently creating an archaeological database. Two of my tables are Artifacts and SiteName. All of my sites in SiteName have an ID. I have a column in Artifacts for SiteID so what I want to do it link the Artifacts to the sites they belong to. I figured it would be something like: replace Artifacts.SiteID with SiteName.ID where Artifacts.Name is equal to SiteName.Name (i put the site names in the artifacts table as well as in the sitename table so as to keep everything in order).

    I've only just started using Access so its a big step for me just to understand what it is I have to do to accomplish this! Is there anyone who can show me how to do this in Access?

    Here is what I've tried: I ran an update query, selected the Artifacts table and the SiteID field in that table. It looks like this on my comp:
    Field: SiteID
    Table: Artifacts
    Update to: [SiteName].[ID]
    Criteria: [Artifacts].[Name]=[SiteName].[Name]

    Unfortunately this doesn't work and I don't understand why not. It seems to make sense to me. Is there anyone who can help me figure out what I'm doing wrong? I've been on so many Microsoft Access help websites but I still can't figure it out! Thanks so much for your time and help,

    Kaye

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I don't why do you want to put siteName in field siteID.

    But here is the query to do the update:

    update artifacts inner join sitename on artifacts.siteid=sitename.siteid set artifacts.siteid=sitename.sitename

  3. #3
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    If you're using this:

    Quote Originally Posted by kbouche4 View Post
    Criteria: [Artifacts].[Name]=[SiteName].[Name]
    as criteria for SiteID, then that would be the problem.

    Try joining the two tables on those fields instead.

    Cheers,

  4. #4
    kbouche4 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by weekend00 View Post
    I don't why do you want to put siteName in field siteID.

    But here is the query to do the update:

    update artifacts inner join sitename on artifacts.siteid=sitename.siteid set artifacts.siteid=sitename.sitename
    I think you misread, I am trying to put the ID of the site from the SiteName table into the SiteID column in the Artifacts table where the site names match between the two tables. This way I can see which artifacts are from which site.

  5. #5
    kbouche4 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by ConneXionLost View Post
    If you're using this:



    as criteria for SiteID, then that would be the problem.

    Try joining the two tables on those fields instead.

    Cheers,
    Hi there,

    Thank you for the help. Unfortunately, these tables should not join. I have five tables in this database and they are separated for a purpose.

    Thank you for trying though

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

Similar Threads

  1. How can I update a query using VBA?
    By thestappa in forum Programming
    Replies: 2
    Last Post: 06-28-2010, 04:01 PM
  2. How to make update to database???
    By skocev in forum Access
    Replies: 2
    Last Post: 03-31-2010, 02:47 PM
  3. Update Query
    By vvasudev in forum Queries
    Replies: 0
    Last Post: 11-10-2009, 01:32 AM
  4. Update Query- selective update?
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-29-2009, 11:15 AM
  5. Replies: 1
    Last Post: 12-09-2005, 09:16 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