Microsoft SQL Server Native Client 11.0 Error #4104

[NeoPa]

I don't know what I'm missing. I have tried to run the following SQL from inside a QueryDef in Access (2003) but it keeps coming back with the error :
[Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "tW.WE" could not be bound (#4104)
That error text actually occurs four times in total in the message.

INSERT INTO Avail
     ( [BranchID]
     , [EmpID]
     , [W/E])
SELECT [sqAllAvail].[BranchID]
     , [sqAllAvail].[EmpID]
     , [sqAllAvail].[W/E]
FROM   (SELECT [tE].[BranchID]
             , [tE].[EmpID]
             , [tW].[WE] AS [W/E]
        FROM   [Employees] AS [tE]
             , [tblWE] AS [tW]
        WHERE  ([tE].[Status]='C')
          AND  ([tW].[WE] Between GetWE('w-3') And GetWE('w13'))) AS [sqAllAvail]
       LEFT JOIN
       [Avail]
  ON   ([sqAllAvail].[BranchID]=[Avail].[BranchID])
 AND   ([sqAllAvail].[EmpID]=[Avail].[EmpID])
 AND   ([sqAllAvail].[W/E]=[Avail].[W/E])
WHERE  ([Avail].[W/E] Is Null)

Clearly, the SQL that Access uses is different from this in layout but this is what I pasted into the QueryDef SQL window and it accepted it happily enough.

I looked through The multi-part identifier "%.*ls" could not be bound. and played around with the names of both the table and the field to try to ensure they wouldn't clash with anything else but none of the changes had the desired effect. I couldn't derive any more from what it was saying than that. Nothing else seemed relevant to this situation. Maybe I'm missing something.

For reference, all three tables are linked tables to the SQL Server 12 BE. GetWE() is a function that returns a Sunday date. The parameter 'w-3' means 3 weeks before the Sunday on or following today, and 'w13' means 13 weeks after.

[Avail] stores availability records per Employee (BranchID,EmpID ) per Week (W/E). The cartesian product of [Employees] and [tblWE] reflects all possible [Avail] records for the range, but only those that don't match existing records are appended.

I also tried it without the table ALIAS of [tW], just using [tblWE] throughout. This had the same problem, reported as dbo.tblWE.WE.

[Rabbit]

I don't see anything explicitly wrong with the SQL.

Do you get the error if you don't alias the WE column?

What is the DDL of the Employee table?

[ck9663]

I don't see anything either. Can you run the subquery on its own and will it give you the same error? If you can give us the DDL of both tables, we might be able to recreate the issue and might be able to assist more.

You might be able to avoid subquery either to speed up your process. It looks like INNER joins and a NOT EXISTS where clause will do. Will not be able to completely confirm without additional details.

~~ CK

[NeoPa]

Firstly, I can confirm that I get the same error in all situations I checked. That explicitly does include taking out the various ALIASES, as well as renaming [tblWE] and the [WE] field within it.

For DDL I assume you mean the layout as specified by what I get when I Script CREATE to a window. Let me warn that much of the table [Employees] is irrelevant to this (in my estimation), but I'll include it all anyway for completeness. I know the structure leaves a lot to be desired but in a live system it's hard to make fundamental changes without causing grief we can ill afford currently.

USE [FrontOffice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
	[EmpID] [varchar](8) NOT NULL,
	[Employee ID] [varchar](50) NULL,
	[PrimaryID] [varchar](8) NULL,
	[BranchID] [varchar](3) NULL,
	[Status] [varchar](1) NOT NULL,
	[Pending] [smallint] NOT NULL,
	[Qualified] [smallint] NULL,
	[Photograph] [varchar](100) NULL,
	[Job Title] [varchar](50) NULL,
	[Title] [varchar](50) NULL,
	[First Name] [varchar](50) NULL,
	[Surname] [varchar](50) NOT NULL,
	[Address 1] [varchar](50) NULL,
	[Address 2] [varchar](50) NULL,
	[Address 3] [varchar](50) NULL,
	[PostCode] [varchar](50) NULL,
	[Zone] [varchar](2) NULL,
	[Telephone] [varchar](20) NULL,
	[Telephone(Day)] [varchar](50) NULL,
	[Telephone(Eve)] [varchar](50) NULL,
	[Mobile] [varchar](50) NULL,
	[Date of Birth] [datetime2](7) NULL,
	[NI Number] [varchar](9) NULL,
	[Qualifications] [varchar](120) NULL,
	[Notes] [varchar](200) NULL,
	[Previous Surname] [varchar](50) NULL,
	[Marital Status] [varchar](50) NULL,
	[Nationality] [varchar](50) NULL,
	[Visa Expiry] [datetime2](7) NULL,
	[VisaRenewal] [datetime2](7) NULL,
	[Religion] [varchar](50) NULL,
	[Next of Kin] [varchar](50) NULL,
	[Name] [varchar](50) NULL,
	[Relationship] [varchar](50) NULL,
	[Address] [varchar](50) NULL,
	[Tel Day] [varchar](50) NULL,
	[Tel Eve] [varchar](50) NULL,
	[Ref 1] [varchar](50) NULL,
	[Date Received] [datetime2](7) NULL,
	[Check OK] [smallint] NULL,
	[Ref 2] [varchar](50) NULL,
	[Date Received2] [datetime2](7) NULL,
	[Check OK2] [smallint] NULL,
	[Serious Illness] [varchar](50) NULL,
	[Last Employer] [varchar](50) NULL,
	[Previous Employer] [varchar](50) NULL,
	[Previous Employer2] [varchar](50) NULL,
	[Previous Employer 3] [varchar](50) NULL,
	[PIN Number] [varchar](50) NULL,
	[PIInsurance] [smallint] NOT NULL,
	[PIIExpiry] [datetime2](7) NULL,
	[Availability Status] [varchar](50) NULL,
	[Expiry Date] [datetime2](7) NULL,
	[Transport] [smallint] NULL,
	[Hospital Work OK] [smallint] NULL,
	[Police Check] [smallint] NULL,
	[Registration Date] [datetime2](7) NOT NULL,
	[ID Badge Issued] [datetime2](7) NULL,
	[CRB Disclosure Received] [datetime2](7) NULL,
	[CRB Disclosure Number] [varchar](50) NULL,
	[CRB Disclosure Appl] [varchar](50) NULL,
	[CRB Status] [varchar](12) NULL,
	[CRB Payment] [varchar](50) NULL,
	[Hep B] [smallint] NULL,
	[MMR] [smallint] NULL,
	[TB] [smallint] NULL,
	[Varicella] [smallint] NULL,
	[Occupational Health Check] [datetime2](7) NULL,
	[By] [varchar](50) NULL,
	[PASA Compliant] [varchar](50) NULL,
	[Interviewed by] [varchar](50) NULL,
	[Lottery Number] [varchar](50) NULL,
	[NVQ2] [smallint] NULL,
	[NVQ3] [smallint] NULL,
	[Senior Interview] [smallint] NULL,
	[Mandatory Training] [smallint] NULL,
	[Proof of Identity] [varchar](50) NULL,
	[Proof of Address] [varchar](50) NULL,
	[Proof of NI Number] [varchar](50) NULL,
	[Proof of Right to Work] [varchar](50) NULL,
	[Proof of Full Work History] [smallint] NULL,
	[Photo x 2] [smallint] NULL,
	[Communication Skills] [varchar](50) NULL,
	[Handbook Issued] [smallint] NULL,
	[Terms of Engagement] [smallint] NULL,
	[NMC Web Check] [smallint] NULL,
	[Suspend] [smallint] NULL,
	[Email] [varchar](50) NULL,
	[Introduction] [varchar](50) NULL,
	[Student Visa] [smallint] NULL,
	[Dual Registered] [smallint] NULL,
	[Employee Group] [varchar](50) NULL,
	[HCCPIN] [varchar](10) NULL,
	[StatusUpdated] [datetime2](7) NULL,
	[Leaving Date] [datetime2](7) NULL,
	[PermanentStaff] [smallint] NOT NULL,
	[CRBPositive] [smallint] NOT NULL,
	[Gender] [varchar](1) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX1_Employees] UNIQUE NONCLUSTERED 
(
	[BranchID] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX2_Employees] UNIQUE NONCLUSTERED 
(
	[Status] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX3_Employees] UNIQUE NONCLUSTERED 
(
	[BranchID] ASC,
	[Status] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX4_Employees] UNIQUE NONCLUSTERED 
(
	[Employee ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_Status]  DEFAULT ('P') FOR [Status]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_Pending]  DEFAULT ((-1)) FOR [Pending]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_Zone]  DEFAULT ('01') FOR [Zone]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_PIInsurance]  DEFAULT ((0)) FOR [PIInsurance]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_Registration Date]  DEFAULT (CONVERT([date],getdate())) FOR [Registration Date]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_StatusUpdated]  DEFAULT (CONVERT([date],getdate())) FOR [StatusUpdated]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_PermanentStaff]  DEFAULT ((0)) FOR [PermanentStaff]
