Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Red face Unanswered: How to join/merge 2 Tables

    Greetings.

    How do I join 2 Tables which both have their own Primary Keys and have approx 15 different Fields each (total 30).

    I have struggled to understand Join, Union, Merge etc. can this be done using a Wizard?

    Thanks in advance.
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need to consider what happens to Columns and what happens to Rows in the resulting data set:

    1. JOIN: TableA = 5 columns, TableB = 4 columns

    - Columns:
    The resulting data set = 9 columns (if you select all columns form TableA and TableB and specify no other criteria with a WHERE clause).

    - Rows:
    a) INNER JOIN:
    Code:
    SELECT TableA.*, TableB.* 
      FROM TableA INNER JOIN 
           TableB ON TableA.Column1 = TableB.Column1;
    The resulting data set = only the rows that have the same value in TableA.Column1 and TableB.Column1.

    b) a) LEFT JOIN
    Code:
    SELECT TableA.*, TableB.* 
      FROM TableA LEFT JOIN 
           TableB ON TableA.Column1 = TableB.Column1;
    The resulting data set = all the rows in TableA. For the rows in TableB where TableB.Column1 do not match TableA.Column1, the 4 columns from TableB are Null.

    c) a) RIGHT JOIN
    Code:
    SELECT TableA.*, TableB.* 
      FROM TableA RIGHT JOIN 
           TableB ON TableA.Column1 = TableB.Column1;
    The resulting data set = all the rows in TableB. For the rows in TableA where TableA.Column1 do not match TableB.Column1, the 5 columns from TableA are Null.

    d) CROSS JOIN:
    Note: There is no operator for a CROSS JOIN in Access SQL, you simply use:
    Code:
    SELECT TableA.*, TableB.* 
      FROM TableA, TableB;
    This operation performs the Cartesian product of the tables involved in the join. The resulting data set has the number of rows in the TableA multiplied by the number of rows in TableB.

    Final notes on JOINS:
    a) You can specify more than one condition in the ON part of the JOIN operation:
    Code:
    SELECT TableA.*, TableB.* 
      FROM TableA INNER JOIN 
           TableB ON (TableA.Column1 = TableB.Column1) And
                     (TableA.Column2 > TableB.Column3);
    b) Nothing prevents you from specifying an additional criteria in a WHERE clause:
    Code:
    SELECT TableA.*, TableB.* 
      FROM TableA INNER JOIN 
           TableB ON TableA.Column1 = TableB.Column1
     WHERE TableA.Column3 Is Not Null;
    c) You can specify a subset of the columns from both tables. This will return 5 columns:
    Code:
    SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableB.Column1, TableB.Column2
      FROM TableA INNER JOIN
           TableB ON TableA.Column1 = TableB.Column1
    d) The columns involved in the JOIN (or in a WHERE clause) do not need to be present in the resulting set and the order of the columns is not fixed:
    Code:
    SELECT TableA.Column5, TableA.Column2, TableA.Column3, TableB.Column2, TableB.Column4
      FROM TableA INNER JOIN
           TableB ON TableA.Column1 = TableB.Column1
     WHERE TableA.Column4 Is Not Null;
    2. UNION: TableA = 5 rows, TableB = 4 rows. The resulting set = 9 rows.
    Note: The number of columns must be the same in all members of a UNION operation:

    a) If TableA = 5 Columns and TableB = 5 Columns:
    Code:
    SELECT TableA.*
      FROM TableA
    UNION 
    SELECT TableB.*
      FROM TableB;
    b) If TableA = 5 column, TableB = 4 column and the resulting data set must have 4 columns:
    Code:
    SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableA.Column4
      FROM TableA
    UNION 
    SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4
      FROM TableB;
    Note: You can select any 4 columns from TableA, so this is valid too:
    Code:
    SELECT TableA.Column1, TableA.Column3, TableA.Column4, TableA.Column5
      FROM TableA
    UNION 
    SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4
      FROM TableB;
    c) If TableA = 5 column, TableB = 4 column and the resulting data set must have 5 columns:
    Code:
    SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableA.Column4, TableA.Column5
      FROM TableA
    UNION 
    SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4, Null
      FROM TableB;
    Note: You can use any constant for replacing the missing 5th column in TableB provided that it's data type is compatible with the data definition of the correspondig column in TableA, so this is valid:
    Code:
    SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableA.Column4, TableA.Column5
      FROM TableA
    UNION 
    SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4, 'Missing Value'
      FROM TableB;
    - For a more detailed explanation on JOINs, see: Join Fundamentals and Using Joins
    - For a more detailed explanation on UNIONs, see: UNION (Transact-SQL)
    Last edited by Sinndho; 11-18-12 at 09:57. Reason: Additional info supplied
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thanks for the excellent explanation. I think you should write a book on how to use Access - then you could make your millions?

    I have printed your comments and will digest it over the next few days.

    Thanks ever so much for helping.
    Last edited by reddevil1; 11-18-12 at 12:25.
    What would you attempt to do if you knew you would not fail?

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    Just to kick-start me, please woudl someone advise what is wrong with the code below to join all columns of two tables?

    SELECT *
    FROM tblCountry
    JOIN tblEmployee;

    Syntax error in FROM clause
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It must be either:
    Code:
    INNER JOIN tblEmployee ON tblCountry.<Some Column> = tblEmployee.<Some Column>
    Or:
    Code:
    LEFT JOIN tblEmployee ON tblCountry.<Some Column> = tblEmployee.<Some Column>
    Or:
    Code:
    RIGHT JOIN tblEmployee ON tblCountry.<Some Column> = tblEmployee.<Some Column>
    Note: = in the criteria can be replaced by another comparison operator (<, >, <>, etc.)
    Have a nice day!

  6. #6
    Join Date
    Nov 2009
    Posts
    223
    I am trying to join my 2 Tables together to make an Overall Report of Income and Expenditure.

    My simplified test database has the following:-

    INCOME TABLE
    IncomeID
    AmountMoneyReceived
    DateMoneyReceived

    EXPENDITURE TABLE
    ExpenditureID
    AmountMoneySpent
    DateMoneySpent


    Therefore, I want 6 columns and 10 rows (eg. All the information from all Rows and all Columns from both Tables)


    SELECT *
    FROM tblIncome
    INNER JOIN tblExpenditure
    ON tblIncome.IncomeID=tblExpenditure.ExpenditureID;
    This only gives 6 columns and 5 rows


    SELECT tblIncome.*
    FROM tblIncome
    UNION
    SELECT tblExpenditure.*
    FROM tblExpenditure;
    This only gives 3 columns and 10 rows


    So how would I get all the information from both Tables (6 columns and 10 rows)?
    What would you attempt to do if you knew you would not fail?

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Oh my giddy aunt!!!

    FYI - the test database has 5 Income Rows and 5 Expenditure Rows = 10 rows.

    SELECT tblIncome.*, tblExpenditure.*
    FROM tblIncome, tblExpenditure;
    The above is the CrossJoin kindly suggested by Sinndho. This provides 6 columns but 25 rows.

    I am trying to get 6 columns and 10 rows.
    I would expect 5 (Income) rows to have data in 3 of the columns (with the other 3 columns blank.

    Maybe i am not explaining myself well?

    My overall aim is to have a Report showing all Income and Expenditure in one Report but I am not having much success as I cannot work out the design of a "joined?" query/report.
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    I am confused and think I am going round in circles??? Please can I summarise this again.

    My simplified test database has the following:-

    INCOME TABLE
    IncomeID
    AmountMoneyReceived
    DateMoneyReceived

    EXPENDITURE TABLE
    ExpenditureID
    AmountMoneySpent
    DateMoneySpent


    I want to produce a Profit/Loss Report which will look something like this:-

    ID Amount Recvd Amount Spent Date
    1 10,000 1/1/12
    2 12,000 15/1/12
    3 10,000 22/1/12
    1 5,000 23/1/12
    4 15,000 24/1/12
    2 20,000 30/1/12


    Please could someone advise the easiest way to do this. Aaaargh!!!
    What would you attempt to do if you knew you would not fail?

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    I have attached a sample Report......
    Attached Thumbnails Attached Thumbnails Profit Loss Report.PNG  
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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