Union Query Question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Dalan
    #1

    Union Query Question

    From reading Access 97 help text, it seems that to do what I need to
    do will require a Union Query. As this would be my first, I think I
    might require a little guidance.

    I have two tables with 10 fields that have like data (for instance
    both have an item description field, an item price field, a general
    notes field, etc.) but with different field names. The tables have
    approximately 20/40 other fields that are dissimilar and not needed
    for the query. Access help indicated that in order to do a Union Query
    each table must have an identical number of fields. So, is the
    creation of two new tables, or two selection queries a requirement in
    order to satisfy, or are there other workarounds?

    I assume somewhere there is an option to select an existing field name
    or create a new one? Would there be any other issues to focus on?

    Thanks for your help.
    Tags: None
    • paii
      #2
      Re: Union Query Question

      Each query in the union must return the same number and order of fields, the
      tables do not have to match. You can use a calculated field to fill-in data
      for fields the underling table is missing. Also look-up the difference
      between UNION and UNION ALL to ensure you get all the records you need. The
      easiest way to build the UNION is to create each query separately then cut
      and past between the SQL windows.

      "Dalan" <other@safe-mail.net> wrote in message
      news:504f21f6.0 309160457.69c2d c64@posting.goo gle.com...[color=blue]
      > From reading Access 97 help text, it seems that to do what I need to
      > do will require a Union Query. As this would be my first, I think I
      > might require a little guidance.
      >
      > I have two tables with 10 fields that have like data (for instance
      > both have an item description field, an item price field, a general
      > notes field, etc.) but with different field names. The tables have
      > approximately 20/40 other fields that are dissimilar and not needed
      > for the query. Access help indicated that in order to do a Union Query
      > each table must have an identical number of fields. So, is the
      > creation of two new tables, or two selection queries a requirement in
      > order to satisfy, or are there other workarounds?
      >
      > I assume somewhere there is an option to select an existing field name
      > or create a new one? Would there be any other issues to focus on?
      >
      > Thanks for your help.[/color]


        Comment

        • dogwalker
          #3
          Re: Union Query Question

          you already have the tables (so you don't need new tables)
          you can use two queries but you don't have to.
          the fieldnames have to be the same.
          look at the examples in the help (they use the AS keyword)
          also consider the ALL keyword (UNION ALL SELECT)
          (or you may get less records than you expect)


          "Dalan" <other@safe-mail.net> wrote in message
          news:504f21f6.0 309160457.69c2d c64@posting.goo gle.com...[color=blue]
          > From reading Access 97 help text, it seems that to do what I need to
          > do will require a Union Query. As this would be my first, I think I
          > might require a little guidance.
          >
          > I have two tables with 10 fields that have like data (for instance
          > both have an item description field, an item price field, a general
          > notes field, etc.) but with different field names. The tables have
          > approximately 20/40 other fields that are dissimilar and not needed
          > for the query. Access help indicated that in order to do a Union Query
          > each table must have an identical number of fields. So, is the
          > creation of two new tables, or two selection queries a requirement in
          > order to satisfy, or are there other workarounds?
          >
          > I assume somewhere there is an option to select an existing field name
          > or create a new one? Would there be any other issues to focus on?
          >
          > Thanks for your help.[/color]


            Comment

            • Pieter Linden
              #4
              Re: Union Query Question

              You need to make sure you alias the field names so the aliases/names
              match... Say you have two tables that are union-compatible (field
              types are the same).

              TableA(Field1, Field2, Field3)

              TableB(FieldA, FieldB, FieldC)

              SELECT TableA.Field1 As FieldA, TableA.Field2 As FieldB, TableA.Field3
              As FieldC
              FROM TableA
              UNION ALL
              SELECT FieldA, FieldB, FieldC
              FROM TableB...

              then you can query that result... but NOTE one thing... You CANNOT
              update it. (because there's no way to find the original record's table
              from a union query).

                Comment

                Previous template Next
                Working...