Union Query Truncates Results?

[JimS]

I have 6 Excel spreadsheets in exactly the same format representing data from
the years 2002 through 2007. Each year is in its own sheet within one book.
There are about 39,000 total rows, using Excel 2003.

I have mapped those 6 sheets to 6 separate linked tables in Access,
referring to them by their years ("2002", "2003", etc.) In Access, I've
developed a union query that is meant to unify the data into one table:

select "2007" as [Year],* from 2007
union select "2006" as [Year],* from 2006
union select "2005" as [Year],* from 2005
union select "2004" as [Year],* from 2004
union select "2003" as [Year],* from 2003
UNION select "2002" as [Year],* from 2002;

Works like a charm, putting the year into each row as the first column, and
creating a table (I use a subsidiary query to make a table) with 39,000 odd
records.

Trouble is, "2002" should have 4 records reflected (I checked the excel
spreadsheet, and the linked table...), but ends up with only two rows labeled
"2002" in the year column. I can identify that the two rows in the union
query result are identical to the two rows that are missing, in other words,
there are two blue rows in the table and two red rows. The union query gives
me only one red and one blue row.

I cannot find anywhere in this rather simple query this could happen. As you
can see, there is no "where" clause.

What's happening?

 

[Rob Parker]

Hi Jim,

Union by default removes duplicates. If you want to retain duplicate
records, use Union All:
select "2007" as [Year],* from 2007
union All select "2006" as [Year],* from 2006
...

Another thing that Union does is truncate memo fields (or fields in linked
Excel tables with more than 256 characters) to 256 characters.

HTH,

Rob

 

[JimS]

Perfect, thanks for your help!
--
Jim

Hi Jim,

Union by default removes duplicates. If you want to retain duplicate
records, use Union All:
select "2007" as [Year],* from 2007
union All select "2006" as [Year],* from 2006
...

Another thing that Union does is truncate memo fields (or fields in linked
Excel tables with more than 256 characters) to 256 characters.

HTH,

Rob

I have 6 Excel spreadsheets in exactly the same format representing data
from
the years 2002 through 2007. Each year is in its own sheet within one
book.
There are about 39,000 total rows, using Excel 2003.

I have mapped those 6 sheets to 6 separate linked tables in Access,
referring to them by their years ("2002", "2003", etc.) In Access, I've
developed a union query that is meant to unify the data into one table:

select "2007" as [Year],* from 2007
union select "2006" as [Year],* from 2006
union select "2005" as [Year],* from 2005
union select "2004" as [Year],* from 2004
union select "2003" as [Year],* from 2003
UNION select "2002" as [Year],* from 2002;

Works like a charm, putting the year into each row as the first column,
and
creating a table (I use a subsidiary query to make a table) with 39,000
odd
records.

Trouble is, "2002" should have 4 records reflected (I checked the excel
spreadsheet, and the linked table...), but ends up with only two rows
labeled
"2002" in the year column. I can identify that the two rows in the union
query result are identical to the two rows that are missing, in other
words,
there are two blue rows in the table and two red rows. The union query
gives
me only one red and one blue row.

I cannot find anywhere in this rather simple query this could happen. As
you
can see, there is no "where" clause.

What's happening?