Microsoft Access 2010 Union Query Error Message

Posted on 2012-08-23
Last Modified: 2012-08-29
I have a union query that combines the fields from 15 other queries. I can run the union query and it works perfectly fine but when I try to use that union query in a report I receive the following error: "The number of columns in the two selected tables or queries of a union query do not match".

Like I said, I can run the union query with no problems it just when I try to use that query as a record source in a report that I get the error.
Question by:CamposInc
    LVL 30

    Expert Comment

    Option 1:
    Reduce the number of queries and check.
    If it works add a new query and recheck.
    Repeat previous step and report when the error starts to show.

    Option 2:
    Upload the database, with reduced number of data records leaving only the necessary objects to produce the problem.

    Author Comment

    Thanks for the quick reply.

    I tried removing all but two of the queries in the union query and it was still allowing me to run the query but when I use that query in the report it gives me the same error.

    I'm attaching a zipped copy of the database with only the tables/queries/reports needed to reproduce the error.

    Thanks for your help!
    LVL 30

    Expert Comment

    Please comment on these queries, because they have different number of fields from the rest of queries which have have 9 fields.

    qryProjectReport-RevenueAllocation01-Bid   242
    qryProjectReport-RevenueAllocation01-RSBucket99          15
    qryProjectReport-RevenueAllocation02-ProjectCosts        3
    qryProjectReport-RevenueAllocation03-OutsourcedVendors   3
    qryProjectReport-RevenueAllocation04-EquipmentServices   3
    qryProjectReport-RevenueAllocation05-Expenses            3
    qryProjectReport-RevenueAllocation99-LastFacilityDate01  3
    qryProjectReport-RevenueAllocation99-LastFacilityDate02  4

    Author Comment

    This is my starting point. This contains the projects that all the other queries are based off of.

    This is my final query. This is a combination of the qryProjectReport-RevenueAllocation01-Bid query and the qryProjectReport-RevenueAllocation01-RSBucket98 query.

    These four queries determine if there are any other associated costs for each of the projects in qryProjectReport-RevenueAllocation01-Bid.

    These two queries determine the "RevenueMoveDate" for SOME of the projects.

    The only queries that are in the union query are the queries with nine fields in them.

    Accepted Solution

    I have not figured out why this is happening, however, I have found a workaround for this.

    I created a query that takes the data in qryProjectReport-RevenueAllocation01-RSBucket99
     (my final query that I could not use in my report) and made a "make table" query.

    This creates a temporary table that I can then use in my report.

    Author Closing Comment

    Found a work around that accomplishes the same thing as I wanted.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now