Join a table, queryfails with "Query input must contain at leastone table or query

P

Phil Smith

I don.t get it. I have a query which returns data no problem.

SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type]
FROM MasterList_Step3
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));

If I join a table to it, it fails with the above error.


SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type],
Brand_DivisionSelector.Division
FROM Brand_DivisionSelector RIGHT JOIN MasterList_Step3 ON
(Brand_DivisionSelector.Brand = MasterList_Step3.Brand) AND
(Brand_DivisionSelector.Division = MasterList_Step3.Division)
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));

If I join a table to it, it fails with the above error.

The table I am joining has a single record. There is matching data in a
one to many relationship between the two fields in branddivisionselector
and masterlist)step3. However, this is a right join, which shoudl ot
prevent data from being displayed.

This is not making any sense to me. I am sure the error is bogus, but
have no clue where to go from here.

Any Ideas?
 
J

John Spencer

I see nothing wrong with the SQL statement. Try using Brackets around
Division and see if that helps.

SELECT MasterList_Step3.Brand
, MasterList_Step3.[Item Type]
, Brand_DivisionSelector.[Division]
FROM Brand_DivisionSelector RIGHT JOIN MasterList_Step3 ON
Brand_DivisionSelector.Brand = MasterList_Step3.Brand AND
Brand_DivisionSelector.[Division] = MasterList_Step3.[Division]
WHERE MasterList_Step3.[Item Type]<>"Irregular"
And MasterList_Step3.[Item Type]<>"Catalog"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
P

Phil Smith

No joy. Same error. It is a very strange error to get...



John said:
I see nothing wrong with the SQL statement. Try using Brackets around
Division and see if that helps.

SELECT MasterList_Step3.Brand
, MasterList_Step3.[Item Type]
, Brand_DivisionSelector.[Division]
FROM Brand_DivisionSelector RIGHT JOIN MasterList_Step3 ON
Brand_DivisionSelector.Brand = MasterList_Step3.Brand AND
Brand_DivisionSelector.[Division] = MasterList_Step3.[Division]
WHERE MasterList_Step3.[Item Type]<>"Irregular"
And MasterList_Step3.[Item Type]<>"Catalog"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Phil said:
I don.t get it. I have a query which returns data no problem.

SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type]
FROM MasterList_Step3
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));

If I join a table to it, it fails with the above error.


SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type],
Brand_DivisionSelector.Division
FROM Brand_DivisionSelector RIGHT JOIN MasterList_Step3 ON
(Brand_DivisionSelector.Brand = MasterList_Step3.Brand) AND
(Brand_DivisionSelector.Division = MasterList_Step3.Division)
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));

If I join a table to it, it fails with the above error.

The table I am joining has a single record. There is matching data in
a one to many relationship between the two fields in
branddivisionselector and masterlist)step3. However, this is a right
join, which shoudl ot prevent data from being displayed.

This is not making any sense to me. I am sure the error is bogus, but
have no clue where to go from here.

Any Ideas?
Click to expand...
Click to expand...
 
B

Bob Barrows

Phil said:
I don.t get it. I have a query which returns data no problem.

SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type]
FROM MasterList_Step3
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));
Click to expand...

"MasterList_Step3" is the name of a table? Does this name imply that you
have multiple "MasterList" tables that are differentiated only by a
"Step" number? If so, this would not be a very good design: the step
number should be stored as data, not metadata. OTOH, if
"MasterList_Step3" is the name of a query that only select records from
a table called "MasterList" where the step number is 3, then you have
done a good thing.

If I join a table to it, it fails with the above error.


SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type],
Brand_DivisionSelector.Division
FROM Brand_DivisionSelector RIGHT JOIN MasterList_Step3 ON
(Brand_DivisionSelector.Brand = MasterList_Step3.Brand) AND
(Brand_DivisionSelector.Division = MasterList_Step3.Division)
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));
Click to expand...

So "Brand_DivisionSelector" is a table? You can successfully create the
following query?

SELECT Brand_DivisionSelector.Division
FROM Brand_DivisionSelector
 
P

Phil Smith

No, Masterlist_Step3 is a query.

So "Brand_DivisionSelector" is a table?

Yes, and I did get it to work. I am not exactly sure how, but it is
working now.

Thanx

Bob said:
Phil said:
I don.t get it. I have a query which returns data no problem.

SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type]
FROM MasterList_Step3
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));
Click to expand...


"MasterList_Step3" is the name of a table? Does this name imply that you
have multiple "MasterList" tables that are differentiated only by a
"Step" number? If so, this would not be a very good design: the step
number should be stored as data, not metadata. OTOH, if
"MasterList_Step3" is the name of a query that only select records from
a table called "MasterList" where the step number is 3, then you have
done a good thing.


If I join a table to it, it fails with the above error.


SELECT MasterList_Step3.Brand, MasterList_Step3.[Item Type],
Brand_DivisionSelector.Division
FROM Brand_DivisionSelector RIGHT JOIN MasterList_Step3 ON
(Brand_DivisionSelector.Brand = MasterList_Step3.Brand) AND
(Brand_DivisionSelector.Division = MasterList_Step3.Division)
WHERE (((MasterList_Step3.[Item Type])<>"Irregular" And
(MasterList_Step3.[Item Type])<>"Catalog"));
Click to expand...


So "Brand_DivisionSelector" is a table? You can successfully create the
following query?

SELECT Brand_DivisionSelector.Division
FROM Brand_DivisionSelector
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

perform INNER JOIN on query results 2
Sum means empty query? 5
can't save modified query 2
Query - Table 3
Join on a UNION query 2
SQL PROGRAMMING - LEFT OUTER JOIN 1
Query gives me a warning when I ru it... 1
Two queueries - same table - different totals 1

Top