Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    San Francisco Bay Area
    Posts
    4

    Unanswered: Updating a table from a select query

    I'm a semi-newby, and I have limited experience with update queries. Basically, I have some fields in a table from which I make a couple of calculations. What I then want to do is update two blank fields in that same table with the calculated data. I've written a select query that gives me the information I need: Count and Amt. Here is the select query:

    SELECT ExportData.FAMLYID, ExportData.M1ID, ExportData.M2ID, ExportData.M3ID, ExportData.M4ID, IIf([M2ID] Is Null,1,IIf([M3ID] Is Null,2,IIf([M4ID] Is Null,3,4))) AS [Count], IIf([Count]=4,1550,IIf([Count]=3,1500,IIf([Count]=2,1440,720))) AS Amt
    FROM ExportData;

    What do I need to do in order to map [Count] to the Database field Mbrs and [AMT] to the Database field Dues?

    Thank you in advance for the help. - Vic

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Applying the same logic we should have:
    Code:
    UPDATE ExportData SET 
    ExportData.Mbrs = IIf([M2ID] Is Null,1,IIf([M3ID] Is Null,2,IIf([M4ID] Is Null,3,4))), 
    ExportData.Amt = IIf([M2ID] Is Null,720,IIf([M3ID] Is Null,1440,IIf([M4ID] Is Null,1500,1550)));
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Location
    San Francisco Bay Area
    Posts
    4

    Update a table from a select query

    I'm embarrassed about how simple this seems.

    I'll give it a try. Thank you.

    Vic

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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