Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    SQL SERVER FETCH (Access + SQL 2005)

    This might be the wrong forum to post this.
    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
    Andrew

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL SERVER FETCH (Access + SQL 2005)

    Yep

    Thanks Hans, it was the simple solution I didn't use that worked.
    I guess I was having one of those academic (I wonder if I can do it this way days).

    Silly thing, I had the query very similar in the VB solution, but I had just decided to
    solve the problem in a complicated way I guess.
    I think I need more sleep.

    Many thanks
    Andrew

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL SERVER FETCH (Access + SQL 2005)

    I don't know anything about SQL Server, but the following Access query returns the results you want:

    SELECT tblDates.Date, Sum(tblCourses.Attendees) AS TotalAttendees
    FROM tblCourses, tblDates
    WHERE tblDates.Date Between [Start] And [End]
    GROUP BY tblDates.Date;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •