The column prefix 'MS1' does not match with a table or alias name used in the query. (#107)

[blue875]

A tale of two queries:
One query looks like this, and runs fine. We'll call this the
"Customer1 query":

SELECT Customer1 overall.*,
IIf(IsNull([Customer1 Field].[Field Volume]),0,1) AS IsField,
IIf(IsNull([Customer1 Staff].[Staff Volume]),0,1) AS IsStaff
FROM ([Customer1 overall] LEFT JOIN [Customer1 Field]
ON [Customer1 overall].[Volume]=[Customer1 Field].[Field Volume])
LEFT JOIN [Customer1 Staff]
ON [Customer1 overall].[Volume]=[Customer1 Staff].[Staff Volume];

Customer1 Field, Customer1 Staff, and Customer1 Overall are each
queries with certain where clauses. These three queries are pulling
data from linked SQL Server tables.

Now, using the same tables, but changing a few of the where clauses, I
set up another query, which we'll call the "Customer2 query". With the
table/relationship structure being exactly the same, but pulling
different records from the same tables, I get an error:

[Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1'
does not match with a table or alias name used in the query. (#107)

Repeat that error a dozen or so times, occasionally incrementing to
'MS2', 'MS3', and so forth.

Customer2 Overall, Customer2 Field, and Customer2 Staff queries each
individually run fine. Put them together, and Access goes nuclear.
Also, let's say I remove Customer2 Field from the query, leaving only
one left outer join. Still doesn't work, same error.

Remember, Customer1 works fine, with the same setup! Could there be
something about my where clauses, or in some calculations I'm doing in
the queries, that would cause this?

[Rick Wannall]

Could you post the where clauses? How can anyone analyze this without
seeing the SQL that's executing?

[blue875]

Rick Wannall wrote:

Could you post the where clauses? How can anyone analyze this without
seeing the SQL that's executing?

QUERY 1:
SELECT ProductOrder.SubOrderNum AS [Abs Volume],
(DateDiff("h",[VendorServiceOrder].[ActualStartDateTime],[VendorServiceOrder].[ActualEndDateTime])/24)
AS [Abs TAT], VendorServiceOrder.Cost AS [Abs Cost], "NA" AS [Abs
Quality], VendorServiceOrder.ActualEndDateTime
FROM ((Service INNER JOIN (VendorServiceOrder INNER JOIN ((OrderMaster
INNER JOIN Customer ON OrderMaster.CustomerID = Customer.CustomerID)
INNER JOIN ProductOrder ON OrderMaster.OrderMasterID =
ProductOrder.OrderMasterID) ON VendorServiceOrder.ProductOrderID =
ProductOrder.ProductOrderID) ON Service.ServiceID =
VendorServiceOrder.ServiceID) INNER JOIN Product ON
ProductOrder.ProductID = Product.ProductID) INNER JOIN (ProductGroup
INNER JOIN ProductCategory ON ProductGroup.ProductGroupID =
ProductCategory.ProductGroupID) ON Product.ProductCategoryID =
ProductCategory.ProductCategoryID
WHERE (((VendorServiceOrder.Cost)>0) AND
((VendorServiceOrder.ActualEndDateTime)>#6/1/2005#) AND ((Service.Name)
Not Like "att*" And (Service.Name) Not Like "Title PU") AND
((ProductGroup.Name) Like "titl*") AND
((VendorServiceOrder.IsPaid)=True));

QUERY 2:
SELECT ProductOrder.SubOrderNum AS [Onl Volume],
(DateDiff("h",[VendorServiceOrder].[ActualStartDateTime],[VendorServiceOrder].[ActualEndDateTime])/24)
AS [Onl TAT], VendorServiceOrder.Cost AS [Onl Cost], "NA" AS [Onl
Quality], VendorServiceOrder.ActualEndDateTime
FROM ((Service INNER JOIN (VendorServiceOrder INNER JOIN ((OrderMaster
INNER JOIN Customer ON OrderMaster.CustomerID = Customer.CustomerID)
INNER JOIN ProductOrder ON OrderMaster.OrderMasterID =
ProductOrder.OrderMasterID) ON VendorServiceOrder.ProductOrderID =
ProductOrder.ProductOrderID) ON Service.ServiceID =
VendorServiceOrder.ServiceID) INNER JOIN Product ON
ProductOrder.ProductID = Product.ProductID) INNER JOIN (ProductGroup
INNER JOIN ProductCategory ON ProductGroup.ProductGroupID =
ProductCategory.ProductGroupID) ON Product.ProductCategoryID =
ProductCategory.ProductCategoryID
WHERE (((VendorServiceOrder.Cost)=0) AND
((VendorServiceOrder.ActualEndDateTime)>#6/1/2005#) AND ((Service.Name)
Not Like "att*" And (Service.Name) Not Like "Title PU") AND
((ProductGroup.Name) Like "titl*") AND
((VendorServiceOrder.IsPaid)=True));

QUERY 3:
SELECT "Atlas" AS System, Customer.CustAccountNum AS CustCode,
Customer.Name AS CustName, OrderMaster.PropState AS ST, Service.Name AS
Product, ProductOrder.SubOrderNum AS Volume,
(DateDiff("h",[VendorServiceOrder].[ActualStartDateTime],[VendorServiceOrder].[ActualEndDateTime])/24)
AS TAT, "N/A" AS Quality, ProductOrder.Price AS [Customer Cost],
VendorServiceOrder.Cost AS [Vendor Cost],
([ProductOrder].[Price]-[VendorServiceOrder].[Cost]) AS Margin,
IIf(([ProductOrder].[Price])=0,0,1-([VendorServiceOrder].[Cost])/([ProductOrder].[Price]))
AS [% Margin], VendorServiceOrder.ActualEndDateTime
FROM ((Service INNER JOIN (VendorServiceOrder INNER JOIN ((OrderMaster
INNER JOIN Customer ON OrderMaster.CustomerID = Customer.CustomerID)
INNER JOIN ProductOrder ON OrderMaster.OrderMasterID =
ProductOrder.OrderMasterID) ON VendorServiceOrder.ProductOrderID =
ProductOrder.ProductOrderID) ON Service.ServiceID =
VendorServiceOrder.ServiceID) INNER JOIN Product ON
ProductOrder.ProductID = Product.ProductID) INNER JOIN (ProductGroup
INNER JOIN ProductCategory ON ProductGroup.ProductGroupID =
ProductCategory.ProductGroupID) ON Product.ProductCategoryID =
ProductCategory.ProductCategoryID
WHERE (((Service.Name) Not Like "att*" And (Service.Name) Not Like
"Title PU") AND ((ProductOrder.Price)=0) AND
((VendorServiceOrder.ActualEndDateTime)>#6/1/2005#) AND
((ProductGroup.Name) Like "titl*") AND
((VendorServiceOrder.IsPaid)=True));

COMBINING QUERY: (Where the trouble hits)
SELECT Customer profit search sub customer overall.*,
IIf(IsNull([Customer profit search sub customer abstractor].[Abs
Volume]),0,1) AS IsAbstractor, [Customer profit search sub customer
abstractor].[Abs TAT], [Customer profit search sub customer
abstractor].[Abs Cost], [Customer profit search sub customer
abstractor].[Abs Quality], IIf(IsNull([Customer profit search sub
customer online].[Onl Volume]),0,1) AS IsOnline, [Customer profit
search sub customer online].[Onl TAT], [Customer profit search sub
customer online].[Onl Cost], [Customer profit search sub customer
online].[Onl Quality]
FROM ([Customer profit search sub customer overall] LEFT JOIN [Customer
profit search sub customer online] ON [Customer profit search sub
customer overall].[Volume]=[Customer profit search sub customer
online].[Onl Volume]) LEFT JOIN [Customer profit search sub customer
abstractor] ON [Customer profit search sub customer
overall].[Volume]=[Customer profit search sub customer abstractor].[Abs
Volume];

[Rick Wannall]

SELECT Customer profit search sub customer overall.*,

I don't think that piece of SQL ever ran anywhere. And I don't see anything
like From QUERY1 anywhere.

If you post the SQL straight out of the queries, and provide the real names
of the queries (QUERY1, QUERY2?), and the DDL of the tables involved, it
would be possible to run a quick test on this and make some response.

[blue875]

Sorry, the query names are:
QUERY1 = Customer profit search sub customer abstractor
QUERY2 = Customer profit search sub customer online
QUERY3 = Customer profit search sub customer overall

As for this SQL:
SELECT Customer profit search sub customer overall.*,
Keep in mind this does run. I thought this was a fairly common
statement, actually. I've seen [Table].* in tons of SQL books, not to
mention my own frequent use of it.
I'd like to remind you that I have another query very similar in
structure, where the only thing that is different is Service.Name and
ProductGroup.Name. I assure you, all the entries listed are valid.
As stated previously, the first three queries all run exactly as
expected. As also stated, there are no errors with the syntax,
otherwise my error message would point directly to something included
in the query, not something such as a column prefix "MS1", which is not
referred to anywhere.

Being that the source tables are on SQL Server, I do not have access to
those to give you the DDL. I assure you again, though, that there are
no incorrect references: no string references on integer columns, no
integer references on string columns, etc.

I believe that the issue may be more dealing with the interfacing of MS
Access to SQL Server. I mean, if it were a syntax issue, wouldn't the
error message be more closely tied to something typed? If it were an
incorrect reference, wouldn't the error message say something like
"Cannot reference column X as indicated" or something similar?

[Rick Wannall]

On 2-Jun-2006, bl*****@carolina.rr.com wrote:

SELECT Customer profit search sub customer overall.*,

You can assure me all you want, but without brackets around that table name,
that SQL never ran anywhere. Respondents in the newsgroup are not supposed
to have to figure out what you meant to post, or whether what you posted
maybe just needs a bracket here or a comma there or...whatever.

I'll be very interested to see how this one turns out.

[blue875]

That part of the SQL statement was generated by Access itself. I tend
to switch between the Design View and SQL View, because it creates the
statements faster. I simply double-clicked the "*" in the window for
that table, and Access created that phrase. In my other
almost-identical query, the statement starts the same way, with no
brackets around the table name, and it does run, as do some others set
up the same way. I know normally it requires brackets for any
table/query names that contain spaces or certain characters....I guess
there's a special case for "*" statements.

Having noticed that, I added the brackets and ran the query...still no
dice. I also tried removing one of the Left Outer Join tables, to see
if a simpler query would work better....also a no-go.