UNION....ish 1

[Fherrera]

Hi, I have all my data in 3 databases each with one table (all of them are defined the same)

Pretty much just, ID, TAG, DATE, VALUE

Each database has a unique autonumbered ID...

Anyway, to run a query for all of them I have been using the UNION operator.

Basically,

Select * from T1 where something
UNION
Select * from T2 where something
UNION
Select * from T3 where something

Now, this is great except sometimes there are duplicate from T1 and T2 or T3, ie. they are unique within each table but once you union them together there are duplicates.

Is it possible to use the large unioned select statement aboce as a subselect? I've looked into this, but this requires me to choose a table..but in this case there are 3 tables altogether (so which one would I choose if I still want data from all of them, just unique to ALL of them not just its individual table).

So, something liek this :

Select * From T?? Where something IN
(
Select * from T1 where something
UNION
Select * from T2 where something
UNION
Select * from T3 where something
)

This requires me to choose one table... Is there someway of doing this? Or maybe create temp table of the subselect statement then query that?

I'm using Access 1997...any help would be great.

Thanks

Frank

 

[lespaul]

If you want to use your union query as the source for another query, you can save the query:

qryAllThreeTables

and then you can use that:

SELECT * FROM qryAllThreeTables

what is your issue with duplicates? You don't want the duplicates to show in your resulting query or you do want the duplicates to show?

HTH

Leslie

 

[Fherrera]

Duplicates would be one instance, the other is when Counting the numbers. I was hoping that for example if Table 2 produced a count of 20 for TAG1 and Table 3 produced count of 10 for TAG1, when I union them it will be:

TAG1 20
TAG1 10

And I hoping that using this other method I could make it give me

TAG1 30 instead.. which is a "total" count of sorts.

I was hoping to be able to do it all in a single sql query, as the reason I have 3 databases is because there is so much raw data i'm trying to process.

Anyhow, i'll try this query method thanks.

 

[lespaul]

In that case something like this should work:

Select S.FieldName1, Sum(S.FieldName2) As SumOf2Fields From 
(
Select * from T1 where something
UNION
Select * from T2 where something
UNION
Select * from T3 where something
) As S
Group By S.FieldName1

Leslie

 

[Fherrera]

That's exactly what I was looking for...anywhere I saw the sub selects they where all after a from and WHERE clause.

Thanks

F

 

[Fherrera]

Hrm, I finally got to try it and I got a syntax error... This was from the query design view->sql mode in Access 97:

SELECT T.TAG, T.VALUE, COUNT(T.VALUE) As TotalNumValues
FROM 
(
SELECT TAG, VALUE, COUNT(VALUE)
FROM Combination1
WHERE VALUE > 1
GROUP BY TAG, VALUE
ORDER BY TAG, COUNT(VALUE) DESC
UNION
SELECT TAG, VALUE, COUNT(VALUE)
FROM Combination1
WHERE VALUE > 2
GROUP BY TAG, VALUE
ORDER BY TAG, COUNT(VALUE) DESC
UNION
SELECT TAG, VALUE, COUNT(VALUE)
FROM Combination3
WHERE VALUE > 1
GROUP BY TAG, VALUE
ORDER BY TAG, COUNT(VALUE) DESC
) AS T
GROUP BY T.TAG, T.VALUE
ORDER BY T.TAG, COUNT(T.VALUE) DESC;

It makes sense to me?

Is this feature of using a select statement to be the FROM table not valid in Access 97?

Thx

F

 

[lespaul]

SELECT T.TAG, T.VALUE, COUNT(T.VALUE) As TotalNumValues
FROM
(
SELECT TAG, VALUE, COUNT(VALUE)
FROM Combination1
WHERE VALUE > 1
GROUP BY TAG, VALUE
UNION
SELECT TAG, VALUE, COUNT(VALUE)
FROM Combination1
WHERE VALUE > 2
GROUP BY TAG, VALUE
UNION
SELECT TAG, VALUE, COUNT(VALUE)
FROM Combination3
WHERE VALUE > 1
GROUP BY TAG, VALUE
) AS T
GROUP BY T.TAG, T.VALUE
ORDER BY T.TAG, COUNT(T.VALUE) DESC;

try that. I removed the order by clauses in the UNION query. When you UNION you only have to have the order by the last one, but since you are really only using this as a source for a different query, you don't need them at all.



Leslie

 

[Fherrera]

Oh so if I order the last select of the union it orders all of them? Good to know.

I still get the syntax error in the FROM clause. Can I do this in Access 97? If not, no worries I managed to do what I wanted with your first answer. But still..kinda weird?

Thanks

Frank

 

[lespaul]

If you run the union query alone, do you get the syntax error as well or only when you run the whole thing?

leslie

 

[PHV]

As explained earler, save the union query ans use this queryname in the from clause of your outer query.
You may consider giving a name (alias) to the COUNT(VALUE) column of the first select in the union query, and the SUM this column in the outer query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244