Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    7

    Unanswered: Union Query - Need Separate Columns from Each Table

    Good afternoon:

    I have a working UNION query combining the contents of two tables into one query. I have added a field to specifiy which table the data has come from. However, I need the data to have it's OWN field rather than combine into one. For example

    PropertyID Value1

    are my current fields. I would like the query to output

    Property ID.Table1 Property ID.Table 2 Value1.Table1 Value1.Table2

    Additionally, if the property ID matches, I need it to be all in one record.

    Any thoughts on how to achieve this?

    Any help will be greatly appreciated. I have wasted hours working on this and have now reached out to the experts!

    Fondly
    Tanya S.
    Last edited by TanyaS; 05-06-13 at 16:51. Reason: clarification

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by TanyaS View Post
    Property ID.Table1 Property ID.Table 2 Value1.Table1 Value1.Table2
    I guess you mean:
    Code:
    Table1.[Property ID] Table2.[Property ID] Table1.Value1 Table2 .Value1
    This is normally obtained using a FULL OUTER JOIN which, unfortunately, does not exist in Access SQL.

    A solution consists in combining two LEFT JOIN queries in a UNION query, something like this:
    Code:
    SELECT Table1.[Property ID], Table1.[Value1]
    FROM Table1
    LEFT JOIN Table2
    ON Table1.[Property ID] =  Table2.[Property ID]
    UNION
    SELECT Table2.[Property ID], Table2.[Value1]
    FROM Table2
    LEFT JOIN Table1
    ON Table1.[Property ID] =  Table2.[Property ID]
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    7
    Thank you so much for the quick reply. I have been playing with your suggestion. I am a beginner in SQL. I actually have three tables I am trying to piece together. Here is the current SQL statement (I am so sorry this is so long)...I need the data from Assessor Appeal Information and the BOR Appeal Information to keep separate columns with where the data came from:

    SELECT [Client Information].[Last Name], [Client Information].[First Name], [Property Information].ID, [Property Information].[File Name], [Property Information].Township, [Property Information].[Major Class], [Property Information].[PIN Abbreviated], [Assessor Appeal Information].[Assessor Tax Year], [Assessor Appeal Information].[Assessor Complaint Number], [Assessor Appeal Information].[Assessor Proposed], [Assessor Appeal Information].[Assessor Requested], [Assessor Appeal Information].[Assessor Final], [Assessor Appeal Information].[Assessor Rereview], "Assessor" AS type
    FROM ([Client Information] INNER JOIN [Property Information] ON [Client Information].ID = [Property Information].[Client Number]) INNER JOIN [Assessor Appeal Information] ON [Property Information].ID = [Assessor Appeal Information].[Property ID]
    WHERE ((([Assessor Appeal Information].[Assessor Tax Year])=[Forms]![frmReportChoice]![TaxYear]))
    UNION ALL SELECT [Client Information].[Last Name], [Client Information].[First Name], [Property Information].ID, [Property Information].[File Name], [Property Information].Township, [Property Information].[Major Class], [Property Information].[PIN Abbreviated], [BOR Appeal Information].[BOR Tax Year], [BOR Appeal Information].[Board Complaint Number], [BOR Appeal Information].[Board Proposed], [BOR Appeal Information].[Board Requested], [BOR Appeal Information].[Board Final], [BOR Appeal Information].[Board Rereview], "Board" AS type
    FROM ([Client Information] INNER JOIN [Property Information] ON [Client Information].ID = [Property Information].[Client Number]) INNER JOIN [BOR Appeal Information] ON [Property Information].ID = [BOR Appeal Information].[Property ID]
    WHERE ((([BOR Appeal Information].[BOR Tax Year])=[Forms]![frmReportChoice]![TaxYear]));

    Goodness, I am embarrassed this is taking so long. Any help you can offer will be greatly appreciated.

    Fondly,
    Tanya S.

Posting Permissions

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