Search code examples
ms-access-2016

Insert records from two different tables into one

Table 1: MSISDN Table

-----------------------------
ID       MSISDN        Number
-----------------------------

Table 2: Account Manager Table

-----------------------------------------------------------
ID    Account Manager Name    Account Manager Email   Count
-----------------------------------------------------------

Table 3: Assignment Table

---------------------------------------------------------------------------------
ID    Account Manager ID    Account Manager Name    Account Manager Email   Count
---------------------------------------------------------------------------------

Whenever there is a new entry in MSISDN table, I want to insert MSISDN_Number along with ID, Account Manager Name, Account Manager Email from Account Manager Table INTO Assignment Table automatically. There is one condition on Account Manager Table that I want to select only records for lowest count under count column in Account Manager Table.

My Approach: 1. To get details from Account Manager Table for lowest count:

SELECT TOP 1 AccountManager_Table.[ID], AccountManager_Table.[Account Manager Name], AccountManager_Table.[Account Manager Email]
    FROM AccountManager_Table
    Where AccountManager_Table.[Count] =(SELECT MIN(Count) from AccountManager_Table);

2.Not working INSERT Into Query:

INSERT INTO Assignment_Table ( [Account Manager ID], [Account Manager Name], [Account Manager Email],[MSISDN No] )
Values(
  (SELECT TOP 1 AccountManager_Table.[ID], AccountManager_Table.[Account Manager Name], AccountManager_Table.[Account Manager Email]
  FROM AccountManager_Table
  WHERE (((AccountManager_Table.Count)=(SELECT MIN(Count) from AccountManager_Table)))
  ),
  (SELECT MSISDN_Table.[MSISDN Number]
  FROM MSISDN_Table
  WHERE ID = (SELECT MAX(ID) FROM AccountManager_Table)
  )
);

I want to insert MSISDN and Account Manager Details in assignment table together.Please help :( Thanks.

Solution

  • You need to make both of your SELECT statements into a single SELECT statement.

    Look at how to do a JOIN:

    https://www.w3schools.com/sql/sql_join.asp

    Also, your third table doesn't have a column for MSISDN Number, so you'll have to add that column. The columns being fed into the table need to match to columns that exist in the table.