SQL v Access table selection alias

Question

I'm trying to convert some SSMS SQL to Access SQL and am finding the whole process rather frustrating! I have SQL that works perfectly well in SSMS but cannot get it to work in Access. The SQL is relatively simple. All it does is update one field in a table based on a count of items in a second table.

update Summary_Complaint_Table set period1_count = sql.mycount from 
(
 select t2.category,count(t2.category)as mycount 
 from complaints t2 
 where t2.date_received between #1/9/2015# and #23/12/2016# 
 group by category
 ) as sql 
 where Summary_Complaint_Table.category = sql.category

The inner Select works perfectly well as does the outer update when I substitute sql.count and sql_category with values. The error I'm getting is

Syntax error (missing operator) in query expression 'sql.mycount from    
(select t2.category,count(t2.category)as mycount from complaints t2 
 where t2.date_received between #1/9/2015# and #23/12/2016# 
 group by category) as sql'

The original SSMS (SQL server 2005) syntax that works is

update #temp set period1_count = sql.mycount
from 
(
select t2.category,count(t2.category)as mycount
from complaints t2
where t2.date_received between @period1_from and @period1_to
group by category
) as sql
where 
#temp.category = sql.category

Will this help? http://stackoverflow.com/questions/871905/use-select-inside-an-update-query — Prisoner, Sep 08 '16 at 09:58

Sergey S. · Accepted Answer · 2016-09-08T11:43:10.230

2

Access cannot update data in one SQL if it contains aggregation/group by functions in any part of SQL. As workaround you can use DCount function instead of Count()..Group By.

edited Sep 08 '16 at 11:43

answered Sep 08 '16 at 10:12

Sergey S.

6,296
1
14
29

Thanks for this Sergey. I've used this successfully to create the data I need. It looks to me like I'll be doing most of my Access SQL using VBA! – MiguelH Sep 12 '16 at 11:06

score 0 · Answer 2 · answered Sep 08 '16 at 10:11

I believe you need a space and an "as":

'sql.mycount from    
(select t2.category, count(*) as mycount from complaints as t2 
 where t2.date_received between #2015/09/01# and #2016/12/23# 
 group by category) as sql'

Also, the dd/mm/yyyy date sequence will not work where dd is 12 or less.

SQL v Access table selection alias

2 Answers2