Help with Access 2003 Distinct values

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

Help with Access 2003 Distinct values

Author

Topic

Starchild26
Starting Member

1 Post

Posted - 2011-08-05 : 08:40:59

Really sorry if this is posted in the wrong place, but I am at my wits end.

Using Access 2003 I have a database that holds unique records for learners - Ethnicity, Learner_id (User) table and the courses that they have enroled on (Course_Information) table. These courses (Course) table are sperated into four Areas of Learning (Area of Learning) table, Vocational, Non-Vocational, Essential Skills and Employability.

I have to create a query that will count the number of unique learner codes that apper in a Course_Information Table, between two entered dates, looks up that learners Ethnicity Code from a User table and shows fo each Area of Learning how many learners of each ethnic code attend courses.

I am not an SQL programmer and I have managed to come up with the code below.

SELECT DISTINCTROW Ethnicity.Ethnic_Code, [Start] AS BeginDate, [End] AS EndingDate,

IIf(Area_Of_Learning.Area_Of_Learning='Vocational' ,Count(DISTINCT learner_id) AS Vocational FROM Course_Information,0),
IIf(Area_Of_Learning.Area_Of_Learning='Employabili ty',Count(DISTINCT learner_id) AS Employability FROM Course_Information,0),
IIf(Area_Of_Learning.Area_Of_Learning='Non-vocational',Count(DISTINCT learner_id) AS Non_vocational FROM Course_Information,0),
IIf(Area_Of_Learning.Area_Of_Learning='Essential Skills',Count(DISTINCT learner_id),0) AS Essential_Skills FROM Course_Information,0),

FROM Area_Of_Learning INNER JOIN (((Course_Information INNER JOIN Course ON Course_Information.Course_code = Course.Course_Code) INNER JOIN [User] ON Course_Information.Learner_id = User.Learner_ID) INNER JOIN Ethnicity ON User.Ethnicity = Ethnicity.Ethnic_Code) ON Area_Of_Learning.Area_Learning_ID = Course.Area_Learning_ID

WHERE (((Course_Information.Start_date) Between [Start] And [End]))

GROUP BY Ethnicity.Ethnic_Code, Course_Information.Establishment_ID, Area_Of_Learning.Area_Of_Learning

ORDER BY Ethnicity.Ethnic_Code;

When I run the query i get the Syntax error (missing operator) in query expression. and I do not know what to do to fix it.

I had the query running before , and the output seemed to count every learner entry in the Course_Information table.

Basically I might have a learner, Learner_id 123, Ethnic code W1 that might enrol on 5 Vocational and 5 Non Vocational courses, another Learner, Learner_id 456 Ethnic code A1 that might also enrol on 5 Vocational and 5 Employability Courses, I want the query to return result :

Between dates entered,

W1 Vocational = 1 Non Vocational =1
A1 Vocational = 1 Employability = 1

Please can anyone help me, I have been trying to get this working for a month or two.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources