Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Merge 2 different tables

    Hi,

    How is it possible to merge 2 different tables together.

    Table1 has AddressID, ApptNum, ApptDate, CompletedDate
    Table2 has AddressID, NANum, NADate, CompletedDate

    I want to merge into
    AddressID, Number, Date, CompletedDate, Description

    The Desctiption will show Appt for table1 and NA for table2.

    Thanks,

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you need some magic: your question denies the existance of "Number" & "Date" fields (BTW both are a poor choice for fieldnames IMO - "Date" is a reserved word, and both don't tell you a lot about the data within).

    SELECT AddressID, ApptNum As Description, magic1, magic2 FROM Table1
    UNION
    SELECT AddressID, 'NA' As Description, magic1, magic2 FROM Table2

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Using a UNION query.

    Code:
    SELECT AddressID, ApptNum As [Number] AppDate As [Date], CompletedDate, Description as "Appt"
    FROM Table1
    
    UNION
    
    SELECT AddressID, NANum As [Number] NADate As [Date], CompletedDate, Description as "NA"
    FROM Table2
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm too slow
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks StarTrekker.

    I tried the union statement and the query runs, the only issue I have is that the Description column doesnt have either "Appt" or "NA" input. It replicates the Address ID into that field.

    If I just table 1 instead of the Union query it will put "Appt" as Description.

    How can I get around it?
    Last edited by JezLisle; 05-12-08 at 12:41.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT AddressID, ApptNum As [Number] AppDate As [Date], CompletedDate, "Appt" AS Description 
    FROM Table1
    
    UNION ALL
    
    SELECT AddressID, NANum As [Number] NADate As [Date], CompletedDate, "NA" AS Description
    FROM Table2
    Some syntax errors corrected. Also, UNION ALL is more efficient if you don't have\ mind dupes.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for that... lol @ me for "Description as "Appt"!! :lol:
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks, I have tried this and still I cant get it to work. I have even changed Description to Descriptions to avoid any reserved words.

    This is the SQL in full.

    SELECT quniAddressAppts.uprn AS HRN, quniAddressAppts.ApptNum As ApptOrNANum,
    quniAddressAppts.ApptDate As ApptOrNADate, quniAddressAppts.completion_date
    AS [Completed?], "Appt" As Descriptions
    FROM quniAddressAppts
    UNION ALL SELECT quniAddressNoAccess.uprn AS HRN, quniAddressNoAccess.NA As ApptOrNANum,
    quniAddressNoAccess.NADate As ApptOrNADate, quniAddressNoAccess.completion_date
    AS [Completed?], "NA" As Desciptions
    FROM quniAddressNoAccess;

    This attached is the extract from the above SQL
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Code:
    SELECT quniAddressAppts.uprn AS HRN, 
               quniAddressAppts.ApptNum As ApptOrNANum,
               quniAddressAppts.ApptDate As ApptOrNADate, 
               quniAddressAppts.completion_date AS [Completed],
               "Appt" As Descriptions
    FROM quniAddressAppts
    
    UNION ALL 
    
    SELECT quniAddressNoAccess.uprn AS HRN, 
               quniAddressNoAccess.NA As ApptOrNANum,
               quniAddressNoAccess.NADate As ApptOrNADate, 
               quniAddressNoAccess.completion_date AS [Completed],
               "NA" As Descriptions
    FROM quniAddressNoAccess;
    Minor corrections is all I've made, a spelling error with Descriptions and I got rid of the question-marks.

    Other than that, the SQL looks clean to me. What do you mean by you can't get it to work? What "symptoms" do you have?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - defo avoid any non alphanumeric characters in column names (the only exception for me is underscore). For boolean stuff like that I call columns "is_completed" (or "isCompleted" if you prefer camel case).

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol. Looked at your data. In that case make it "completed_date".

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Picky picky! ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have tried the revised SQL that you have posted and I still have the same results.

    Under the Descriptions header I dont get either "Appt" or "NA". It replicates the HRN number in this field

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hmmm....

    If you run

    Code:
    SELECT quniAddressAppts.uprn AS HRN, 
               quniAddressAppts.ApptNum As ApptOrNANum,
               quniAddressAppts.ApptDate As ApptOrNADate, 
               quniAddressAppts.completion_date AS [Completed],
               "Appt" As Descriptions
    FROM quniAddressAppts;
    Do you get "Appt" in the Description field?

    Do the same test for the other segment of your SQL.

    Let us know the results
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh. I've reread everything properly. I thought the column not the alias had a "?" in it. I was addressing everything to the OP also, not correcting you. In any event - you got it right and I misunderstood. I'll drop out - you are doing a better job than me

Posting Permissions

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