Results 1 to 6 of 6

Thread: Merge Tables?

  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: Merge Tables?

    This is probably dead easy... IDK how tho.

    I have two tables linked to an excel file.
    Each table is a list of people, 90% of the columns in both tables are identical.

    I want to create a new table which is basically a merge of the two tables on all those columns, so that instead of having 2 tables I have one with all the data.

    No data in T2 is in T1

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    no sure what you mean by two tables linked to an excel file but if you can see tables in Access, try:
    dim strSQL as string
    strSQL = "INSERT INTO T1(fName, lName) SELECT fName, lName) FROM T2
    currentdb.execute strSQL



    90% of the columns in both tables are identical
    you can only INSERT INTO "columns" (fields) that exist in the target: if T2 contains columns that do not exist in T1 you must first design-view add the columns to T1

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2009
    Posts
    11
    Just means instead of importing the data into access from an excel file, I link the access DB to the excel file. So if I make changes in the excel file, they reflect the Access DB

    I think what I need is a full join which I hear access does not do.

    For example:
    T1
    FirstName, LastName, ID, Address, Postal Code, X, Y, Z

    T2
    FirstName, LastName, ID, Address, Postal Code, A, B, C

    I want
    T3
    FirstName, LastName, ID, Address, Postal Code

    But with all rows from t1 and T2 not just ones that match, because none should.

    So..
    Sub FJOIN()
    Dim strSQL As String
    Str "INSERT * INTO Master FROM T1"
    CurrentDb.Execute strSQL
    Str "INSERT * INTO Master FROM T2"
    CurrentDb.Execute strSQL
    End Sub

    But that doesnt work.
    Just wana concat the tables...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Just wana concat the tables...
    where is the result going?
    T1? - my suggestion works
    T3 (!!!!we didn't have a T3 until now)?

    for T3, before you start make sure you have a T3 with fields FirstName, LastName, ID, Address, Postal Code then:
    dim strSQL as string
    strSQL = "INSERT INTO T3(FirstName, LastName, ID, Address, Postal Code) SELECT FirstName, LastName, ID, Address, Postal Code FROM T1"
    currentdb.execute strSQL
    dim strSQL as string
    strSQL = "INSERT INTO T3(FirstName, LastName, ID, Address, Postal Code) SELECT FirstName, LastName, ID, Address, Postal Code FROM T2"
    currentdb.execute strSQL


    that wont actually work because you have a space in a fieldname: either fix it or replace Postal Code in the above SQL with the ugly [Postal Code]
    then you have a T3

    you could UNION in a query - where do you see an issue with that?
    SELECT FirstName, LastName, ID, Address, Postal Code FROM T1
    UNION
    (??? UNION ALL)
    SELECT FirstName, LastName, ID, Address, Postal Code FROM T2

    izy

    LATER:
    finally my reading skills kick-in: you can't INSERT * because you can only INSERT INTO "columns" (fields) that exist in the target and you are talking of A, B, C in source and X, Y, Z in target (or the other way around)



    EVEN LATER:
    you can alias the fields: e.g. if it happens to be NameLast in T1 and LastName in T2, you could SELECT NameLast As LastName FROM T1 (...and UNION or INSERT)
    the cognoscenti say (with relentless and numbingly boring frequency) NOT to use SELECT * - don't.
    Last edited by izyrider; 02-18-09 at 16:24.
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2009
    Posts
    11
    I thought union only gave the results that was in BOTH, I dont wont that.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    no - it adds the two sets together (provided that the two sets are capable of being added together - can't add apples and oranges)

    izy


    izy
    currently using SS 2008R2

Posting Permissions

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