Синтаксис Sql (access)

  • Автор темы korel
  • Дата начала
K

korel

Гость
#1
Помогите пожалуйста преобразовать этот скрипт под Access. Был бы очень благодарен за справочные материалы
по особенностям синтаксиса запросов под Access


select info_contact.LastName + ' ' + info_contact.FirstName + ' ' + info_contact.MiddleName as Name,
info_company.name as companyname,
category.name as categoryname,
specialization.name as specializationname,
department.name as departmentname,
position.name as positionname,
count(datediff(m,min(datefrom),max(datefrom))+1) as cntmes
from info_task
inner join info_taskstate on info_task.taskstate_id = info_taskstate.id and isfinish = 1
left join info_contact on info_task.contact_id = info_contact.id
left join info_company on info_task.company_id = info_company.id
left join info_dictionary category on info_contact.category_id = category.id
left join info_dictionary specialization on info_contact.specialization_id = specialization.id
left join info_dictionary department on info_contact.department_id = department.id
left join info_dictionary position on info_contact.position_id = position.id
group by info_contact.LastName, info_contact.FirstName, info_contact.MiddleName, info_company.name, category.name, specialization.name,department.name, position.name
order by 1,2
 
E

etc

Гость
#2
А что, оно в Access не работает? И что говорит?
 
K

korel

Гость
#3
Говорит - Ошибка синтаксиса(пропущен оператор) в выражении запроса ".

Немножко поправил синтаксис(на sql работает как часы :) ), а на Access не хочет:

select info_contact.LastName + ' ' + info_contact.FirstName + ' ' + info_contact.MiddleName as Name,
info_company.name as companyname, category.name as categoryname, specialization.name as specializationname,
department.name as departmentname, position.name as positionname,
count(*) as cnt,
datediff(m,min(datefrom),max(datefrom))+1 as cntmes
from info_task
inner join info_taskstate on info_task.taskstate_id = info_taskstate.id and isfinish = 1
left join info_contact on info_task.contact_id = info_contact.id
left join info_company on info_task.company_id = info_company.id
left join info_dictionary category on info_contact.category_id = category.id
left join info_dictionary specialization on info_contact.specialization_id = specialization.id
left join info_dictionary department on info_contact.department_id = department.id
left join info_dictionary position on info_contact.position_id = position.id
group by info_contact.LastName, info_contact.FirstName, info_contact.MiddleName, info_company.name, category.name, specialization.name,department.name, position.name
order by 1,2
 
K

korel

Гость
#4
С учетом специфики синтаксиса аксесса - но все равно выдает сообщение ошибка синтаксиса в операции join:
буду очень благодарен если подскажете "где собака зарыта".

select info_contact.LastName + ' ' + info_contact.FirstName + ' ' + info_contact.MiddleName as Name,
info_company.name as companyname, category.name as categoryname, specialization.name as specializationname,
department.name as departmentname, position.name as positionname,
count(*) as cnt,
datediff(m,min(datefrom),max(datefrom))+1 as cntmes
from info_task inner join
(info_taskstate left join(info_contact left join(info_company left join(info_dictionary category left join(info_dictionary specialization left join(info_dictionary department left join(info_dictionary position on info_contact.position_id = position.id)on info_contact.department_id = department.id) on info_contact.specialization_id = specialization.id) on info_contact.category_id = category.id)
on info_task.company_id = info_company.id)on info_task.contact_id = info_contact.id)on info_task.taskstate_id = info_taskstate.id and isfinish = 1;
group by info_contact.LastName, info_contact.FirstName, info_contact.MiddleName, info_company.name, category.name, specialization.name,department.name, position.name
order by 1,2;
 
K

korel

Гость
#6
Всем спасибо!!!!!!!!!!!!!!!
Помощь утопающих - дело рук самих утопающих!
ТЕМА ЗАКРЫТА! :)