Query input must contain at least one table or query

[ragtopcaddy via AccessMonster.com]

I get the above error msg when running the following union query:

SELECT 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid

UNION SELECT IDXID, Index, sctrid
FROM tblIndices
WHERE IDXID In (SELECT curveid FROM tblCurveDtls)
ORDER BY Index;

The individual queries run fine and return records. As you can plainly see,
there is a table, tblIndices, in the query. Any ideas on what the real
problem is?

Thanks,

Bill R

 

[Duncan Bachen]

I get the above error msg when running the following union query:

SELECT 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid

UNION SELECT IDXID, Index, sctrid
FROM tblIndices
WHERE IDXID In (SELECT curveid FROM tblCurveDtls)
ORDER BY Index;

The individual queries run fine and return records. As you can plainly see,
there is a table, tblIndices, in the query. Any ideas on what the real
problem is?

Thanks,

Bill R

Index is a reserved word in Access and might cause some trouble..
http://support.microsoft.com/kb/q286335/

Nothing else is jumping out at me right now.

 

[ragtopcaddy via AccessMonster.com]

Duncan,

Thanks for your response.

Further searching in this newsgroup revealed the solution.

In a Union query, a FROM statement has to appear in all the SELECT and UNION
SELECT members. I found a suggestion to "spoof" a table in my from statement
in the first line. Any valid tablename will do. That did the trick.

Your point about the Index keyword is well taken. I bracketed it in the first
SELECT, but not in the second. It didn't affect the query, but I will bracket
it as well in the 2nd instance just to be on the safe side,

Bill R

I get the above error msg when running the following union query:

[quoted text clipped - 12 lines]

Bill R

Index is a reserved word in Access and might cause some trouble..
http://support.microsoft.com/kb/q286335/

Nothing else is jumping out at me right now.

 

[Tom Ellison]

Dear Bill:

In Access Jet, the FROM clause must appear in every SELECT statement, not
just for UNION queries.

Your approach of adding a FROM XXX is good, but will be a problem when the
table XXX contains more than 1 row. I recommend:

SELECT TOP 1 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid FROM XXX

Even this would have a problem if XXX has no rows. Be sure to choose a
table that is guaranteed not to be empty.

Consider using UNION ALL if there are no rows that may be duplicates. It
would be faster.

Tom Ellison

Duncan,

Thanks for your response.

Further searching in this newsgroup revealed the solution.

In a Union query, a FROM statement has to appear in all the SELECT and
UNION
SELECT members. I found a suggestion to "spoof" a table in my from
statement
in the first line. Any valid tablename will do. That did the trick.

Your point about the Index keyword is well taken. I bracketed it in the
first
SELECT, but not in the second. It didn't affect the query, but I will
bracket
it as well in the 2nd instance just to be on the safe side,

Bill R

I get the above error msg when running the following union query:

[quoted text clipped - 12 lines]

Bill R

Index is a reserved word in Access and might cause some trouble..
http://support.microsoft.com/kb/q286335/

Nothing else is jumping out at me right now.

 

[ragtopcaddy via AccessMonster.com]

Tom,

Thanks for the suggestion. I will try it out.

BTW: the following SQL, without a FROM clause, will return a record. Try it
out:

SELECT 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid

Also, tblSectors has 5 records, but the result of the union query is all the
records in tblIndices (20), plus the one 0 record added "on the fly" for a
total of 21.

Bill

Dear Bill:

In Access Jet, the FROM clause must appear in every SELECT statement, not
just for UNION queries.

Your approach of adding a FROM XXX is good, but will be a problem when the
table XXX contains more than 1 row. I recommend:

SELECT TOP 1 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid FROM XXX

Even this would have a problem if XXX has no rows. Be sure to choose a
table that is guaranteed not to be empty.

Consider using UNION ALL if there are no rows that may be duplicates. It
would be faster.

Tom Ellison

Duncan,

[quoted text clipped - 26 lines]

 

[ragtopcaddy via AccessMonster.com]

It will run from the DB window with a double-click as well as the SQL view,
but not from design view.

Tom,

Thanks for the suggestion. I will try it out.

BTW: the following SQL, without a FROM clause, will return a record. Try it
out:

SELECT 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid

Also, tblSectors has 5 records, but the result of the union query is all the
records in tblIndices (20), plus the one 0 record added "on the fly" for a
total of 21.

Bill

Dear Bill:

[quoted text clipped - 19 lines]

 

[Tom Ellison]

Dear Bill:

I tried this. Things seem to have changed for earlier versions would be my
guess. But it still doesn't fully function. I don't know if I would trust
it, but if it works well for you, let me know.

Tom Ellison

It will run from the DB window with a double-click as well as the SQL
view,
but not from design view.

Tom,

Thanks for the suggestion. I will try it out.

BTW: the following SQL, without a FROM clause, will return a record. Try
it
out:

SELECT 0 AS Idxid, "No Index" AS [Index], 0 AS sctrid

Also, tblSectors has 5 records, but the result of the union query is all
the
records in tblIndices (20), plus the one 0 record added "on the fly" for a
total of 21.

Bill

Dear Bill:

[quoted text clipped - 19 lines]

Nothing else is jumping out at me right now.