Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Access updates with null

    Hi,

    This is my first post on here - i have a basic understanding of access, but still build most of my queries with the wizard rather than in SQL - so please be gentle with me!! I can usually follow them through in SQL, and have searched the forum on here to help me with problems before, but i couldn't find what i need to sort this one out..... i suspect i do not know the function name i need to get the search results to help me!!

    I have built a query that will update values in the master table with values from a recharge table as follows:

    UPDATE [Master]
    INNER JOIN [Recharge] ON [Master].Unique = [Recharge].Unique
    SET [Master].[Amount Recharged] = [Recharge]![Total Held], [Master].[Total Held] = 0;

    The update works for rows where a value exists in [Recharge] - replacing the [Master].[Amount Recharged] value with the [Recharge].[Total Held] value, and the [Master].[Total Held] with zero. However, where there isn't an entry, it replaces the values in [Master].[Amount Recharged] and [Master].[Total Held] with null fields.

    How do i get the query to update items in [Master] ONLY where there is a corresponding value in [Recharge] leaving all other values in [Master] as they were originally - i.e. if there is no data in the recharge table for an item in the master table, take no action to that item (but updating entries where there are)?

    I tried adding in a WHERE EXISTS statement (that i stole from a thread where someone was trying something similar) but get the message
    "operation must use an updatable query".

    Any help to sort this out would be greatly appreciated!!!

    Thanks in advance

    Paul

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    UPDATE [Master]
    INNER JOIN [Recharge] ON [Master].Unique = [Recharge].Unique
    SET [Master].[Amount Recharged] = [Recharge]![Total Held], [Master].[Total Held] = 0
    WHERE [Recharge].Unique IS NOT NULL;

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    Thanks

    ......it's easy when you know how!!

    Works perfectly - many thanks.

    Can't believe i didn't think of this myself...........

Posting Permissions

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