I am using Access against a SQL 2005 database.
I have 3 Tables
1 Has a list of dates
2 Has a list of courses with start and end dates + attendees
3 Is required to get a list of attendees on each date
For example
1 tblDates might have dates for 10 11 12 and 13 Nov
e.g.
Date
09/11/06
10/11/06
11/11/06
12/11/06
2 tblCourses Has a list of courses
Course Start End Attendees
C1 09/11/06 12/11/06 8
C2 11/11/06 14/11/06 6
C3 09/11/06 11/11/06 5
List 3 tblDailyAttendance needs a total for each date in 1
Date Attendees
09/11/06 13
10/11/06 13
11/11/06 19
12/11/06 14
etc
I have a solution using VBA that loops recordsets to build the attendance list that works ok, but really I am after 1 of 2 things that could be run
ideally on the SQL SERVER rather that on the linked access database, because eventually the database will move to a project
Can anyone think of a query/queries that would solve this problem without the need to result to VBA Recordsets
OR can anyone pont me to a solution using TSQL CURSORS
I had a go at this on the server, and everything compiled OK, but it only returned ONE row
I obviously got some bit of logic wrong.
Maybe I cannot Nest the Cursor Loops as I have done (Coming from a programming background it seemed logical to me)
For interest or help, the basic TSQL procedure I tried is below
USE training
GO
— Declare the variables to store the values returned by FETCH.
DECLARE @Date smalldatetime,@Start smalldatetime, @End SmallDatetime
DECLARE @Places int
DECLARE @ADate smalldatetime
DECLARE @DayFetch int, @CourseFetch int
— Set Up Courses Summary Cursor
DECLARE coursecursor CURSOR FOR
SELECT crs_CourseDate,crs_FinishDate,Delegates FROM dbo.vue_CourseSummary
— Set Up Dates Table Cursor
DECLARE daycursor CURSOR FOR
SELECT cat_Date FROM tbl_Dates
OPEN daycursor
FETCH NEXT FROM dayCursor INTO @Date
SET @DayFetch=@@FETCH_STATUS
WHILE @DayFetch = 0
BEGIN
— Open the courses cursor each day and close at the end
OPEN coursecursor
— Get Data from First Record in Cursor
FETCH NEXT FROM coursecursor INTO @Start, @End, @Places
SET @CourseFetch=@@FETCH_STATUS
WHILE @CourseFetch = 0
BEGIN
— Check for Dates inside Range
IF @Start >= @Date AND @End <= @Date
BEGIN
-- Write the Data into the Daily table
INSERT INTO tbl_DailyAttendance
(cdt_Date,cdt_Places)
SELECT @Date,@Places
END
-- Get Next Course to Check, loop until No more records
FETCH NEXT FROM coursecursor INTO @Start, @End, @Places
SET @CourseFetch=@@FETCH_STATUS
END
-- Get Next Day from date list
FETCH NEXT FROM dayCursor INTO @Date
SET @DayFetch=@@FETCH_STATUS
-- Close Courses Cursor at end of the courses loop ready for next day
CLOSE coursecursor
END
DEALLOCATE coursecursor
CLOSE daycursor
DEALLOCATE daycursor
Any help or pointers to where I might find a solution would be gratefully accepted
