UNION query truncates data??

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  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

• Andrew Collett

  SSC Enthusiast

  Points: 129

  More actions

  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

   

   

• Shirley Ostrander

  SSC Rookie

  Points: 43

  More actions

  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... 

   

• Andrew Collett

  SSC Enthusiast

  Points: 129

  More actions

  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...