Multi-UNION select

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
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];
 
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
 
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...
 
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];
 
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
 
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...
 
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...
 
Back
Top