Results 1 to 4 of 4
  1. #1
    samgardner is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    2

    How to make an update query?

    I have a number of records I need to update from another table. For some reason, it doesn't work.

    UPDATE BILLING_PERIOD SET BILLING_PERIOD.BILLING_MONTH = (SELECT BILLING_MONTH_YEAR.BILLING_MONTH from BILLING_MONTH_YEAR where ID = 1), BILLING_PERIOD.BILLING_YEAR = (SELECT BILLING_MONTH_YEAR.BILLING_YEAR from BILLING_MONTH_YEAR where ID = 1)


    WHERE BILLING_PERIOD.BILLING_MONTH is null;

    BILLING_MONTH_YEAR is a one-record table, but would be updating multiple records in BILLING_PERIOD, which I want to update with all the same values. For some reason the query above gives me an "Must be an updateable query" error even though there's no aggregation. Can someone suggest how I can update this to work?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why is BILLING_MONTH_YEAR a one-record table? How is this record populated? If there is only one record then WHERE clause should not be needed for source. Don't use nested query.

    UPDATE BILLING_PERIOD, BILLING_MONTH_YEAR SET BILLING_PERIOD.BILLING_MONTH = [BILLING_MONTH_YEAR].[BILLING_MONTH], BILLING_PERIOD.BILLING_YEAR = [BILLING_MONTH_YEAR].[BILLING_YEAR] WHERE BILLING_PERIOD.BILLING_MONTH Is Null;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    samgardner is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    2
    Quote Originally Posted by June7 View Post
    Why is BILLING_MONTH_YEAR a one-record table? How is this record populated? If there is only one record then WHERE clause should not be needed for source. Don't use nested query.

    UPDATE BILLING_PERIOD, BILLING_MONTH_YEAR SET BILLING_PERIOD.BILLING_MONTH = [BILLING_MONTH_YEAR].[BILLING_MONTH], BILLING_PERIOD.BILLING_YEAR = [BILLING_MONTH_YEAR].[BILLING_YEAR] WHERE BILLING_PERIOD.BILLING_MONTH Is Null;
    Thank you!

    As for why it's one record, it's that way because I make it that way. I fill it based on another procedure that ensures it has one row. I don't think I've seen a joinless query before, so that's neat.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It's called Cartesian association of records. Every record in each table associates with all records of other table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Update Query to Make a String Uniform
    By razkowski in forum Queries
    Replies: 7
    Last Post: 12-05-2018, 02:40 PM
  2. Replies: 8
    Last Post: 03-26-2018, 04:11 PM
  3. How to make a query update in real time
    By rfcomm2k in forum Queries
    Replies: 2
    Last Post: 02-06-2017, 08:05 AM
  4. How to make an update query
    By KWHAT in forum Access
    Replies: 4
    Last Post: 12-10-2011, 05:12 PM
  5. How to make an Update Query
    By cd3atm in forum Queries
    Replies: 1
    Last Post: 12-06-2010, 02:43 PM

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
  •  
Other Forums: Microsoft Office Forums