title: Changing cte for microsoft access
body: <p>I've tried searching for an answer to this but can't find one.</p>
  
  <p>I have a CTE I use for SQL queries relating to 2 data tables in a database.  The primary key of one table is a foreign key in the other and can appear numerous times in the 2nd table.  I want to do a count of the number of times each foreign key appears in the second table, and list this as a total field in my search results along with details from the first table.  As CTEs don't work in Access I've adjusted this to use a sub select in the join, but it still doesn't like it in access.</p>
  
  <p>Here are the basic parts of the tables</p>
  
  <pre><code>CREATE TABLE [dbo].[Clients](
      [ClientRef] [int] NOT NULL,
      [Surname] [varchar](40) NULL,
      [Forenames] [varchar](50) NULL,
      [Title] [varchar](40) NULL,
   CONSTRAINT [CLIE_ClientRef_PK] PRIMARY KEY CLUSTERED 
  (
      [ClientRef] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  CREATE TABLE [dbo].[Policies](
      [PolicyRef] [int] NOT NULL,
      [ClientRef] [int] NULL,
   CONSTRAINT [POLI_PolicyRef_PK] PRIMARY KEY CLUSTERED 
  (
      [PolicyRef] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Here's my CTE</p>
  
  <pre><code>WITH CliPol (ClientRef, Plans) AS (SELECT ClientRef, COUNT(ClientRef) AS Plans FROM Policies GROUP BY ClientRef)
  SELECT Clients.Surname, Clients.Forenames, Clients.Title, CliPol.Plans AS [No. of plans] 
  FROM Clients LEFT JOIN CliPol ON Clients.ClientRef = CliPol.ClientRef 
  ORDER BY Surname, Forenames;
  </code></pre>
  
  <p>And here's my adjusted query.</p>
  
  <pre><code>SELECT Clients.ClientRef, Clients.Surname, Clients.Forenames, Clients.Title , Plans.NoPlans
  FROM Clients
  LEFT JOIN 
      (SELECT ClientRef, COUNT(ClientRef) AS NoPlans FROM Policies GROUP BY ClientRef) 
      AS Plans ON Plans.ClientRef = Clients.ClientRef
  ORDER BY Clients.Surname, Clients.Forenames
  </code></pre>
  
  <p>Unfortunately Access throws error #3131, <em>"Syntax error in FROM clause"</em>, when I try to run that query.</p>
  
  <p>Does anybody know how I make this work in Access?</p>