GO
ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_CRBPositive]  DEFAULT ((0)) FOR [CRBPositive]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Address] CHECK  (([Address]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Address]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Address 1] CHECK  (([Address 1]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Address 1]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Address 2] CHECK  (([Address 2]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Address 2]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Address 3] CHECK  (([Address 3]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Address 3]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Availability Status] CHECK  (([Availability Status]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Availability Status]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_BranchID] CHECK  (([BranchID]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_BranchID]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_By] CHECK  (([By]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_By]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_EmpID] CHECK  (([EmpID]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_EmpID]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Employee ID] CHECK  (([Employee ID]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Employee ID]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_First Name] CHECK  (([First Name]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_First Name]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Gender] CHECK  (([Gender]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Gender]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_HCCPIN] CHECK  (([HCCPIN]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_HCCPIN]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Interviewed by] CHECK  (([Interviewed by]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Interviewed by]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Job Title] CHECK  (([Job Title]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Job Title]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Last Employer] CHECK  (([Last Employer]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Last Employer]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Marital Status] CHECK  (([Marital Status]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Marital Status]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Mobile] CHECK  (([Mobile]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Mobile]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Name] CHECK  (([Name]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Name]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Nationality] CHECK  (([Nationality]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Nationality]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Next of Kin] CHECK  (([Next of Kin]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Next of Kin]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_NI Number] CHECK  (([NI Number]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_NI Number]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Notes] CHECK  (([Notes]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Notes]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Photograph] CHECK  (([Photograph]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Photograph]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_PIN Number] CHECK  (([PIN Number]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_PIN Number]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_PostCode] CHECK  (([PostCode]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_PostCode]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Previous Employer] CHECK  (([Previous Employer]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Previous Employer]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Previous Employer 3] CHECK  (([Previous Employer 3]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Previous Employer 3]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Previous Employer2] CHECK  (([Previous Employer2]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Previous Employer2]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Previous Surname] CHECK  (([Previous Surname]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Previous Surname]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_PrimaryID] CHECK  (([PrimaryID]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_PrimaryID]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Qualifications] CHECK  (([Qualifications]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Qualifications]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Ref 1] CHECK  (([Ref 1]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Ref 1]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Ref 2] CHECK  (([Ref 2]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Ref 2]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Relationship] CHECK  (([Relationship]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Relationship]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Religion] CHECK  (([Religion]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Religion]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Serious Illness] CHECK  (([Serious Illness]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Serious Illness]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Status] CHECK  (([Status]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Status]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Surname] CHECK  (([Surname]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Surname]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Tel Day] CHECK  (([Tel Day]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Tel Day]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Tel Eve] CHECK  (([Tel Eve]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Tel Eve]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Telephone] CHECK  (([Telephone]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Telephone]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Telephone(Day)] CHECK  (([Telephone(Day)]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Telephone(Day)]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Telephone(Eve)] CHECK  (([Telephone(Eve)]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Telephone(Eve)]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Title] CHECK  (([Title]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Title]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [CK_Employees_Zone] CHECK  (([Zone]<>''))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Employees_Zone]
GO

