Union Query Not work

G

Guest

Dear All,

I have different 4 queries that I combine them together using union query :
Those 4 queries [A], , [C] and [D] have column as follows: Name,
ID,Date, TotalAmount, TotalError.
The Union Query that I put down as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [D];

When I run this Union Query, I got an error message as follows:
The specified field ‘[ID]’ could refer to more than one table listed in the
FROM clause of your SQL statement. (Error 3079)
Would be appreciate if some one could help me as I try so many times it does
not work?
Thanks & Best Regards
PA

Note: If I just run up to query [C],it does work well as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
 
A

Allen Browne

Perhaps D is a query that contains more than one table.

Try completing the SQL statement by including the table name, e.g.:
...
UNION
SELECT [D].[Name], [D].[ID], [D].[Date], [D].[TotalAmount],
[D].[TotalError] FROM [D];

You might also find UNION ALL faster than UNION, unless you want Access to
de-duplicate the records.

Another way to solve the problem would be to create just one table instead
of A,B,C, and D. Use an extra field to distinguish whatever the difference
is.

If you really have tables called Name and Date, they are likely to be
misunderstood by Access.
 
G

Guest

Dear Allen

It does work as follows:

UNION
SELECT [Name]. [D], [ID].[D], [Date].[D], [TotalAmount].[D],
[TotalError].[D] FROM [D];


But when I run this query, it asked additional parameter to be completed
such as [ID],[ Date],[TotalAmount],[TotalError] etc ..Is it possible for me
to eliminate this “new parameter question�

Many thanks for your help,

PA


Allen Browne said:
Perhaps D is a query that contains more than one table.

Try completing the SQL statement by including the table name, e.g.:
...
UNION
SELECT [D].[Name], [D].[ID], [D].[Date], [D].[TotalAmount],
[D].[TotalError] FROM [D];

You might also find UNION ALL faster than UNION, unless you want Access to
de-duplicate the records.

Another way to solve the problem would be to create just one table instead
of A,B,C, and D. Use an extra field to distinguish whatever the difference
is.

If you really have tables called Name and Date, they are likely to be
misunderstood by Access.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PA said:
Dear All,

I have different 4 queries that I combine them together using union query
:
Those 4 queries [A], , [C] and [D] have column as follows: Name,
ID,Date, TotalAmount, TotalError.
The Union Query that I put down as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [D];

When I run this Union Query, I got an error message as follows:
The specified field '[ID]' could refer to more than one table listed in
the
FROM clause of your SQL statement. (Error 3079)
Would be appreciate if some one could help me as I try so many times it
does
not work?
Thanks & Best Regards
PA

Note: If I just run up to query [C],it does work well as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
Click to expand...
Click to expand...
 
A

Allen Browne

The table name (D in your example) must come before the field name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PA said:
Dear Allen

It does work as follows:

UNION
SELECT [Name]. [D], [ID].[D], [Date].[D], [TotalAmount].[D],
[TotalError].[D] FROM [D];


But when I run this query, it asked additional parameter to be completed
such as [ID],[ Date],[TotalAmount],[TotalError] etc ..Is it possible for
me
to eliminate this "new parameter question"?

Many thanks for your help,

PA


Allen Browne said:
Perhaps D is a query that contains more than one table.

Try completing the SQL statement by including the table name, e.g.:
...
UNION
SELECT [D].[Name], [D].[ID], [D].[Date], [D].[TotalAmount],
[D].[TotalError] FROM [D];

You might also find UNION ALL faster than UNION, unless you want Access
to
de-duplicate the records.

Another way to solve the problem would be to create just one table
instead
of A,B,C, and D. Use an extra field to distinguish whatever the
difference
is.

If you really have tables called Name and Date, they are likely to be
misunderstood by Access.

PA said:
Dear All,

I have different 4 queries that I combine them together using union
query
:
Those 4 queries [A], , [C] and [D] have column as follows: Name,
ID,Date, TotalAmount, TotalError.
The Union Query that I put down as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [D];

When I run this Union Query, I got an error message as follows:
The specified field '[ID]' could refer to more than one table listed in
the
FROM clause of your SQL statement. (Error 3079)
Would be appreciate if some one could help me as I try so many times it
does
not work?
Thanks & Best Regards
PA

Note: If I just run up to query [C],it does work well as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, Allen

It does work very well

Many thanks for your advise

PA

Allen Browne said:
The table name (D in your example) must come before the field name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PA said:
Dear Allen

It does work as follows:

UNION
SELECT [Name]. [D], [ID].[D], [Date].[D], [TotalAmount].[D],
[TotalError].[D] FROM [D];


But when I run this query, it asked additional parameter to be completed
such as [ID],[ Date],[TotalAmount],[TotalError] etc ..Is it possible for
me
to eliminate this "new parameter question"?

Many thanks for your help,

PA


Allen Browne said:
Perhaps D is a query that contains more than one table.

Try completing the SQL statement by including the table name, e.g.:
...
UNION
SELECT [D].[Name], [D].[ID], [D].[Date], [D].[TotalAmount],
[D].[TotalError] FROM [D];

You might also find UNION ALL faster than UNION, unless you want Access
to
de-duplicate the records.

Another way to solve the problem would be to create just one table
instead
of A,B,C, and D. Use an extra field to distinguish whatever the
difference
is.

If you really have tables called Name and Date, they are likely to be
misunderstood by Access.

Dear All,

I have different 4 queries that I combine them together using union
query
:
Those 4 queries [A], , [C] and [D] have column as follows: Name,
ID,Date, TotalAmount, TotalError.
The Union Query that I put down as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [D];

When I run this Union Query, I got an error message as follows:
The specified field '[ID]' could refer to more than one table listed in
the
FROM clause of your SQL statement. (Error 3079)
Would be appreciate if some one could help me as I try so many times it
does
not work?
Thanks & Best Regards
PA

Note: If I just run up to query [C],it does work well as follows:
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [A]
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM
UNION
SELECT[Name],[ID],[Date],[TotalAmount],[TotalError] FROM [C]
Click to expand...
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

Union Query of Two Queries (Part 2) 2
Union Query Asking for Parameter Value 1
Union Query asking for Parameter Value 7
Union query producing duplicate records 7
union query problem 16
Date Limit in Crosstab from Union query 2
Union query not displaying select statement 1
BUILDING UNION QUERY 9

Top