I really need help on this one. I am a newbie to writing queries, even
though I understand the logic. The mechanics is where I get lost. Also, I
cannot tell from reading a lot of the posts what needs to be done in SQL, and
what can be done with the query wizard, or in Design View. For those
reasons, please excuse me if I am asking a redundant question posted
elsewhere.

MY PROBLEM
I have multiple tables. Most are imported from Excel and converted to
Access tables. (Took me a while but I figured out how to do that.) The
tables contain a lot of the same information, but with different field
headings. I know how to work around that. I want to extract only the fields
containing current information from each table to create a new table with all
current information.

I read somewhere in the posts that this can be accomplished by creating a
union between the tables, but then was dismayed after reading further that it
would have to be written in SQL---I do not know how to do that. Is there a
simpler way to write a query in Design View to accomplish this task?

EXAMPLE

Table One Table Two Table Three Table Four

Co. Name Co. Name Co. Name Co. Name
Address Address Address Address
Website Fax Number E-mail E-mail
Sales Exec Sales Exec Product Product

*Table One is current for the company name, and address
*Table Two is current for the fax number
*Table Three is current for e-mail and product
*Table Four is current for address, email, product

I want to create a query in Design view that results in a dynaset showing
all the above fields, but only using the fields from tables (that I select in
Design view) that contain the current information. The reason there are so
many tables containing the same fields is because separate individuals
created them to use for different purposes. The manager of the department
wants me to use Access (have taught myself) to create one table/dynaset
without altering the original tables--which is why I thought it best to
import the information rather than link it.

Am I going about this all wrong, or am I on the right track? BTW, I am
comletely lost using the expression builder.... When Microsoft updates
Access the next time, I sure hope a decision is made to make that tool easier
to use for novices like myself.

Thank you.

Hi,



Indeed, that is a UNION query. Just imagine a tableau:


Table1: CoName, Address, WebSite, SalesExec, NULL, NULL, NULL
Table2: CoName, Address, NULL, SalesExec, Fax, NULL, NULL
Table3: CoName, Address, NULL, NULL, NULL, Email, Product
Table4: CoName, Address, NULL, NULL, NULL, Email, Product


Note that I added a NULL to "fill the holes".

The union query is thus:
--------------------------------
SELECT CoName, Address, WebSite, SalesExec, NULL, NULL, NULL
FROM table1

UNION ALL

SELECT CoName, Address, NULL, SalesExec, Fax, NULL, NULL
FROM table2

UNION ALL

SELECT CoName, Address, NULL, NULL, NULL, Email, Product
FROM table3

UNION ALL

SELECT CoName, Address, NULL, NULL, NULL, Email, Product
FROM table4
-----------------------------


The only missing stuff is about to supply a name, for each fields, in the
FIRST select:

----------------------------
SELECT CoName, Address, WebSite, SalesExec, NULL As Fax, NULL As Email,
NULL AS Product
FROM table1

UNION ALL

....

--------------------------


and that's about it.



Hoping it may help,
Vanderghast, Access MVP


"jcinn" <(E-Mail Removed)> wrote in message
news:7B109D0B-8E57-4FA5-A35C-(E-Mail Removed)...
>I really need help on this one. I am a newbie to writing queries, even
> though I understand the logic. The mechanics is where I get lost. Also,
> I
> cannot tell from reading a lot of the posts what needs to be done in SQL,
> and
> what can be done with the query wizard, or in Design View. For those
> reasons, please excuse me if I am asking a redundant question posted
> elsewhere.
>
> MY PROBLEM
> I have multiple tables. Most are imported from Excel and converted to
> Access tables. (Took me a while but I figured out how to do that.) The
> tables contain a lot of the same information, but with different field
> headings. I know how to work around that. I want to extract only the
> fields
> containing current information from each table to create a new table with
> all
> current information.
>
> I read somewhere in the posts that this can be accomplished by creating a
> union between the tables, but then was dismayed after reading further that
> it
> would have to be written in SQL---I do not know how to do that. Is there
> a
> simpler way to write a query in Design View to accomplish this task?
>
> EXAMPLE
>
> Table One Table Two Table Three Table Four
>
> Co. Name Co. Name Co. Name Co. Name
> Address Address Address Address
> Website Fax Number E-mail E-mail
> Sales Exec Sales Exec Product Product
>
> *Table One is current for the company name, and address
> *Table Two is current for the fax number
> *Table Three is current for e-mail and product
> *Table Four is current for address, email, product
>
> I want to create a query in Design view that results in a dynaset showing
> all the above fields, but only using the fields from tables (that I select
> in
> Design view) that contain the current information. The reason there are
> so
> many tables containing the same fields is because separate individuals
> created them to use for different purposes. The manager of the department
> wants me to use Access (have taught myself) to create one table/dynaset
> without altering the original tables--which is why I thought it best to
> import the information rather than link it.
>
> Am I going about this all wrong, or am I on the right track? BTW, I am
> comletely lost using the expression builder.... When Microsoft updates
> Access the next time, I sure hope a decision is made to make that tool
> easier
> to use for novices like myself.
>
> Thank you.