If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Criterion - How to Write Query for Multiple Tables
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Delete Query | Shanin | Running & Setting Up Queries | 14 | July 31st, 2004 07:15 PM |
Display Relational tables in a query | Bill Duris | Running & Setting Up Queries | 1 | July 23rd, 2004 03:37 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |