Results 1 to 3 of 3
  1. #1
    2Zons is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6

    Question Append Query to create combined data

    I am pretty rusty at Access. I am trying to do something that I know should be pretty simple but I cant figure out how to do it.



    I have a table I am trying to populate with records that are built off of 2 other tables. The table I am trying to populate is called "Instructions" and the tables I am using to populate it are called "Operations" and "Operands". The instruction table is a list of instructions that are made from Operations, and Operands. I want to create a string that is (Operation + ' ' + Operand +',' + Operand) that will hold all the combinations of instructions and operator. Example:

    Operations
    ID Name
    0 MOV
    1 ADD

    Operands
    ID Name
    0 A
    1 B

    Using the above example I would want the query to create the following records
    ID Instruction Name
    0 MOV A,A
    1 MOV A,B
    2 MOV B,A
    3 MOV B,B
    4 ADD A,A
    5 ADD A,B
    6 ADD B,A
    7 ADD B,B

    My query filters out combinations where both operators are the same, but for illustrative purposes I left them in here. I can make a select query that will generate those results in an expression field, but when I try and convert it to an update query I can't figure out how to send the expression to a field in a the Instructions table. Any help is appreciated, thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    ID should not begin with 0. Just set ID field as autonumber type.

    INSERT INTO Instructions(InstructionName) SELECT [Operations].[Name] & " " & [Operands].[Name] & "," & [Operands_1].[Name] AS Inst
    FROM Operands, Operations, Operands AS Operands_1;
    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
    2Zons is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    That's got it. Thanks for the quick reply. They are auto number fields in the actual tables I was just typing them in here as an example. I forgot that Auto ID start at 1.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-23-2016, 04:36 PM
  2. Replies: 10
    Last Post: 01-22-2016, 01:12 PM
  3. Create an Append Query
    By Gusio in forum Queries
    Replies: 3
    Last Post: 01-14-2014, 04:39 PM
  4. Using a form to create an Append query?
    By Access_Novice in forum Forms
    Replies: 6
    Last Post: 10-04-2013, 11:39 AM
  5. Replies: 22
    Last Post: 01-25-2011, 11:19 AM

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