Post reply

Count Problem

  • November 11, 2008 at 5:09 pm

    #77133

    I am trying to do a count of all tests and all sessons where all the data for test and sesions is stored in one field.

    RESULTS SHOULD BE:

    Total_October_Tests Total_October_Sessions

    2 2

    BUT I AM GETTING THIS AS THE RESULT INSTEAD:

    Total_October_Tests Total_October_Sessions

    2 0

    2 2

    3 2

    SELECT DISTINCT (SELECT COUNT (Lesson_CatId)

    FROM Session_Information

    WHERE Lesson_CatId = "001" OR Lesson_CatId ="003" OR Lesson_CatId = "004" OR Lesson_CatId = "005"

    AND Session_Information.ScheduleDate_Id=Lesson_DateSchedule.ScheduleDate_Id

    AND [Lesson_DateSchedule].[Lesson_Date] BETWEEN #10/01/08# AND #10/31/08#) AS Total_October_Tests, (SELECT DISTINCT COUNT(Lesson_CatId)

    FROM Session_Information

    WHERE Lesson_CatId="002"

    AND [Lesson_DateSchedule].[Lesson_Date] BETWEEN #10/01/08# AND #10/31/08#) AS Total_October_Sessions

    FROM Session_Information, Lesson_DateSchedule;

  • November 11, 2008 at 5:32 pm

    #897234

    Probably like this:

    SELECT DISTINCT (

    SELECT COUNT (Lesson_CatId)

    FROM Session_Information, Lesson_DateSchedule

    WHERE Lesson_CatId = "001" OR Lesson_CatId ="003" OR Lesson_CatId = "004" OR Lesson_CatId = "005"

    AND Session_Information.ScheduleDate_Id=Lesson_DateSchedule.ScheduleDate_Id

    AND [Lesson_DateSchedule].[Lesson_Date] BETWEEN #10/01/08# AND #10/31/08#

    ) AS Total_October_Tests

    , (

    SELECT DISTINCT COUNT(Lesson_CatId)

    FROM Session_Information, Lesson_DateSchedule

    WHERE Lesson_CatId="002"

    AND [Lesson_DateSchedule].[Lesson_Date] BETWEEN #10/01/08# AND #10/31/08#

    ) AS Total_October_Sessions

    ;

    Can't say that I like it though.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • November 12, 2008 at 3:02 am

    #897393

    I tried it however I am getting this error:

    Reserved error (-3025); there is no message for this error.

    Ho can I resolve this issue?

  • November 12, 2008 at 7:17 am

    #897498

    Right, sorry forgot about this restriction of Access:

    SELECT DISTINCT (

    SELECT COUNT (Lesson_CatId)

    FROM Session_Information, Lesson_DateSchedule

    WHERE Lesson_CatId = "001" OR Lesson_CatId ="003" OR Lesson_CatId = "004" OR Lesson_CatId = "005"

    AND Session_Information.ScheduleDate_Id=Lesson_DateSchedule.ScheduleDate_Id

    AND [Lesson_DateSchedule].[Lesson_Date] BETWEEN #10/01/08# AND #10/31/08#

    ) AS Total_October_Tests

    , (

    SELECT DISTINCT COUNT(Lesson_CatId)

    FROM Session_Information, Lesson_DateSchedule

    WHERE Lesson_CatId="002"

    AND [Lesson_DateSchedule].[Lesson_Date] BETWEEN #10/01/08# AND #10/31/08#

    ) AS Total_October_Sessions

    FROM Session_Information;

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply