Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Can't update query (2003/SR2)

    I have a DB that tracks families attending our Vacation Bible School. I have a table for the Parent info and one that tracks the Children. These are connected through the use of a Family #. I have an "Active" field for both the Parents Table and the Children Table. This is an annual event, so I keep all the information in the database and each year set each one to inactive and then change them to active as needed. I decided to add a form to make it easier to update the information. The Parent update form worked fine, the information comes directly from the table. To update the children, I created a query, so I could see the last names of the children. The query consists of Child's Name, which is the first name and Active from the Child Name table and the Last Name from the Parent Info table. I then built a form based on this information but I could not update the active field. I found that I could also not update the active field in the query. I'm not sure what the problem is, I am not getting an error message. Below is the SQL statement from the query. If anyone can tell me why I can't update, (and, it's probably something really obvious) I would appreciate it. Thanks.

    SELECT [Child Name].[Child's Name], [Parent Info].[Last name], [Child Name].Active
    FROM [Child Name] INNER JOIN [Parent Info] ON [Child Name].[Family #] = [Parent Info].[Family #]
    ORDER BY [Parent Info].[Last name], [Child Name].[Child's Name];

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't update query (2003/SR2)

    Is Family # the primary key in the Parent Info table?

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Can't update query (2003/SR2)

    It was not but as I looked at it, it probably should be.

    Taking a clue from your question, I made it the primary key and now the update works fine. Hans, you're a genius at this stuff. Why did that make a difference? Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't update query (2003/SR2)

    Making Family # the primary key tells Access that there can only be one record with a given Family # in the Parent Info table. This means that there can be no ambiguity in the query about which record to update.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •