Access 97 Help with SQL Specific Query Please 3

[spoon1]

Hi, I am not a programmer so thank you for any help you can give me. In my project I have 9 tables all containing 11 common (same) fields. The only reason they are seperate tables is because these people are in different departments and they get added/deleted/modified by clicking on a command button on the switchboard to open their department form. Also, a report can be generated for each department seperately.
*************
HERE IS MY PROBLEM:
*************
I need to be able to view all of the tables together. SINCE all the fields are the same I didn't think it would be such a huge problem. I have created this query titled "Query_tech" to view it. I have created a similar query for each table in my project:
**************
SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Tech Personnel];
**************

This works great for each seperate table, but I can't join the other to view as "ALL PERSONNEL"

I have tried joining all of the individual queries by seperating by the word UNION -like this:

**********************
Query_tech UNION Query_mechs UNION Query_admin UNION Query_parts
**********************
but it did'nt work.

CAN SOMEONE PLEASE HELP ME JOIN ALL OF THE TABLES SO I CAN VIEW AS A WHOLE INSTEAD OF SEPERATELY? AFTER I DO THIS, I CAN GENERATE A REPORT FOR ALL PERSONNEL.

Thank you so much for your help, I have spent many hours working on this problem.

 

[dodge20]

If you are going to use the UNION, you have to use it with your actual sql statements, not the query title.

SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Tech Personnel]
UNION
SELECT ......
UNION
SELECT......

Dodge20

 

[scriverb]

You were on the right track with your UNION query but your syntax was in error.

UNION QUERY:
Select * FROM tblDeptA Order by Field1, Field2
UNION
Select * FROM tblDeptB
UNION
Select * FROM tblDeptA
. . .

I think you get the idea. I am assuming that the DB that you will be running this has LINKED table capability to all of the databases tables for each of these departments.

You can start with the SQL from the first query and paste that into the UNION query leaving off the semi-color on the end. Join each with a UNION and you do not have include the Order by of the subsequent queries.

Let me know if I can help any further with this.



Bob Scriver

 

[spoon1]

Thank you Dodge20 and Bob for your timely response. Dodge, I don't understand your reply- what do you mean by actual sql statements?
Bob, in reference to the syntax you wrote; Is the asterisk supposed to be there, or are you using it as a marker for something I should be putting there? Also, should I use parenthesis at all, like I used in my select statement that I used for my query? Thank you again for any help you can give me.

 

[scriverb]

"Select *" just gives you all the fields in the table. The * is just a wildcard for all fields. You can drop that and just pick the fields that you want in place of it. I just used it as a shorthand while demonstrating the UNION query syntax.



Bob Scriver

 

[lespaul]

Dogde was referring to your code below, where you are trying to UNION by query name:

Query_tech UNION Query_mechs UNION Query_admin UNION Query_parts

Bob has it when he writes one query with multiple selects. Your final query should look like:

SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Tech Personnel]
UNION
SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Mech Personnel]
UNION
SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Admin Personnel]
UNION
SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Parts Personnel]



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[spoon1]

OMG!! I SEE!!! This site is great! I really appreciate the help I received from all of you. You saved my project, I was ready to start all over and decided to surf the net for some help, after stumbling onto tek-tips, I'm glad I did, and I'll frequent it often. I know it takes you guys some personal time to answer these questions- many thanks again!!

 

[scriverb]

Leslie, thanks for giving spoon1 the details for his query. Also, I love the anecdote after your name. Only from a programmer.

Bob Scriver

 

[spoon1]

One more thing... How do I sort all of the joined tables alphabetically? Thank You.

 

[scriverb]

In the first select of the UNION query you add the Order By clause:
SELECT [Last Name], [First Name], [Middle Init], [SPT], [SSN], [COB], [POB1], [POB2], [PAD], [Category], [Comments]
FROM [Tech Personnel]
ORDER BY [Last Name], [First Name]
UNION

The rest of the Selects will follow this lead.


Bob Scriver

 

[lespaul]

Glad you like it! It's on a tshirt from

http://www.thinkgeek.com

Here's one I found here a few days ago and am thinking about changing to:

No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced

Spoon, glad you got the OMG light to come on, isn't that the best feeling??? As far as tek-tips goes, this is the best technical forum I've found on the net. All the people are friendly and helpful (some go FAR BEYOND anything you would expect).

Glad you joined us, come back anytime!!

Leslie