Post reply

UNION query truncates data??

  • March 19, 2007 at 10:28 am

    #171793

    I have four separate queries, that all work on their own. I am then taking the 4 and putting them together in a UNION query as follows:

    SELECT * FROM qry_ActivityReport_Expirations

    UNION

    SELECT * FROM qry_ActivityReport_ProtDev

    UNION

    SELECT * FROM qry_ActivityReport_Submissions

    UNION

    SELECT * FROM qry_ActivityReport_Closures;

    The queries all adhere to UNION rules - same number of columns, same types, same order, but the data from one of the queries gets truncated. Its a memo field and is fine in its own query, but will not all show up in the UNION. It seems the order that the queries are listed doesn't make a difference as I have tried that and still get the same result.

    Is there something else that I am missing??

    Thanks in advance!

  • March 19, 2007 at 11:00 am

    #695771

    UNION removes duplicate rows.  Use UNION ALL if you want the duplicates to remain.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 20, 2007 at 12:38 am

    #695849

    Also of note in an Access union of this nature is that you have to have all the fields in the same order as the field headings is taken from the first query. Can be very risky if you're dealing with numbers!

    I.e. consider the two layouts:

    Name !  Debt ! Credit

    and

    Name !  Credit ! Debt

     

    All your results from the second query will be laid out wrong! And what's more it'll remove the duplicates too, in a bad way.

     

    Anyway, union queries are an excellent tool, but can be quite dangerous if you're messing around with the underlying segments of the query. One option is to put the full SQL syntax into the one union query, that's safer but doesn't make use of the ease of access query writing in the query wizard - not sure if I'm allowed to talk about that on this website

     

     

  • March 22, 2007 at 9:38 am

    #696416

    I'm curious - are saying that the table is truncated (losing rows of data) or that the memo field is truncated (losing the last xx characters in the field)?

    I have found that memo fields are a pain and tend to get truncated all the time to 256 characters... 

     

  • March 23, 2007 at 4:32 am

    #696565

    It's the table that appears to be truncated, in fact it's not, it's just a distinct list.

     

    Memo fields are a different beast altogether, and aren't really much fun within queries...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply