Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    28

    Unanswered: Table Update with Count in Nested Query

    Hi. I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.

    I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.

    Code:
    Update dbo_BM_Map inner Join Temp_Progression_Populate
    on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID
    Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete
    Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No
    And Temp_Progression_Populate.Initial_Mapping_Complete in
    	(Select count(Initial_Mapping_Complete), Chapter_No
    	from Temp_Progression_Populate
    	Group by Chapter_No
    	Having Count(Initial_Mapping_Complete) = 1)
    I've spent too long trying to get this working. Any help appreciated.

    Thanks in advance
    Josh
    Attached Thumbnails Attached Thumbnails update.jpg  

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    UPDATE dbo_BM_Map
    SET Initial_Mapping_Complete = T2.Initial_Mapping_Complete
    FROM dbo_BM_Map T1
    INNER Join Temp_Progression_Populate T2 on T1.Product_ID = T2.Product_ID
    AND T1.Chapter_No = T2.Chapter_No
    WHERE T1.chapter_no IN (
    SELECT T1.chapter_no
    FROM dbo_BM_Map T1
    Group by T1.chapter_no
    Having Count(T1.Initial_Mapping_Complete) = 1 )

    SELECT * FROM dbo_BM_Map
    Last edited by corncrowe; 10-08-12 at 12:27.

  3. #3
    Join Date
    Mar 2012
    Posts
    28
    Hi Corncrowe.
    I used your code and adjusted it and now it works fine. Thanks

    The syntax for Access SQL is a little different, as you can see with the join. It's annoying.
    Here's my SQL.

    Code:
    Update dbo_BM_Map inner Join Temp_Progression_Populate
    on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID
    Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete
    Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No
    And Temp_Progression_Populate.Chapter_No in (Select Temp_Progression_Populate.Chapter_No
    from Temp_Progression_Populate
    Group by Temp_Progression_Populate.Chapter_No
    Having Count(Temp_Progression_Populate.Initial_Mapping_Complete) = 1)
    Thanks for your help.

Tags for this Thread

Posting Permissions

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