Combining tables

We have an application that uses Access databases and archives its data
every year. Then there is the current year's data is an Access database. I
have imported the prior years data into their own table in a SQL database.
I've set up a DTS job to run every day that will import the current Access
database. So, for example, we have tables b2003, b2004 and b2005 in a SQL
database. Now I'm trying to create a report that will be using all these
tables. What I was trying to do was to create a view that included all
these SQL tables. However, I can't think of the SQL code that would join
those tables together. Can anyone help me out?
Thanks.I tried to enter in the follwoing:
SELECT *
FROM b2003
UNION ALL
SELECT *
FROM b2004
UNION ALL
SELECT *
FROM b2005
UNION ALL
But I got this error:
"The Query Designer does not support the UNION SQL construct."
Did I do something wrong?
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23I54A8irFHA.2588@.tk2msftngp13.phx.gbl...
> We have an application that uses Access databases and archives its data
> every year. Then there is the current year's data is an Access database.
> I have imported the prior years data into their own table in a SQL
> database. I've set up a DTS job to run every day that will import the
> current Access database. So, for example, we have tables b2003, b2004 and
> b2005 in a SQL database. Now I'm trying to create a report that will be
> using all these tables. What I was trying to do was to create a view that
> included all these SQL tables. However, I can't think of the SQL code
> that would join those tables together. Can anyone help me out?
> Thanks.
>|||Hello,
You may test the following code in SQL server Query Analyzer:
SELECT * FROM b2003
UNION ALL
SELECT * FROM b2004
UNION ALL
SELECT * FROM b2005
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Joshua,
Was it: "The Query Designer does not ~GRAPHICALLY~ support the UNION SQL
construct"?
If so then it is all right, the designer just won't produce a diagram.
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23b5HtBjrFHA.1172@.TK2MSFTNGP11.phx.gbl...
> I tried to enter in the follwoing:
> SELECT *
> FROM b2003
> UNION ALL
> SELECT *
> FROM b2004
> UNION ALL
> SELECT *
> FROM b2005
> UNION ALL
> But I got this error:
> "The Query Designer does not support the UNION SQL construct."
> Did I do something wrong?
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%23I54A8irFHA.2588@.tk2msftngp13.phx.gbl...
database.
and
that
>