Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Updatable Query?

    Got a simple problem with an update query that checks for duplicate information and updates the table. Both queries are written dynamically. The first query is saved as a query, the second is run using DoCmd.RunSQL

    qryTestDupEmail:
    SELECT tblLeads.EmailAdd
    FROM tblLeads
    WHERE tblLeads.Campaign=2 AND tblLeads.LeadDate>#1/30/2006#
    GROUP BY tblLeads.EmailAdd
    HAVING Count(tblLeads.EmailAdd)>1;


    RunSQL:
    UPDATE tblLeads INNER JOIN qryTestDupEmail
    ON tblLeads.EmailAdd = qryTestDupEmail.EmailAdd
    SET tblLeads.DupLead = True, tblLeads.WhyReturned = 'Duplicate lead'
    WHERE tblLeads.LeadID)>38275 AND tblLeads.Campaign=2;


    Access reports "Operation must use an updateable query" (no number given)

    Am I missing something simple here?

    tc

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Try

    PHP Code:
      UPDATE tblLeads 
         SET yourField
    ="yourNewValue"  
          
    WHERE tblLeads.Campaign=AND tblLeads.LeadDate>#1/30/2006#
          
    GROUP BY tblLeads.EmailAdd
          HAVING Count
    (tblLeads.EmailAdd)>1

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Access won't allow updating in a group by query (or a query that links to a group by query). If you think about it the former is obvious. The latter is a little frustrating.

    You could get round your particular problem by using a DCount and getting rid of the Having and Group By clauses or you could save the results of the first query to a table and use that.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Thanks.

    Due to the number of records, DCount, especially in a query, would have been very slow, so I opted for the temp table. It's too bad Access has still not embraced the "Unbound Recordset" because this would be a good example of when to use one.

    tc

Posting Permissions

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