SQL Union question

[Chip R.]

Hi,

This isn't really an access question, but as I'm really new to SQL I
don't know where I would post this. Any suggestions on that would
also be helpful. Now on to the question...

I am trying to extract data from a Timberline database through an OBDC
driver to MS Excel. I'm using Microsoft Query and a crude knowledge
of SQL to build my query.

What I'm having trouble doing is getting a list of jobs (or rather,
job numbers) and the appropriate contact for those jobs.

I'd like to get a list of jobs and the primary contact for that job.
That is, if a job has a contact of type A, I want the result set to
show that contact next to the job, if not, I want a contact of type B,
if finally not that, I want a contact of type C.

I was hoping that a union statement could help me with that.
Something along the lines of:

SELECT JOB_LIST.Job, CONTACT_LIST.Na me
FROM JOB_LIST, CONTACT_LIST
WHERE (JOB_LIST.Conta ct_ID = CONTACT_LIST.Co ntact_ID)
AND (CONTACT_LIST.T ype = 'A')
UNION
SELECT JOB_LIST.Job, CONTACT_LIST.Na me
FROM JOB_LIST, CONTACT_LIST
WHERE (JOB_LIST.Conta ct_ID = CONTACT_LIST.Co ntact_ID)
AND (CONTACT_LIST.T ype = 'B')

However, this gets a "non unique table reference" error. The UNION
statements I've seen generally form a union between select statements
from two different databases, not between select statements from the
same database. However, maybe the SQL code here looks good, and the
problem lies within Microsoft query and I should ask this somewhere
else; I really don't know.

Is there a way to do what I want? Does it have nothing to with UNION
statements?

Any help would be greatly appreciated,
Chip

[Please reply to message board, not e-mail]

[Rich P]

First, you need to confirm that you having a valid ODBC connection. I
would create an ODBC link in Access. Matter of fact, I would pull the
data into Excel from the Access link.

Open up an Access mdb, go to the Tables tab and right-click anywhere in
the db window (in the Tables tab) that is clear (anywhere on the white
background). On the context menu click on "Link Tables". In the dialog
box that appears, go to the lower left corner of the Dialog window to
the
"Files of Type:" dropdown box. Scroll down to the bottom of that list
and select "ODBC Databases". Then, in the "Select Data Source" dialog
window that appears, go to the "Machine Data Source" Tab. Select the
DSN to your Timberline Database. Note: if you don't see the DSN or
know which DSN it is, then that is your problem. But if you do know the
DSN, then select it and click OK. If this DSN has a password, make sure
to click on the "remember password" checkbox in the Dialog window (lower
right corner) and then click OK. Select the index column in the next
window and click OK (it doesn't really matter which column you select if
you don't know which one is the index column, usually a column on the
left side).

Now you have a definite connection to your external data table. Open
the table to make sure you can see data. If you don't need to automate
the process you can create a query in Access and query for the fields
you need. Highlight the rows from the query and copy and paste them
into Excel.

As for the queries, instead of a Union query, just use the "OR"
operator.

SELECT JOB_LIST.Job, CONTACT_LIST.Na me
FROM JOB_LIST, CONTACT_LIST
WHERE (JOB_LIST.Conta ct_ID = CONTACT_LIST.Co ntact_ID)
AND (CONTACT_LIST.T ype = "A" Or CONTACT_LIST.Ty pe = "B")

In the Access query window, you don't write any Sql statements. Just
select fields and enter criteria in the criteria fields.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Chip R.]

Rich,

Thank you for your help.

However, I did as you suggested and imported the data from Timberline
into Access. I was fairly sure that I had a valid OBDC connection,
and I did.

But I still don't have the query correct. Sorry that I didn't explain
myself better, but I did not want the fields if either the type of the
contact was 'A' or 'B'. I wanted the fields corresponding to 'A' and
only 'A', even if a 'B' existed, and if 'A' did not exist, then I
wanted 'B' and only 'B'. An example might show this better.

Given the data:

Job Name Type
3000 Chris A
3001 Doug B
3002 Esteban C
3003 Frank A
3003 Gerald B
3004 Hank B
3004 Igor C
Your query would result in:

If (A or B)...

3000 Chris A
3001 Doug B
3003 Frank A
3003 Gerald B
3004 Hank B

However, I only want:

If A... Else If B...

3000 Chris A
3001 Doug B
3003 Frank A
3004 Hank B

That is, despite having two people for job 3003, I only want type A,
despite there also being a type B. A UNION statement seemed like it
would do the job, because it would do the query first for the 'A's,
then for the 'B's, and I could use a distinct statement or something
to eliminate the 'B's that already had 'A's. However, I still
receieve the "non distinct table reference" error.

Anyway, thanks for your help.

-Chip

[Rich P]

OK. try this in the Access Query builder - just copy and paste it
straight in the sql window - Note: to get to the sql window go to the
Query design view, go to the View menu, 2nd menu from the top <b>SQL</b>
Sql view. This puts you in the Sql window. Just overwrite whatever
text is in that window with the text below. To get back to design view
go back to the View menu and click on the first selection Design View.

SELECT * FROM Table1 WHERE Table1.type = (SELECT MIN(type) FROM Table1
T2 " WHERE T2.Job = Table1.Job) AND Table1.type in ("a","b")

Or

SELECT job, name, type FROM Table1 WHERE table1.type = (SELECT MIN(type)
FROM Table1 T2 WHERE T2.Job = Table1.Job) AND Table1.type in ("a","b")
Here I'm using a subquery and a few agregate functions that come with
sql. The first query will list all the columns in your table that meet
your desired criteria. But inside the query builder you will only see
one column for Type with the subquery listed in the criteria section of
that column. In the 2nd query you get 3 columns with the subquery
listed in the criteria section of the Type column.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!