Re: Criterion - How to Write Query for Multiple Tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 07:42:35 -0500

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" <jcinn@discussions.microsoft.com> wrote in message
news:7B109D0B-8E57-4FA5-A35C-A93708E4C532@microsoft.com...
>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.

Relevant Pages

  • Re: UPDATE - UNION
    ... You can change the query as: ... UNION ALL ... SELECT id,col2 as MyAlias, 'Col2' as TargetCol FROM TABLE1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Query - Crosstab ?
    ... You need the reverse of a crosstab, so perhaps a UNION query. ... SELECT Company, Product1 AS Product, "1" AS Source FROM Table1 ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Synching Multiple Tables
    ... pull all the data from all the tables into one master table. ... Do you really need a master table, or can you simply create a UNION query ... FROM Table1 ...
    (microsoft.public.access.replication)
  • Re: Aggregate calculations on calculated query fields?
    ... "KARL DEWEY" wrote: ... data entry date, display using datasheet form, and then a command button to ... query shown before to do all the calculations. ... UNION SELECT INVENTORY.Date ...
    (microsoft.public.access.queries)