Multi-UNION select

B

Bill Stanton

In the following UNION query, the 2nd of three selects
does not seem to take effect. I.e., nothing is returned from
the select from table "DonRegInd". However, if I isolate
the second select and run it be itself, the appropriate
record(s) are returned.

I can't tell from the HELP text if I need a different syntax
when I have more than two tables in a UNION operation.

Thanks,
Bill

SELECT [DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];
 
L

LeAnne

Sometimes squirrely things happen with the ALL predicate. It's really
necessary only if you want the query to return all records, including
duplicates. To return unique records, use UNION SELECT.

hth,

LeAnne
 
B

Bill Stanton

LeAnne,
I need the ALL predicate. Would things become "un-squirreled"
if I created 3 separate queries and "UNION'd" them?
Bill


LeAnne said:
Sometimes squirrely things happen with the ALL predicate. It's really
necessary only if you want the query to return all records, including
duplicates. To return unique records, use UNION SELECT.

hth,

LeAnne

Bill said:
In the following UNION query, the 2nd of three selects
does not seem to take effect. I.e., nothing is returned from
the select from table "DonRegInd". However, if I isolate
the second select and run it be itself, the appropriate
record(s) are returned.

I can't tell from the HELP text if I need a different syntax
when I have more than two tables in a UNION operation.

Thanks,
Bill

SELECT [DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];
Click to expand...
Click to expand...
 
D

Dale Fye

How can you tell? Based on your code, there is no way to tell where
the results came from.

Try it like the following to see exactly where your results are coming
from.

SELECT 'RegFam' as Source,
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT 'RegInd as Source,
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT 'UnReg' as Source,
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];


--
HTH

Dale Fye


In the following UNION query, the 2nd of three selects
does not seem to take effect. I.e., nothing is returned from
the select from table "DonRegInd". However, if I isolate
the second select and run it be itself, the appropriate
record(s) are returned.

I can't tell from the HELP text if I need a different syntax
when I have more than two tables in a UNION operation.

Thanks,
Bill

SELECT [DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];
 
B

Bill Stanton

Hi Dale,
I knew only because in the form that uses the query that the
"total" amount was shy by what I knew to be included from
amounts in the 2nd of the three tables.

Anyway, having added the "as Source" to each of the selects,
the results are complete. However, I'm weary as to why my
original query didn't work... doesn't the "FROM [tablename]"
define which table the records are to be selected from?

Bill
 
B

Bill Stanton

I understand what you're saying, and I will go back and
re-run the query without the "As Source". However,
before I followed your suggestion, the results of the
original query was off in the Sum(Amount) by exactly
the amount that would have otherwise come from the
ONLY record in the 2nd table... just happens that at
the moment there's only one record in that table.

I'll post back this evening with the results... got to go now.

Thanks,
Bill


Dale Fye said:
Yes, the From [tablename] defines which table the results are supposed
to come from, but if you don't include some method to show you which
file your results came from, there is no way to tell which part of the
union is returning results.

If all you did was add the Source stuff I added, then your query was
probably running correctly in the first place.

--
HTH

Dale Fye


Hi Dale,
I knew only because in the form that uses the query that the
"total" amount was shy by what I knew to be included from
amounts in the 2nd of the three tables.

Anyway, having added the "as Source" to each of the selects,
the results are complete. However, I'm weary as to why my
original query didn't work... doesn't the "FROM [tablename]"
define which table the records are to be selected from?

Bill


Dale Fye said:
How can you tell? Based on your code, there is no way to tell where
the results came from.

Try it like the following to see exactly where your results are coming
from.

SELECT 'RegFam' as Source,
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT 'RegInd as Source,
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT 'UnReg' as Source,
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];


--
HTH

Dale Fye


In the following UNION query, the 2nd of three selects
does not seem to take effect. I.e., nothing is returned from
the select from table "DonRegInd". However, if I isolate
the second select and run it be itself, the appropriate
record(s) are returned.

I can't tell from the HELP text if I need a different syntax
when I have more than two tables in a UNION operation.

Thanks,
Bill

SELECT [DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];
Click to expand...
Click to expand...
 
B

Bill Stanton

Right you are. Total "cockpit error"... right query, wrong form
RecordSource.
Thanks,
Bill


Dale Fye said:
Yes, the From [tablename] defines which table the results are supposed
to come from, but if you don't include some method to show you which
file your results came from, there is no way to tell which part of the
union is returning results.

If all you did was add the Source stuff I added, then your query was
probably running correctly in the first place.

--
HTH

Dale Fye


Hi Dale,
I knew only because in the form that uses the query that the
"total" amount was shy by what I knew to be included from
amounts in the 2nd of the three tables.

Anyway, having added the "as Source" to each of the selects,
the results are complete. However, I'm weary as to why my
original query didn't work... doesn't the "FROM [tablename]"
define which table the records are to be selected from?

Bill


Dale Fye said:
How can you tell? Based on your code, there is no way to tell where
the results came from.

Try it like the following to see exactly where your results are coming
from.

SELECT 'RegFam' as Source,
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT 'RegInd as Source,
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT 'UnReg' as Source,
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];


--
HTH

Dale Fye


In the following UNION query, the 2nd of three selects
does not seem to take effect. I.e., nothing is returned from
the select from table "DonRegInd". However, if I isolate
the second select and run it be itself, the appropriate
record(s) are returned.

I can't tell from the HELP text if I need a different syntax
when I have more than two tables in a UNION operation.

Thanks,
Bill

SELECT [DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL
SELECT [DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Special syntax for ORDER BY ????? 6
Union Select Failing 2
subform total 5
Problem joining queries 5
Requery "Too Soon"? 34
Not returning correct 6
Union Query Report 0
SUM in a UNION query 2

Top