Results 1 to 4 of 4
  1. #1
    barby.pava is offline Novice
    Windows XP Access 2002
    Join Date
    Feb 2016
    Posts
    11

    update query help

    hi everybody im new in access and I need help with an update query
    I have a
    Table
    Pet data Owner Reservation (the fields are add from a form)
    - ownerphone - ownerphone -ownerphone
    - dogname - name - dogname
    - vax1 - ownerlast name - vax1
    - vax2 - vax2
    -ownerlast name

    pet data connect to owner with ownerphone and dogname with ownerlastname


    owner connect to reservation with ownerphone

    now a owner can have multiple dogs
    in the reservation table a dog can have multiple reservation

    what I want and need that if in the pet data I have to change the vax1 date after I click on the exit bottom the reservation field vax1 update.
    I made it with this sql

    UPDATE Reservations LEFT JOIN [Pet Data] ON Reservations.HPHONE = [Pet Data].HPHONE SET Reservations.[DHLP-P,DRCP] = [PET DATA].[DHLP-P,DRCP], Reservations.[K-9 Flu] = [PET DATA].[K-9 FLU];

    the problem that I have is if a owner has 2 dogs the reservation vax1 pick up the 2nd dog info instead only pick up the data from the 2 different dogs.

    hope somebody will help me understand because I have never done an update query

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should Normalize your data. It will help you structure your data and make queries and data manipulation much simpler.
    Also, UPDATE queries can be very unforgiving, best to make a backup of your database before attempting same.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names.

    Poor field names:
    [Pet Data] - has space
    [K-9 FLU] - has space and dash
    [DHLP-P,DRCP] - has dash and comma

  4. #4
    barby.pava is offline Novice
    Windows XP Access 2002
    Join Date
    Feb 2016
    Posts
    11

    Solved!!

    Quote Originally Posted by ssanfu View Post
    Also shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names.

    Poor field names:
    [Pet Data] - has space
    [K-9 FLU] - has space and dash
    [DHLP-P,DRCP] - has dash and comma

    The only think that i had to do was to make and extra relation pet data dog name with reservations dog name.

    And is working

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

Similar Threads

  1. Replies: 14
    Last Post: 08-12-2014, 06:33 AM
  2. Replies: 3
    Last Post: 05-27-2014, 09:31 AM
  3. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  4. Replies: 2
    Last Post: 08-30-2012, 07:59 AM
  5. Replies: 4
    Last Post: 11-10-2011, 03:41 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