473,287 Members | 1,426 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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?

Jun 2 '06 #1
6 6505
Could you post the where clauses? How can anyone analyze this without
seeing the SQL that's executing?
Jun 2 '06 #2

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];

Jun 2 '06 #3
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.
Jun 2 '06 #4
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?

Jun 2 '06 #5
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.
Jun 2 '06 #6
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.

Jun 5 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Thomas Jerkins | last post by:
When I write a create table SQL statement I want to add some information about the column heading of each column. How does the exact syntax for the create table look like (which includes this column...
3
by: Andrew | last post by:
With command-line interface ( 3.23.37, UNIX Socket ) all is well with column aliasing. However, column aliases disappear in Excel, over ODBC, when there are multiple (joined) tables in the query. ...
2
by: muzamil | last post by:
Hi Your help for the following query will be highly apprecaited. I've wasted alot of time on it. Data definition is at the bottom. Thanks -----------------------------------
3
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it...
17
by: Benoit Martin | last post by:
I'm working on a project in VB.net connecting to a SQL Server 2000 database that I can't modify I created a dataset with a schema identical to the DB. When trying to update the DB from the dataset...
5
by: Mark Cave-Ayland | last post by:
Hi everyone, I'm trying to calculate an output column which is the difference of two other columns in the query output; the first column is an aggregate of items in stock, while the second...
2
by: mrkinsopo | last post by:
Here's my simple Trigger to lauch a sp in SQL2000: CREATE TRIGGER trExample ON TABLE_1 FOR INSERT AS if TABLE_1.FIELD_1 ='Some Value' begin declare @widget_id varchar(20) select...
5
by: sweetpotatop | last post by:
Hi, I got 'Invalid Column Name NewCol1' when I query the following: Select col1, col2, (some calculation from the fields) as NewCol1, (some calculation from the fields) as NewCol2, NewCol1 =...
4
by: Captain Jack Sparrow | last post by:
I have a column in a table named "Hospice". I have a make table query that pulls in this column but renames the column in the result to "Hospice (Yes/No)". The column name in the resulting table is...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.