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

Discussion in 'MS Access' started by kbouche4, Aug 30, 2010.

  1. kbouche4

    kbouche4 New Member

    Joined:
    Aug 30, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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. apr pillai

    apr pillai New Member

    Joined:
    Dec 17, 2010
    Messages:
    16
    Likes Received:
    12
    Trophy Points:
    0
    Occupation:
    Auditor
    Location:
    India
    Home Page:
    http://www.msaccesstips.com/
    Re: Archaeological database - Need help with update query: Replace__with___where____=

    Instead of running an Update Query every time when you find a new atrifact, it will be a better method if you design two forms for both Site details and Artifacts Table and link them together. Do this as follows;

    1. Design the Main Form for the Site Table
    2. Design a Sub-Form in Datasheet View or Continuous Form for Artifacts Table
    3. Insert the Artifacts form as Sub-form into the Main Site Form
    4. Select the Sub-form and display its property Sheet (View- ->Properties)
    5. Enter the SiteID in the Link Master Field Property
    6. Enter the SiteID of the Artifacts Table into the Link Child Field Property and save the Form.
    7. Before you attempt to enter an Artifact record find the Site Name in the Main Form.
    8. then enter the Artificats details in the sub-form.
    9. The SiteId will be automatically recorded into the Artiifacts table.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice