Hey everyone, hows hump day treating you?! 😃
Hey, got a question about something I'm trying to find a solution to.
So my boss comes and tells me she wants this MS Access Query syntax converted so it can work in SQL 2005.
SELECT dbo_vStudents.Location, dbo_vStudents.StudentID, dbo_vStudents.SSN, dbo_vTerm.TermBeginDate, IIf([Re-Entry]="Y","Re-Enter",IIf([StudentStatus]="Drop" Or [StudentStatus]="Active" Or [StudentStatus]="Transfer" Or [StudentStatus]="Upgrade" Or [StudentStatus]="Downgrade" Or [StudentStatus]="Change Program" Or [StudentStatus]="Graduate","Start",IIf(Left([StudentStatus],3)="EMP","EMP",IIf(Left([StudentStatus],3)="Non","NonMatric",[StudentStatus])))) AS [Start Stat], dbo_vStudentStatus.StudentStatus, dbo_vStudents.LastName, dbo_vStudents.FirstName, dbo_vStudents.MiddleName, dbo_vProgram.ProgramCode, dbo_vStudentEnrollment.EnrollmentLDA, dbo_vStudentEnrollment.EnrollmentGraduation, dbo_vStudentEnrollment.DropDate, dbo_vStudentEnrollment.[Re-Entry], Right(Year(dbo_vTerm!TermBeginDate),2) & Right("0" & Month(dbo_vTerm!TermBeginDate),2) AS [Yr/Mo], dbo_vEmployees.LName AS [AC LName], dbo_vEmployees.FName AS [AC FName], dbo_vEmployees.UserDefined06 AS [AC Campus], dbo_vLeadSourceGroup.LeadSourceGroup, dbo_vLead.LeadDate, [TermBeginDate]-[LeadDate] AS LeadtoStart, IIf([StudentStatus]="Drop",[EnrollmentLDA]-[TermBeginDate],"") AS StarttoDrop
FROM (((((((dbo_vStudentEnrollment LEFT JOIN dbo_vStudentStatus ON (dbo_vStudentEnrollment.EnrollmentStatusID = dbo_vStudentStatus.StudentStatusID) AND (dbo_vStudentEnrollment.Location = dbo_vStudentStatus.Location)) LEFT JOIN dbo_vTerm ON (dbo_vStudentEnrollment.Location = dbo_vTerm.Location) AND (dbo_vStudentEnrollment.EnrollmentFirstTermID = dbo_vTerm.TermID)) LEFT JOIN dbo_vProgram ON (dbo_vStudentEnrollment.ProgramNo = dbo_vProgram.ProgramNo) AND (dbo_vStudentEnrollment.Location = dbo_vProgram.Location)) LEFT JOIN dbo_vEmployees ON (dbo_vStudentEnrollment.AdminRepEmpID = dbo_vEmployees.EmpID) AND (dbo_vStudentEnrollment.Location = dbo_vEmployees.Location)) LEFT JOIN dbo_vStudents ON (dbo_vStudentEnrollment.StudentNo = dbo_vStudents.StudentNo) AND (dbo_vStudentEnrollment.Location = dbo_vStudents.Location)) LEFT JOIN dbo_vLead ON (dbo_vStudents.LeadsID = dbo_vLead.LeadsID) AND (dbo_vStudents.Location = dbo_vLead.Location)) LEFT JOIN dbo_vLeadSource ON (dbo_vLead.SourceCodeID = dbo_vLeadSource.LeadSourceID) AND (dbo_vLead.Location = dbo_vLeadSource.Location)) LEFT JOIN dbo_vLeadSourceGroup ON (dbo_vLeadSource.Location = dbo_vLeadSourceGroup.Location) AND (dbo_vLeadSource.LeadSourceGroupID = dbo_vLeadSourceGroup.LeadSourceGroupID)
WHERE (((dbo_vStudents.Location)="Boise") AND ((dbo_vTerm.TermBeginDate)>=[Begin Date ?]) AND ((dbo_vStudentStatus.StudentStatus) Not Like "future*") AND ((dbo_vStudentEnrollment.TransferIn) Is Null Or (dbo_vStudentEnrollment.TransferIn)="N"))
ORDER BY dbo_vStudents.Location, dbo_vTerm.TermBeginDate, IIf([Re-Entry]="Y","Re-Enter",IIf([StudentStatus]="Drop" Or [StudentStatus]="Active" Or [StudentStatus]="Transfer" Or [StudentStatus]="Upgrade" Or [StudentStatus]="Downgrade" Or [StudentStatus]="Change Program" Or [StudentStatus]="Graduate","Start",IIf(Left([StudentStatus],3)="EMP","EMP",IIf(Left([StudentStatus],3)="Non","NonMatric",[StudentStatus])))), dbo_vStudents.LastName;
The first problem is, I don't hardly know Access for crap. Never bothered to learn it, never cared to! :p Figured I'd use a real database from the beginning.
The 2nd problem, I don't know T-SQL syntax very much either. Usually I write PHP scripts with the mssql_connect, mssql_query(), etc... of which the DBA usually does all this. I don't know why the hell the DBA doesn't just do this! LOL
I know enough about DB's to write apps for them, web based or stand alone, but I never claimed to be a DBA. If you have read my last post a while back, you probably already have an idea of how dysfunctional this entire IT department is (if you want to call it that).
So I am trying to find a quick and dirty way to go about this "problem". I'm going to have to learn T-SQL either way, but know Oracle and MySQL, so the basic SQL syntax shouldn't change much besides real specific things.
Has anyone worked much with Access and PHP in the past? Are there any scripts out there that you know that could get me started here?
I was thinking of just doing this in Ruby, C++, or Java, but figured I'd rather run this as a web app, and when I'm done, have a page on one of my sites that others can use to be spared of this in the future. I'll have to add to it too, so any query syntax (oracle, mysql, postrgresql, sqlite, t-sql, access) can be converted to any other syntax of those choices.
The only thing I really need to know is Access query syntax to at least get started...
You think any of you guys would be interested on working on a project to help build this, if I get a new project added on GitHub? Haven't had a chance to do a group project before, and thought it'd be fun one of these days... :p
In any case, thanks in advance for your input. 😃
