November 11, 2008 at 5:09 pm
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
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
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
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