USE [FrontOffice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Avail](
	[AvailID] [int] IDENTITY(1,1) NOT NULL,
	[BranchID] [varchar](8) NOT NULL,
	[EmpID] [varchar](8) NOT NULL,
	[W/E] [datetime2](7) NOT NULL,
	[Mon Early] [varchar](1) NULL,
	[Mon Late] [varchar](1) NULL,
	[Mon Night] [varchar](1) NULL,
	[Tue Early] [varchar](1) NULL,
	[Tue Late] [varchar](1) NULL,
	[Tue Night] [varchar](1) NULL,
	[Wed Early] [varchar](1) NULL,
	[Wed Late] [varchar](1) NULL,
	[Wed Night] [varchar](1) NULL,
	[Thu Early] [varchar](1) NULL,
	[Thu Late] [varchar](1) NULL,
	[Thu Night] [varchar](1) NULL,
	[Fri Early] [varchar](1) NULL,
	[Fri Late] [varchar](1) NULL,
	[Fri Night] [varchar](1) NULL,
	[Sat Early] [varchar](1) NULL,
	[Sat Late] [varchar](1) NULL,
	[Sat Night] [varchar](1) NULL,
	[Sun Early] [varchar](1) NULL,
	[Sun Late] [varchar](1) NULL,
	[Sun Night] [varchar](1) NULL,
	[Availability Notes] [varchar](50) NULL,
	[Meds] [smallint] NULL,
 CONSTRAINT [PK_Avail] PRIMARY KEY CLUSTERED 
(
	[AvailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX1_Avail] UNIQUE NONCLUSTERED 
(
	[EmpID] ASC,
	[W/E] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX2_Avail] UNIQUE NONCLUSTERED 
(
	[BranchID] ASC,
	[EmpID] ASC,
	[W/E] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX3_Avail] UNIQUE NONCLUSTERED 
(
	[W/E] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UX4_Avail] UNIQUE NONCLUSTERED 
(
	[W/E] ASC,
	[BranchID] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Availability Notes] CHECK  (([Availability Notes]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Availability Notes]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_BranchID] CHECK  (([BranchID]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_BranchID]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_EmpID] CHECK  (([EmpID]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_EmpID]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Fri Early] CHECK  (([Fri Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Fri Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Fri Late] CHECK  (([Fri Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Fri Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Fri Night] CHECK  (([Fri Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Fri Night]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Mon Early] CHECK  (([Mon Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Mon Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Mon Late] CHECK  (([Mon Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Mon Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Mon Night] CHECK  (([Mon Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Mon Night]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Sat Early] CHECK  (([Sat Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Sat Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Sat Late] CHECK  (([Sat Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Sat Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Sat Night] CHECK  (([Sat Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Sat Night]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Sun Early] CHECK  (([Sun Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Sun Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Sun Late] CHECK  (([Sun Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Sun Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Sun Night] CHECK  (([Sun Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Sun Night]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Thu Early] CHECK  (([Thu Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Thu Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Thu Late] CHECK  (([Thu Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Thu Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Thu Night] CHECK  (([Thu Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Thu Night]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Tue Early] CHECK  (([Tue Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Tue Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Tue Late] CHECK  (([Tue Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Tue Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Tue Night] CHECK  (([Tue Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Tue Night]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Wed Early] CHECK  (([Wed Early]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Wed Early]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Wed Late] CHECK  (([Wed Late]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Wed Late]
GO
ALTER TABLE [dbo].[Avail]  WITH CHECK ADD  CONSTRAINT [CK_Avail_Wed Night] CHECK  (([Wed Night]<>''))
GO
ALTER TABLE [dbo].[Avail] CHECK CONSTRAINT [CK_Avail_Wed Night]
GO

USE [FrontOffice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblWE](
	[WE] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_tblWE] PRIMARY KEY CLUSTERED 
(
	[WE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

@CK.
I'm not sure I can see how I could redo without a subquery. Perhaps you could explain, assuming it turns out that it's possible in the circumstances.

Sometimes, in Access, I've had to find ways around the limitations of Jet/ACE SQL as it often decides that particular approaches result in a non-updatable data set. It may well be that, without the same limitations, T-SQL allows a more straightforward approach. Bear in mind though, that for now at least, my main and very important requirement is to keep the SQL in Access for now. The less changes I have to make before I can show a workable system the better it looks. Later on I plan to add improvements by moving more to SQL Server where I can find benefits, but that's definitely a later stage of the project if I can get away with it.

[Rabbit]

I don't see any conflicts. Are there any user defined data types in the database?

Does the subquery run by itself?

[ck9663]

The query, as is, ran ok with in my query window. Although I have to replace the function with getdate() so it doesn't return any error. Unless your function refers to the column showing in the error message, then that could be what's offending your code.

I tried to recreate the query without a subquery. I'm not sure if this is what you need but it would go something like this:

INSERT INTO Avail
     ( [BranchID]
     , [EmpID]
     , [W/E])
SELECT [tE].[BranchID], [tE].[EmpID], [tW].[WE] AS [W/E]
FROM   [Employees] AS [tE]
inner join [tblWE] AS [tW] on ([tE].[Status]='C') AND  ([tW].[WE] Between GetWE('w-3') And GetWE('w13'))
where not exists (select 1 from [Avail]
  where  ([tE].[BranchID]=[Avail].[BranchID])
 AND   ([tE].[EmpID]=[Avail].[EmpID])
 AND   ([tE].[W/E]=[Avail].[W/E]))

Good Luck!!!


~~ CK

[NeoPa]

I can say now that there are no User Defined types in the database at all.

Otherwise I'm critically short of time today and I'd like to give your responses the full time and attention they deserve before responding properly.

[NeoPa]

Hi CK.

After fixing a couple of minor typos I translated your suggestion into this :

INSERT INTO [Avail]
     ( [BranchID]
     , [EmpID]
     , [W/E])
SELECT [tE].[BranchID]
     , [tE].[EmpID]
     , [tW].[WE] AS [W/E]
FROM   [Employees] AS [tE]
       INNER JOIN
       [tblWE] AS [tW]
  ON   ([tE].[Status]='C')
 AND   ([tW].[WE] Between GetWE('w-3') And GetWE('w13'))
WHERE  NOT EXISTS (SELECT 1
                   FROM   [Avail]
                   WHERE  ([Avail].[BranchID]=[tE].[BranchID])
                     AND  ([Avail].[EmpID]=[tE].[EmpID])
                     AND  ([Avail].[W/E]=[tW].[WE]))

I trust it is an accurate reflection of your idea. It took me a while to realise that, indeed it was an accurate reflection of the logic.

When I tried it out I found, not entirely to my surprise because I've tried similar JOINs before in Jet/ACE, that the JOIN expressiion isn't supported.
Realising that the JOIN wasn't fundamental to the whole idea but just a nice extra to have, I tried :

INSERT INTO [Avail]
     ( [BranchID]
     , [EmpID]
     , [W/E])
SELECT [tE].[BranchID]
     , [tE].[EmpID]
     , [tW].[WE] AS [W/E]
FROM   [Employees] AS [tE]
     , [tblWE] AS [tW]
WHERE  ([tE].[Status]='C')
  AND  ([tW].[WE] Between GetWE('w-3') And GetWE('w13'))
  AND  NOT EXISTS (SELECT 1
                   FROM   [Avail]
                   WHERE  ([Avail].[BranchID]=[tE].[BranchID])
                     AND  ([Avail].[EmpID]=[tE].[EmpID])
                     AND  ([Avail].[W/E]=[tW].[WE]))

This one worked!!!!! :-)

Hopefully I'll let this technique sink down into my psyche and come up with it, or something like it, in future when doing similar work. It's a struggle though. I never seem to think of subqueries in the WHERE clause even though Rabbit's been posting examples of it in Bytes for many years.

[NeoPa]

Rabbit:
Does the subquery run by itself?

Yes. Strangely, it does.

It goes without saying, but I'm going to anyway, that I'm very grateful to you both for your time and efforts spent helping me. It has been tremendously helpful to me in truth.

[Rabbit]

You're welcome, it's my pleasure. It's unfortunate we couldn't figure out what's causing the error. I have no idea why the subquery in the join doesn't work. I see nothing wrong with it.

I tend to avoid subqueries in the WHERE clause when I can make do with a subquery in the FROM clause. I find that the performance is usually better.

[NeoPa]

That's very good to know. It tends to be my way of thinking generally, but then I overlook the whole idea of subqueries in the WHERE clasue completely most times.