Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: "The number of columns in the two selected

    Error . . .

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

    SELECT City, County
    , 'LISTDINGENTRYDATE' as count_type
    , Count(*) as count
    FROM tblCARETSResearchData
    WHERE LISTINGENTRYDATE Between #04/01/2009# And #04/30/2009#
    AND County = "Orange"
    GROUP BY City


    UNION ALL
    SELECT City
    , 'PENDINGDATE' as count_type
    , Count(*) as count
    FROM tblCARETSResearchData
    WHERE PENDINGDATE Between #04/01/2009# And #04/30/2009#
    AND County=" Orange"
    GROUP BY City

    UNION ALL SELECT City
    , 'CLOSINGDATE' as count_type
    , Count(*) as count
    FROM tblCARETSResearchData
    WHERE CLOSINGDATE Between #04/01/2009# And #04/30/2009#
    AND County = "Orange"
    GROUP BY City
    ORDER BY CITY;

    I cannot see where they don't match?

    There is only one table involved.

    Thanks.


    Rick

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    County is in the first, not the others.
    Paul

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Success Query

    I started from scratch in design view and then SQL view.
    It functions perfectly AND I added a "County" filter to the queries.

    Code:
    SELECT tblCARETSData.City, 'LISTINGDATE' AS count_type, Count(*) AS [count]
    FROM tblCARETSData
    WHERE (((tblCARETSData.ListingDate) Between #4/1/2009# And #4/30/2009#))
    GROUP BY tblCARETSData.City, tblCARETSData.County
    HAVING (((tblCARETSData.County)="Orange"))
    
    UNION ALL
    SELECT tblCARETSData.City, 'PENDINGDATE' AS count_type, Count(*) AS [count]
    FROM tblCARETSData
    WHERE (((tblCARETSData.PENDINGDATE) Between #4/1/2009# And #4/30/2009#))
    GROUP BY tblCARETSData.City, tblCARETSData.County
    HAVING (((tblCARETSData.County)="Orange"))
    
    UNION ALL SELECT tblCARETSData.City, 'CLOSINGDATE' AS count_type, Count(*) AS [count]
    FROM tblCARETSData
    WHERE (((tblCARETSData.CLOSINGDATE) Between #4/1/2009# And #4/30/2009#))
    GROUP BY tblCARETSData.City, tblCARETSData.County
    HAVING (((tblCARETSData.County)="Orange"));
    Thanks for your assistance everyone. This forum is great!

Posting Permissions

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