August 10, 2010 at 7:55 am
Hi guys,
Can anybody help me to convert this access query to sql server please.
Cheers
SELECT dbo_capd_enrolmentisr.ei_ilra10, PU.m_id AS PUm_id, PQ.m_reference, dbo_capd_student.s_studentreference, dbo_capd_person.p_surname, dbo_capd_person.p_forenames
FROM ((((dbo_capd_student INNER JOIN ((dbo_capd_moduleenrolment INNER JOIN dbo_capd_enrolmentisr ON dbo_capd_moduleenrolment.e_id = dbo_capd_enrolmentisr.ei_id) INNER JOIN dbo_capd_module AS PQ ON dbo_capd_moduleenrolment.e_module = PQ.m_id) ON dbo_capd_student.s_id = dbo_capd_moduleenrolment.e_student) INNER JOIN dbo_capd_person ON dbo_capd_student.s_id = dbo_capd_person.p_id) INNER JOIN dbo_capd_offering AS CPDtoPQ ON PQ.m_id = CPDtoPQ.o_destination) INNER JOIN dbo_capd_offering AS PUtoCPD ON CPDtoPQ.o_source = PUtoCPD.o_destination) INNER JOIN dbo_capd_module AS PU ON PUtoCPD.o_source = PU.m_id
WHERE (((dbo_capd_enrolmentisr.ei_q17)>#7/31/2010#) AND ((PQ.m_type)="Pq") AND ((PQ.m_end)<#8/1/2010#) AND ((dbo_capd_moduleenrolment.e_status) Not In ("E","N")) AND ((nz([ei_q18m06],#8/1/2010#))>#7/31/2010#))
GROUP BY dbo_capd_enrolmentisr.ei_ilra10, PU.m_id, PQ.m_reference, dbo_capd_student.s_studentreference, dbo_capd_person.p_surname, dbo_capd_person.p_forenames
ORDER BY PQ.m_reference, dbo_capd_person.p_surname, dbo_capd_person.p_forenames;
August 10, 2010 at 8:30 am
wow, formatting really makes it readable:
i think this is what you are after.
because they are all inner joins, it doesn't matter whether there are a zillion nested parenthesis or not.
the access nz function is the same as ISNULL function.
dates are surrounded by single quotes instead of hash character(#08/10/2010# becomes '08/10/2010'
and string literals are in single instead of double quites.
SELECT
dbo_capd_enrolmentisr.ei_ilra10,
PU.m_id AS PUm_id,
PQ.m_reference,
dbo_capd_student. s_studentreference,
dbo_capd_person.p_surname,
dbo_capd_person.p_forenames
FROM dbo_capd_student
INNER JOIN dbo_capd_moduleenrolment
ON dbo_capd_student.s_id = dbo_capd_moduleenrolment.e_student
INNER JOIN dbo_capd_enrolmentisr
ON dbo_capd_moduleenrolment.e_id = dbo_capd_enrolmentisr.ei_id
INNER JOIN dbo_capd_module AS PQ
ON dbo_capd_moduleenrolment.e_module = PQ.m_id
INNER JOIN dbo_capd_person
ON dbo_capd_student.s_id = dbo_capd_person.p_id
INNER JOIN dbo_capd_offering AS CPDtoPQ
ON PQ.m_id = CPDtoPQ.o_destination
INNER JOIN dbo_capd_offering AS PUtoCPD
ON CPDtoPQ.o_source = PUtoCPD.o_destination
INNER JOIN dbo_capd_module AS PU
ON PUtoCPD.o_source = PU.m_id
WHERE (((dbo_capd_enrolmentisr.ei_q17)>'7/31/2010')
AND ((PQ.m_type)='Pq')
AND ((PQ.m_end)< '8/1/2010')
AND ((dbo_capd_moduleenrolment.e_status) Not In ('E', 'N'))
AND ((ISNULL([ei_q18m06], '8/1/2010'))>'7/31/2010'))
GROUP BY
dbo_capd_enrolmentisr.ei_ilra10,
PU.m_id,
PQ.m_reference,
dbo_capd_student.s_studentreference,
dbo_capd_person.p_surname,
dbo_capd_person.p_forenames
ORDER BY
PQ.m_reference,
dbo_capd_person.p_surname,
dbo_capd_person.p_forenames;
Lowell
August 10, 2010 at 10:38 am
Thanks Lowell, It's sorted now. the problem was mostly about the parentheses.
cheers
August 10, 2010 at 3:46 pm
Hi Guys,
is this correct?
I've got the following syntax error:
ODBC --call failed
invalid object name Query1_EnrolmentsWithPlannedEndAfterModuleEnd
Thank you!
SELECT PU.m_reference AS PUCode,
CASE
WHEN MAX(CPD.m_period) < 2010 Then 'No 2010 CPD'
ELSE '2010 CPD Exists'
END AS Has2010CPD
FROM dbo.capd_module AS PU
INNER JOIN Query1_EnrolmentsWithPlannedEndAfterModuleEnd
ON PU.m_id =Query1_EnrolmentsWithPlannedEndAfterModuleEnd.PUm_id
INNER JOIN dbo.capd_offering
ON Query1_EnrolmentsWithPlannedEndAfterModuleEnd.PUm_id = dbo.capd_offering.o_source
INNER JOIN dbo.capd_module AS CPD
ON dbo.capd_offering.o_destination = CPD.m_id
GROUP BY PU.m_reference
August 11, 2010 at 4:36 pm
ok, so you actually have a table named "query1" something or other?
You cannot call another query from a query.
You can call a function, but not a query.
Andrew SQLDBA
August 12, 2010 at 2:54 am
Hi AndrewSQLDBA,
I'm trying to convert an access query to TSQL syntax.
"Query1..." was a query then change to a table. Will I be able to join it?
If not, how would I change change or create a function from that query?
Cheers
enz54
August 12, 2010 at 8:46 am
You can read the code of the query, and translate to t-sql. There is not very much difference in the actual languages. Most of the difference that you will see is in the design of the database. With that many JOINs, I would look more at the database design. There are many things that SQL can do that Access cannot. You would be much further ahead by a better database design.
Andrew SQLDBA
August 12, 2010 at 9:16 am
Since MS Access doesn't support views, it lets you call queries from queries. It's not uncommon to have query1 do some joins and manipulation, then use that result set for the basis of query2 that does additional joins for the final result set - actually, Access doesn't seem to care how deep your query heirarchy goes; I've seen 4-5 levels.
In SQL-Server, you'll need to convert your base queries to views to get the same funtionality.
August 18, 2010 at 6:36 am
MS Access doesn't support views.
Yes it does.
When you save a query in Access, this is stored in the database as a View.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply