Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,
    We have two queries in Access 2007. They both produce the same number of fields and even the same data types. However, when we try to perform a union query on them, we get an error message saying the second query is not recognised as a valid object.

    Union Query
    Code:
    SELECT qryRptClientWAITListE.*
    FROM qryRptClientWAITListE
    UNION SELECT qryRptClientWAITListE-2.*
    FROM qryRptClientWAITListE-2;
    Table Defs:
    qryRptClientWAITListE
    Code:
    Name	                  Text	0
    Case Manager	          Text	0
    Address_1	                  Text	255
    Address_2	                  Text	255
    Town	                          Text	50
    CPhone	                  Text	50
    HACCClient	          Yes/No	1
    EACHClient	          Yes/No	1
    CACPClient	          Yes/No	1
    Private	                  Yes/No	1
    VHC	                          Yes/No	1
    DVANursing	          Yes/No	1
    WaitListed	                  Text	50
    Veteran_File_Number	  Text	        50
    CACCRExpiry	          Date/Time	8
    EACHWL	                  Yes/No	        1
    Date	                          Date/Time	8
    PriorClient	                  Yes/No	        1
    PClient	                  Text	        0
    Notes	                  Memo	        N/A

    qryRptClientWAITListE-2

    Code:
    Name	                  Text	0
    Case Manager	          Text	0
    Address_1	                  Text	100
    Address_2	                  Text	100
    Town	                          Text	25
    CPhone	                  Text	50
    HACCClient	          Yes/No	1
    EACHClient	          Yes/No	1
    CACPClient	          Yes/No	1
    Private	                  Yes/No	1
    VHC	                          Yes/No	1
    DVANursing	          Yes/No	1
    WaitListed	                  Text	50
    Veteran_File_Number	  Text	        50
    CACCRExpiry	          Date/Time	8
    EACHWL	                  Yes/No	        1
    Date	                          Date/Time	8
    PriorClient	                  Yes/No	       1
    PClient	                  Text	       0
    Notes	                  Memo
    Error Message
    The Microsoft Office Access database engine does not recognise 'qryRptClientWaitlistE-2.*' as a valid field name or expression
    Any suggestions would be greatly appreciated.

    Thanks
    toncc

  2. #2
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just a quick reply...

    Have just tried the following and we don't get any errors. But we also do not get any rows from the second query, only those from the first.

    Code:
    SELECT *
    FROM qryRptClientWAITListE
    UNION SELECT *
    FROM qryRptClientWAITListE-2;

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by toncc View Post
    Hello,
    We have two queries in Access 2007. They both produce the same number of fields and even the same data types. However, when we try to perform a union query on them, we get an error message saying the second query is not recognised as a valid object.

    Union Query
    Code:
    SELECT qryRptClientWAITListE.*
    FROM qryRptClientWAITListE
    UNION SELECT qryRptClientWAITListE-2.*
    FROM qryRptClientWAITListE-2;
    Try putting qryRptClientWAITListE-2 between []

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are the tables all native Access tables in a MDB database or an ACCDB database? If so, I would try removing the memo field at the end of each query and see if that makes it work. Memo fields in UNION queries, as well as other queries, can be tricky. On the other hand, if the tables in question are linked from SQL Server or Oracle, there may be other issues at work.
    Wendell

  5. #5
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    G'day,
    Thanks for the tips. we got around this creating a temporary local table and using that as the import. I'll give these suggestions a go when I get the chance as I really would like to get the hang of union queries.

    Cheers!

Posting Permissions

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