#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    4
    Rep Power
    0

    Deperate need of help for Access 2003 queryNewbie


    Really sorry if this is posted in the wrong place, but I a 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    72
    Originally Posted by Starchild26
    Really sorry if this is posted in the wrong place, but I a 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


    Hi,

    assumed rough structure below

    User
    Learner_ID - PK
    Ethnicity - FK on Ethnicity.Ethnic_Code


    Course_Information
    Course_Code - FK on Course.Course_Code
    Learner_ID - FK on User.Learner_ID
    Start_Date
    End_Date
    Establishment_ID


    Course
    Course_Code - PK
    Area_Learning_ID - FK on Area_of_Learning.Area_Learning_ID


    Area_of_Learning
    Area_Learning_ID - PK
    Area_of_Learning


    Ethnicity
    Ethnic_Code - PK
    Ethnicity

    based on above structure assumption, just check out if below gives some guidelines

    Code:
    SELECT 
    b.Ethnicity, 
    b.Area_Of_Learning, 
    Count(b.Learner_ID) AS CountOfLearner_ID
    FROM 
    (
    SELECT 
    Ethnicity.Ethnicity, 
    Area_Of_Learning.Area_Of_Learning, 
    User.Learner_ID
    FROM 
    Ethnicity 
    INNER JOIN 
    (
    Area_Of_Learning 
    INNER JOIN 
    (
    (
    Course 
    INNER JOIN 
    (
    SELECT 
    Course_Information.Course_code, 
    Course_Information.Learner_id, 
    Course_Information.Start_date, 
    Course_Information.Establishment_ID
    FROM 
    Course_Information
    WHERE (((Course_Information.Start_date) Between #4/1/2011# And #8/1/2011#))
    )
    AS 
    a 
    ON 
    Course.Course_Code = a.Course_code
    ) 
    INNER JOIN 
    [User] 
    ON 
    a.Learner_id = User.Learner_ID
    ) 
    ON 
    Area_Of_Learning.Area_Learning_ID = Course.Area_Learning_ID
    ) 
    ON 
    Ethnicity.Ethnic_Code = User.Ethnicity
    GROUP BY 
    Ethnicity.Ethnicity, 
    Area_Of_Learning.Area_Of_Learning, 
    User.Learner_ID
    )
    AS 
    b
    GROUP BY b.Ethnicity, b.Area_Of_Learning;
    In case not helpful, suggest posting table structures with sample data thrown in and the final output you want from the query for the posted tables data. I am sure someone should definitely be able to help.

    Thanks
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    4
    Rep Power
    0

    Talking It is working!!!!


    Hi recyan

    Thanks very much for this, I have tweaked the code to reflect the correct table fields, added the ability to enter a start and end date, and I seem to be getting the result I wanted,

    Thank you very much for your help.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    72
    Originally Posted by Starchild26
    I have tweaked the code to reflect the correct table fields, added the ability to enter a start and end date, and I seem to be getting the result I wanted,
    Glad you found it helpful.

    Thanks
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    4
    Rep Power
    0

    Question Would you be able to advise


    Originally Posted by recyan
    Glad you found it helpful.

    Thanks
    Your help really was fantastic, Can I just ask a quick question,

    Using your code, how could I have the figures returned by the query for each area of learning used in a report.

    SELECT b.Ethnic_Code, b.Area_Of_Learning, Count(b.Learner_ID) AS CountOfLearner_ID, [Start] AS Start, [End] AS End
    FROM [SELECT
    Ethnicity.Ethnic_Code,
    Area_Of_Learning.Area_Of_Learning,
    User.Learner_ID
    FROM
    Ethnicity
    INNER JOIN
    (
    Area_Of_Learning
    INNER JOIN
    (
    (
    Course
    INNER JOIN
    (
    SELECT
    Course_Information.Course_code,
    Course_Information.Learner_id,
    Course_Information.Start_date,
    Course_Information.Establishment_ID
    FROM
    Course_Information
    WHERE (((Course_Information.Start_date) Between [Start] And [End]))
    )
    AS
    a
    ON
    Course.Course_Code = a.Course_code
    )
    INNER JOIN
    [User]
    ON
    a.Learner_id = User.Learner_ID
    )
    ON
    Area_Of_Learning.Area_Learning_ID = Course.Area_Learning_ID
    )
    ON
    Ethnicity.Ethnic_Code = User.Ethnicity
    GROUP BY
    Ethnicity.Ethnic_Code,
    Area_Of_Learning.Area_Of_Learning,
    User.Learner_ID
    ]. AS b
    GROUP BY b.Ethnic_Code, b.Area_Of_Learning, [Start], [End]
    ORDER BY b.Area_Of_Learning DESC;


    The query returns the numbers for each area of learning, but how would I modify the code to output a figure defined as Vocational, Non-Vocational, Essential Skills and Employability.

    Below is an example of how I would like to use the query's output.

    Start date - 01/04/11, End Date - 30/06/11

    Voc NVoc Ess Emp
    A1 2 5 0 1
    A6 4 7 10 3
    W1 0 20 10 3

    Thanks
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    72
    Originally Posted by Starchild26
    Your help really was fantastic, Can I just ask a quick question,

    Using your code, how could I have the figures returned by the query for each area of learning used in a report.

    SELECT b.Ethnic_Code, b.Area_Of_Learning, Count(b.Learner_ID) AS CountOfLearner_ID, [Start] AS Start, [End] AS End
    FROM [SELECT
    Ethnicity.Ethnic_Code,
    Area_Of_Learning.Area_Of_Learning,
    User.Learner_ID
    FROM
    Ethnicity
    INNER JOIN
    (
    Area_Of_Learning
    INNER JOIN
    (
    (
    Course
    INNER JOIN
    (
    SELECT
    Course_Information.Course_code,
    Course_Information.Learner_id,
    Course_Information.Start_date,
    Course_Information.Establishment_ID
    FROM
    Course_Information
    WHERE (((Course_Information.Start_date) Between [Start] And [End]))
    )
    AS
    a
    ON
    Course.Course_Code = a.Course_code
    )
    INNER JOIN
    [User]
    ON
    a.Learner_id = User.Learner_ID
    )
    ON
    Area_Of_Learning.Area_Learning_ID = Course.Area_Learning_ID
    )
    ON
    Ethnicity.Ethnic_Code = User.Ethnicity
    GROUP BY
    Ethnicity.Ethnic_Code,
    Area_Of_Learning.Area_Of_Learning,
    User.Learner_ID
    ]. AS b
    GROUP BY b.Ethnic_Code, b.Area_Of_Learning, [Start], [End]
    ORDER BY b.Area_Of_Learning DESC;


    The query returns the numbers for each area of learning, but how would I modify the code to output a figure defined as Vocational, Non-Vocational, Essential Skills and Employability.

    Below is an example of how I would like to use the query's output.

    Start date - 01/04/11, End Date - 30/06/11

    Voc NVoc Ess Emp
    A1 2 5 0 1
    A6 4 7 10 3
    W1 0 20 10 3

    Thanks
    Hi,
    Don't know if you are already thro with this. In case not you can check out on crosstab / pivot queries.

    Just as an illustration as to how it works with the query I posted before ( the results might not be in line with what you want ):

    TRANSFORM Count(p.CountOfLearner_ID) AS CountOfCountOfLearner_ID
    SELECT p.Ethnicity
    FROM
    (
    .... the previous query goes here ....
    )
    AS p
    GROUP BY p.ethnicity
    PIVOT p.Area_Of_Learning

    Thanks
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    4
    Rep Power
    0

    Smile Almost there thanks to you


    Originally Posted by recyan
    Hi,
    Don't know if you are already thro with this. In case not you can check out on crosstab / pivot queries.

    Just as an illustration as to how it works with the query I posted before ( the results might not be in line with what you want ):

    TRANSFORM Count(p.CountOfLearner_ID) AS CountOfCountOfLearner_ID
    SELECT p.Ethnicity
    FROM
    (
    .... the previous query goes here ....
    )
    AS p
    GROUP BY p.ethnicity
    PIVOT p.Area_Of_Learning

    Thanks
    Hi recyan,

    Again thanks so much for your help.

    I have done what you have suggested, and had to make a few minor changes:

    TRANSFORM Count(p.CountOfLearner_ID) AS CountOfCountOfLearner_ID
    SELECT Ethnic_Code
    FROM qryEthnicity AS p
    GROUP BY Ethnic_Code
    PIVOT p.Area_Of_Learning;

    And it works, fantastic, but now I get a value of 1 for each etnic code ( which I assume is right, as we have asked it to count the occurence).
    How can I get this query to display the value returned by the original query for eac ethnic code by the area of learning.

    This new query does everything I need it to, but instead of counting (which in my case returns a value of 1) it displays the value retuned by the original query ( which for Vocational W1 returned 102.

    I know I am being a pain, but I have had a go and my limited know how is a problem.

    Once I have with your help sorted this out, I have to get another query I have, that requires the same type of output but instead of Ethnic code returns numbers of learners by Age Range between two entered dates, counting the learner only once for each Area of Learning.

    Cheekily I have added this code below:

    SELECT DET.Establishment_ID, DET.AgeBand, Sum(DET.Vocational) AS Vocational_, Sum(DET.Employability) AS Employability_, Sum(DET.Non_vocational) AS Non_Vocational_, Sum(DET.Essential_Skills) AS Essential_skills_, [Start Date] AS Begin, [End Date] AS Ending
    FROM (SELECT

    Course_Information.Establishment_ID, User.Learner_ID, DateDiff("yyyy",[Date_Of_Birth],Now()) AS StudentAge
    ,Switch((DateDiff("yyyy",[Date_Of_Birth],Now()))>=18 And (DateDiff("yyyy",[Date_Of_Birth],Now()))<22,"18 to 21",
    (DateDiff("yyyy",[Date_Of_Birth],Now()))>=22 And (DateDiff("yyyy",[Date_Of_Birth],Now()))<=25,"22 to 25",(DateDiff("yyyy",[Date_Of_Birth],Now()))>=26 And (DateDiff("yyyy",

    [Date_Of_Birth],Now()))<31,"26 to 30",(DateDiff("yyyy",[Date_Of_Birth],Now()))>=31,"31+") AS AgeBand

    ,iif(Area_Of_Learning.Area_Of_Learning ='Vocational' ,count(Course_Information.learner_id),0) AS Vocational
    ,iif (Area_Of_Learning.Area_Of_Learning = 'Employability' ,count(Course_Information.learner_id),0) AS Employability
    ,iif(Area_Of_Learning.Area_Of_Learning = 'Non-vocational' ,count(Course_Information.learner_id),0) AS Non_vocational
    ,iif(Area_Of_Learning.Area_Of_Learning ='Essential Skills' ,count(Course_Information.learner_id),0) AS Essential_Skills



    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

    [Start Date] and [End Date]

    Group by

    Course_Information.Establishment_ID
    ,User.Learner_ID
    ,USER.Date_Of_Birth
    , Area_Of_Learning.Area_Of_Learning


    ). AS DET
    GROUP BY DET.Establishment_ID, DET.AgeBand;

    Again thanks very much for your help with this.

    M
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    72
    Originally Posted by Starchild26
    Hi recyan,
    .......

    TRANSFORM Count(p.CountOfLearner_ID) AS CountOfCountOfLearner_ID
    SELECT Ethnic_Code
    FROM qryEthnicity AS p
    GROUP BY Ethnic_Code
    PIVOT p.Area_Of_Learning;

    ..... but now I get a value of 1 for each etnic code ( which I assume is right, as we have asked it to count the occurence).
    Have you tried ( again just as an illustration)

    TRANSFORM MAX(p.CountOfLearner_ID) AS
    CountOfCountOfLearner_ID
    ....................

    Take a look at below link. You might perhaps find it useful.

    http://allenbrowne.com/ser-67.html

    Pls note :
    Under pressure of finishing a task, we might tend to ignore certain basic things and just focus on getting the results.
    But you can be sure that it will come back later to haunt you.
    After finishing this particular task, would suggest, you take a re-look at things. You may even find out that we have unnecessarily complicated things or might have ignored certain things which will create problems later.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo