Help with SQL Query - "The multi-part identifier "alias field Name" could not be bound."

• CMitesh

  Say Hey Kid

  Points: 674

  More actions

  November 20, 2006 at 6:43 am

  #121576

  Hi Everyone

  This is the query and I am getting follwoing error message

  "The multi-part identifier "InvDate.Account Reference" could not be bound."

  SELECT   MAX([DATE NOTE ADDED]) AS LASTDATE,

           CC.[COMPANY],

           CC.[ACCOUNT REFERENCE],

           INVDATE.[LASTORDERDATE]

  FROM     CUSTOMERCONTACTNOTES AS CCN,

           (SELECT *

            FROM   CUSTOMER) AS CC,

           (SELECT   MAX([INVOICE DATE]) AS LASTORDERDATE,

                     [ACCOUNT REFERENCE]

            FROM     INVOICEDATA

            GROUP BY [ACCOUNT REFERENCE]) AS INVDATE

  WHERE    CCN.[COMPANY] = CC.[COMPANY]

           AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])

                                                                               FROM   INVOICEDATA)

           AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]

  GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]

  ORDER BY CC.COMPANY ASC

  By the way its SQL Server 2005 Environment.

  Mitesh

• CMitesh

  Say Hey Kid

  Points: 674

  More actions

  November 20, 2006 at 8:51 am

  #672987

  Guys found the solution to my problem...but with someones help.

  Removed - (select * from customer) --

  and Added INVDATE.[LASTORDERDATE] in Group by

  SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,

  CC.[COMPANY],

  CC.[ACCOUNT REFERENCE],

  INVDATE.[LASTORDERDATE]

  FROM CUSTOMERCONTACTNOTES AS CCN,CUSTOMER as CC,

  (SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,

  [ACCOUNT REFERENCE]

  FROM INVOICEDATA

  GROUP BY [ACCOUNT REFERENCE]) AS INVDATE

  WHERE CCN.[COMPANY] = CC.[COMPANY]

  AND CC.[ACCOUNT REFERENCE]COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])

  FROM INVOICEDATA)

  AND CC.[ACCOUNT REFERENCE] COLLATE SQL_Latin1_General_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]

  GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE],INVDATE.[LASTORDERDATE]

  ORDER BY CC.COMPANY ASC