SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Threaded View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please Help Me Pull this off

    hello Gentlemen,

    i am currently at a cross road, i have to pull off a report for a university, with all students and their registration number in a row and all the courses they have registered in a session as columns. Now, the interesting thing is that as the rows increase horizontally downwards with the list of students and their registration numbers in a class, the columns increase vertically simultaneously with the courses that was registered, the course units and marks obtained.

    please see schema of tables with explanation below:

    institution_Courses is where all university courses are held
    institution_programme is where all the programmes the university offers are stored
    institution_PersonalData is where all the student profiles are held
    Institution_Academicprofile is where all the academic profiles of the students are held
    Institution_Programme_Courses is where all the courses a programme offeres are held i.e computer science offers csc 101, csc 112, etc...
    Institution_Programme_Course_Registration is where all student course registrations are stored across different sessions



    CREATE TABLE [dbo].[Institution_Courses](
    [CourseId] [int] IDENTITY(1,1) NOT NULL,
    [CourseCode] [varchar](20) NULL,
    [courseName] [varchar](150) NULL,
    [Activated] [bit] NULL,
    CONSTRAINT [PK_Institution_Courses] PRIMARY KEY CLUSTERED
    (
    [CourseId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    CREATE TABLE [dbo].[Institution_Programme](
    [ProgrammeId] [int] IDENTITY(1,1) NOT NULL,
    [ProgrammeTypeId] [int] NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [CertificateId] [int] NULL,
    [ProgrammeName] [varchar](500) NULL,
    [StartLevel] [varchar](50) NULL,
    [EndLevel] [varchar](50) NULL,
    [Duration] [int] NULL,
    [UnitsRequired] [int] NULL,
    [Activated] [bit] NULL,
    [categoryid] [int] NULL,
    CONSTRAINT [PK_Institution_Programme] PRIMARY KEY CLUSTERED
    (
    [ProgrammeId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_PersonalData](
    [PersonalId] [int] IDENTITY(1,1) NOT NULL,
    [SessionId] [int] NOT NULL,
    [Surname] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [DateofBirth] [datetime] NULL,
    [PlaceofBirth] [varchar](50) NULL,
    [Sex] [char](1) NULL,
    [Religion] [int] NULL,
    [MaritalStatus] [int] NULL,
    [PhoneNumber] [varchar](50) NULL,
    [EmailAddress] [varchar](50) NULL,
    [NationalityId] [int] NULL,
    [StateofOrigin] [int] NULL,
    [LGA] [int] NULL,
    [HomeTown] [varchar](50) NULL,
    [RefCode] [uniqueidentifier] NOT NULL,
    [DateFilled] [datetime] NULL,
    [PictureURL] [varchar](100) NULL,
    [StatusId] [int] NOT NULL,
    [contact_address] [varchar](max) NULL,
    [ModeOfEntry] [varchar](50) NULL,
    CONSTRAINT [PK_Institution_PersonalData] PRIMARY KEY CLUSTERED
    (
    [PersonalId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    CREATE TABLE [dbo].[Institution_AcademicProfile](
    [AcademicDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [PersonalId] [int] NOT NULL,
    [MatricNo] [varchar](50) NULL,
    [ProgrammeId] [int] NULL,
    [CurrentLevel] [int] NULL,
    [CurrentSession] [int] NULL,
    [EntryRegNo] [varchar](50) NULL,
    [AcademicStatusId] [int] NULL,
    [categoryid] [int] NULL,
    [NDGPA] [varchar](10) NULL,
    [HNDGPA] [varchar](10) NULL,
    PRIMARY KEY CLUSTERED
    (
    [AcademicDetailsId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_Programme_Courses](
    [ProgrammeCourseId] [int] IDENTITY(1,1) NOT NULL,
    [ProgrammeId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [CourseTypeId] [int] NOT NULL,
    [CourseUnit] [int] NOT NULL,
    [SemesterId] [int] NOT NULL,
    [LevelId] [int] NOT NULL,
    [Activated] [bit] NULL,
    [pass_mark] [numeric](18, 2) NULL,
    CONSTRAINT [PK_Institution_Programme_Courses] PRIMARY KEY CLUSTERED
    (
    [ProgrammeCourseId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_Programme_Course_Registration](
    [CourseRegId] [int] IDENTITY(1,1) NOT NULL,
    [PersonalId] [int] NOT NULL,
    [ProgrammeId] [int] NOT NULL,
    [LevelId] [int] NOT NULL,
    [SemesterId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [SessionId] [int] NOT NULL,
    [DateReg] [datetime] NULL,
    [Approved] [bit] NULL,
    [DateApproved] [datetime] NULL,
    [TestScore] [decimal](18, 2) NULL,
    [ExamScore] [decimal](18, 2) NULL,
    [Total] [decimal](18, 2) NULL,
    [DateRecorded] [datetime] NULL,
    [CourseUnit] [int] NULL,
    [CourseType] [varchar](20) NULL,
    CONSTRAINT [PK_Institution_Programme_Course_Registration] PRIMARY KEY CLUSTERED
    (
    [CourseRegId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    Please help as i am at a cross road.

    also attached is a sample of the report to be produced

    Thanks Gurus.
    Attached Files Attached Files


Bookmarks

Posting Permissions

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