How to convert a SQL SERVER 2000 Query to MS Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to convert a SQL SERVER 2000 Query to MS Access

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-20 : 07:54:18

Reena S Abraham writes "SELECT PT.ProgramType, P.ProgramName, CAST(P.Period AS varchar(5)) + ' ' + P.PeriodType AS Period, CAST(P.Sessions AS varchar(5)) + ' ' + P.SessionType AS Sessions, P.Days, P.TimeSlot, P.TimeFrom, P.TimeTo, P.Persons, A.AdmissionDate, A.StartingDate, A.ExpiryDate,'' AS FullName, A.ProgramFees, A.Discount, A.EnrollmentFee, A.MaintenanceFee, A.BalanceFee, A.Tax, A.FeesPayable, A.id, P.ID AS Expr1, '' AS Expr2, PT.Code,'a1' AS Type
FROM MasterProgram P INNER JOIN MasterProgramType PT ON P.ProgramType = PT.Code INNER JOIN Admission A ON P.ID = A.Program WHERE (A.MemberID = '2005AUG1005')
UNION
SELECT PT.ProgramType, P.ProgramName, CAST(P.Period AS varchar(5)) + ' ' + P.PeriodType AS Period, CAST(P.Sessions AS varchar(5)) + ' ' + P.SessionType AS Sessions, P.Days, P.TimeSlot, P.TimeFrom, P.TimeTo, P.Persons, A.ADate, A.SDate, A.EDate, E.FName + ' ' + E.LName AS FullName, A.ProgramFees, A.Discount, A.EnrollmentFee, A.MaintenanceFee, A.BalanceFee, A.Tax, A.FeesPayable, A.id, P.ID, E.EmpCode, PT.Code, 'a2' AS Type
FROM MasterProgram P INNER JOIN MasterProgramType PT ON P.ProgramType = PT.Code INNER JOIN AddOn A ON P.ID = A.Program LEFT OUTER JOIN MasterEmployee E ON A.Trainer = E.EmpCode WHERE (A.memberID = '2005AUG1005') ORDER BY Type

convert this SQL Query to MS Access Query"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-20 : 09:28:46

1. The use of the word PLEASE goes a long way around here!
2. Can you not try for yourself a small simple "JOIN tabla to tableb" query on both SQL + ACCESS and then apply the lessons learnt to the code here....the learning experience will be far better for you in the long run.
3. Looking at it quickly I can't see any obvious problems with the code...unless the CAST function or dateformat in the WHERE clause needs reworking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources