Results 1 to 7 of 7
  1. #1
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63

    Failing to grasp the syntax of a Union Query


    I have two tables that joined together by a third table to allow a many to many relationship. the tables are as follows:

    tblProductInfo --> tblProductLinerMM <---tblLiner

    Since each product can use multiple liners, the join table manages the relationship allowing for multiple liners to be associated with multiple products. This is all setup and working fine.

    The challenge I have now is that I want to create a query that will return a single row per product and have each liner be an additional field. From what I can tell, I need to use a union query to achieve this, but when I try to run my query I get the error message:

    "The Number of columns in the two selected tables or queries of a union query do not match."

    Can anyone tell what I'm doing wrong?

    Code:
    SELECT ProductNumber, ProductDesc1
    FROM tblProductInfo
    UNION ALL
    SELECT PLLinerID
    FROM tblProductLinerMM

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The first SELECT has 2 fields and the second SELECT has only 1 field. All SELECT statements must have the same number of fields. If you don't have corresponding field then populate with Null or empty string or some literal value. The field names of the first SELECT will be the field names displayed by the UNION. Use AS alias if you want to show different name.

    SELECT ProductNumber AS ProdNum, ProductDesc1 AS ProdDesc FROM tblProductInfo
    UNION ALL SELECT PLLinerID, "Unknown" FROM tblProductLinerMM;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is one of those rare times when the error message is quite descriptive. You have 2 fields in the first SELECT and only 1 in the second. You can create a second blank field like:

    SELECT PLLinerID, "" As ProductDesc1

    That said, I'm not sure a UNION query is what you want, but see if it is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Can anyone tell what I'm doing wrong?
    The error message already told you. You're selecting two fields from tblProductInfo but only one field from tblProductLinerMM on the Union side of the query. The number of fields selected must match on both sides of a Union query. However, a Union query may not necessarily be what you need in the first place. Why don't you give us an example of what results you want to see from the query, then we can offer more specific advice.

  5. #5
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Doh! Too slow.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Second place...that makes me first loser.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Well, both of you pointed out that UNION might not be appropriate (good point) and Beetle suggested providing more info. Call it a team effort - yay, Go Team!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Double dlookup failing
    By JeroenMioch in forum Programming
    Replies: 3
    Last Post: 02-07-2013, 01:50 PM
  2. Updateable query failing in Runtime - 2010
    By ChefContainer in forum Queries
    Replies: 1
    Last Post: 02-05-2013, 07:29 PM
  3. Replies: 8
    Last Post: 10-22-2012, 07:43 PM
  4. Export to Excel failing
    By JChalmers in forum Import/Export Data
    Replies: 1
    Last Post: 11-11-2011, 11:02 AM
  5. Failing on my database miserably
    By jmathews004 in forum Access
    Replies: 6
    Last Post: 04-11-2010, 03:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums