I'm now working on a simple Training Record database. It's a bit of a downgrade from BI. It's a database with basically 3 tables:
Learners (=the employees in the company)
Courses
Participation (which learner took which course when).
My architecture was based on SQL server as the back end and MS-access as the front end. It makes the database robust and the front end design quick (though you have to pay a price for that, and about that in another post). Another tip is if you don't get the result set that you want running a query in MS-Access it's always easier to investigate the situation on the SQL itself.
The Participation requires the following fields: Learner, Course AND CPD, which is the number of points you get from this course. It could be less than the number of points that the Course acquires.
Sounds simple enough, doesn't it? Well, you'd be surprised how many problems can one encounter with a simple database like that.
Business asked for a simple request: when they see all the Learners, they want to see those without any courses as well. The problem is outlined below:
If you run the following query:
SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN dbo_Participation ON LearnerList.[id] = dbo_Participation.[Student]
WHERE (( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;
That gives me all the required participants, including those who has NULL as sumOfCPD
FullName SumOfCPD id
Amanda Smith
5.75 5
Amanda Jones 24.00 6
Catherine Smith
NULL 7
John Smith
NULL 34
BUT, if I try to manipulate it to a certain time range, those didn't take any courses (or in SQL: they're SumOfCPD is NULL) disappears:
SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON LearnerList.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;
FullName SumOfCPD id
Amanda Smith
5.75 5
Amanda Jones 24.00 6
If you move the condition of the "Where" clause back to the "Join" part, that sort out the problem:
SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD
FROM LearnerList LEFT JOIN dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id
AND (dbo.course.Date)<='20131115' And (dbo.course.Date)>='20130701')
ON dbo.student.[id] = dbo.Participation.[Student]
WHERE
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student;
Results:
FullName SumOfCPD id
Amanda Smith
5.75 5
Amanda Jones 24.00 6
Catherine Smith
NULL 7
John Smith
NULL 34
Running the above script in MS-Access would give me the "Error in the FROM clause". The above script run directly from the SQL.
Another option it to use the "NOT EXIST", which exist in MS-Access.
In this example, as I created the SQL as a string in the VBA engine, I didn't want to have the Date as a parameter of the JOIN, as I didn't know how Access would handle it (I wouldn't take a wild guess saying it wouldn't, would I?)
SELECT T1.FullName, T1.id
FROM LearnerList T1
WHERE NOT EXISTS
(SELECT T2.FullName,
Sum(dbo.Participation.CPD) AS SumOfCPD
FROM dbo.LearnerList T2 LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON T2.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND ((T2.id)=5 Or (T2.id)=6 Or (T2.id)=7 Or (T2.id)=34)
AND T1.id = T2.id
GROUP BY T2.LearnerList , dbo.Participation.Student)
AND
(T1.id = 5 OR T1.id = 6 OR t1.id = 7 OR t1.id = 34)
It's important to note that correctly, if run in SQL Server, you'd get the warning of:
Warning: Null value is eliminated by an aggregate or other SET operation.
For all of the above queries except for the "not exist" one.
In MS-Access, on the other hand, even creating a sub-query wouldn't help. You'll get and "ODBC - Call Error" just from the LEFT JOIN. In INNER JOIN you wouldn't get any error.
And last but not least: as this had to be read as a report, and when you do the following
Dim RS As New ADODB.Recordset
'Execute the code to set the RS
Set Reports("rpt_participation_summary").Recordset = RS
You get "You can do this type of action only in an ADP" I just used "SELECT...INTO "a table which is "temporary" on the SQL server. Ah, the joys of using Access! remind me NEVER, but NEVER to do it again!
