SQL Server Performance

Help for SQL query

Discussion in 'T-SQL Performance Tuning for Developers' started by dream85, Jun 28, 2005.

  1. dream85 New Member

    Need help for a sql query to select info from database.
    The situation is like this:

    select Select [Student Academic Records].AdminNo, SUM(Credits) as CM, SUM(Credits) as PE From [Student Particulars], [Student Modules], [Student Academic Records] Where [Student Academic Records].AdminNo Like '%" & txtSearch.Text & "%' AND [Student Academic Records].AdminNo = [Student Particulars].AdminNo AND [Student Academic Records].MCode = [Student Modules].MCode AND [Student Modules].MType LIKE '%" & "CM" & "%' GROUP by [Student Academic Records].AdminNo,[Student Particulars].FullName HAVING SUM(Credits) > " & credit.text & " "


    I have a table call student modules with a column call MTYPE, which specify if the module is a core module or PE.
    I have to calculate the sum of the credits for Core module as well as PE.
    But i have problem as in the sqlstatement as "WHERE" -> which can only check if the MTYPE is CM.
    Meaning i can't put Where MTYPE LIKE CM AND MTYPE LIKE PE
    And after the "GROUP BY" i try to put "HAVING SUM(Credits) > textbox1.text". Can i replace the SUM(Credits) with CM so that i can check both CM and PE?
    Thanks
    PS: i using asp.net and SQL server
  2. ghemant Moderator

    hi,
    please use where condition as :
    where mtype in ('CM','PE') and for would you pleas put some sample data please.



    Regards.

    hsGoswami
    ghemant@gmail.com
  3. dream85 New Member

    http://www.geocities.com/kennethtiang/untitled.JPG
    This will be the screenshot of how it should looks like...

    The datagrid will display the sum of credits of CM and sum of credits of PE

    And i have to use sqlstatement to calculate the total credits of CM and total credits of PE, and also by checking them if they met the number which the user type into 2 textboxs.
    Meaning textbox1 will have a value of 50, so the sql will check if the total credits of CM exceeds 50, if not will not display the record.
    this is also the same for PE.

    So, how do i check by using "WHERE"
    "Where MTYPE = "CM", Where MTYPE = "PE" -> checking 2 conditions?
    After that is "HAVING Sum(Credits)[CM] > textbox1, Sum(Credits)[PE] > textbox2"

    Regards,
    dream85
  4. FrankKalis Moderator

    Sorry, but is this some kind of assigned homework to you? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  5. dream85 New Member

    Doing Final Year Project for my school in SG
  6. FrankKalis Moderator

    Hm, shouldn't you be able to solve this yourself? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Anyway, you've posted what your result should look like. Can you post your table structure and sample data?<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  7. dream85 New Member

    Table structure as in my database structure?<br /><br />If yes, i need to get info from 3 tables for this datagrid.<br /><br />Student Modules - ModuleCode, Name, MType(ModuleType), Credits...etc<br />Student Academic Records - AdminNo, ModuleCode, ....etc<br /><br />Sample data... hm.... ok i make a example out of this..<br />first example i enter AdminNo (xxx), CM (50), PE(10)<br /><br />The sql will look in Student Academic Records for the AdminNo similar to xxx.<br />After retrieved out the records, then it will use the modulecode under the adminNo to compare with those in the student Module table to check for the credits (Also check if the module is CM or PE type). Then will calculate the total of credits for CM Modules, And total of credits for PE modules. In the end then compare with the data i enter whether the credits for CM is over 50 and whether the PE is over 10.<br />Argh, quite hard to explain.<br /><br />"Select AdminNo, Sum(Credits) as CM, Sum(Credits) as PE FROM [Student Academic Records], [Student Modules] WHERE <br />// "This part i try to check both condition. Cause i have to select [Sum(Credits)] as CM and [Sum(Credits)] as PE, how should i put in a way "WHERE Sum(Credits) LIKE "CM" and Sum(Credits) Like PE".<br /><br />Cause both CM and PE using same column as "MTYPE", i can't seperate them.<br /><br />Regards,<br />Dream85<br /><br />(If i could solve them myself, i won't post here [<img src='/community/emoticons/emotion-2.gif' alt=':D' />])
  8. FrankKalis Moderator

  9. dream85 New Member

    Saw the page you referred me to, but different version of SQL server will have different type of look right? I using SQL Server 2003.
    I try to generate sql script for the tables.

    CREATE TABLE [dbo].[Student Academic Records] (
    [AdminNo] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [MCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MGrade] [float] NULL ,
    [TGroup] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MRepeat] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MStartDate] [datetime] NULL ,
    [MEndDate] [datetime] NULL ,
    [Programme] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Period] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ModuleGrade] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Student Modules] (
    [MCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OldMCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MDesc] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MStage] [int] NULL ,
    [MType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MCategory] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TrainingHours] [int] NULL ,
    [Credits] [int] NULL
    ) ON [PRIMARY]
    GO


    but about the sample data, how should i provide them?
    Sorry that i'm new to this, sry for the trouble.

    regards,
    dream85
  10. ghemant Moderator

    the very first thing is that you have to follow the naming conventions while creating tables dont keep spaces between two words use _ instead. if you keep space it wont recognize it.


    i have try using some sample given by you... HTH
    use northwind
    create table t1
    (
    dyear varchar(20),
    semester varchar(20),
    vadminvarchar(20),
    mentorgroupvarchar(20),
    cmint,
    peint
    )

    alter table t1 add mtype varchar(5)

    truncate table t1

    insert into t1 values ('2003','s1','030968f','ei0315',26,2,'cm')
    insert into t1 values ('2003','s2','030968f','ei0315',26,2,'pe')
    insert into t1 values ('2004','s1','030968f','ei0315',26,2,'cm')
    insert into t1 values ('2005','s2','030968f','ei0315',26,2,'pe')



    select vadmin,dyear, SUM(cm) as CM, SUM(pe) as PE From t1
    where t1.mtype in ('cm','pe')
    group by vadmin,dyear



    hsGoswami
    ghemant@gmail.com
  11. FrankKalis Moderator

    I would suspect the grouping to be


    select vadmin,semester,dyear, SUM(cm) as CM, SUM(pe) as PE From t1
    where t1.mtype in ('cm','pe')
    group by dyear, semester, vadmin


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. ghemant Moderator

    Frank,
    thanx for editing me.

    Regards.

    hsGoswami
    ghemant@gmail.com
  13. FrankKalis Moderator

    Btw, this is now only one table. However, when you say you have three base tables, the query is basically the same, just extended for some joins.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  14. dream85 New Member

    Frankalis you are nearly there for what i wanted actually.

    You given this sqlstatement
    select vadmin,semester,dyear, SUM(cm) as CM, SUM(pe) as PE From t1where t1.mtype in ('cm','pe')group by dyear, semester, vadmin


    The only problem now is that i don't have SUM(cm) and Sum(pe)
    that's why i am thinking if there is such a possibility

    select vadmin,semester,dyear, SUM(credits) as CM, SUM(credits) as PE From t1where t1.mtype in ('cm','pe')group by dyear, semester, vadmin

    as you can see i have credit as a column, and in the rows it is specify as either CM or PE.

    And Where t1.mtype in ('cm', 'pe') -> what does this mean actually?

    Thanks for taking the trouble to help me.
    Regards,
    Dream85
  15. ghemant Moderator

    Hi Kenneth,<br />t1.mtype in('cm','pe') will gives you a result from various mtype for example you have to search results from field mtype that has values like 'cm' , 'pe','pp','oe' and out of that you have to find more then one values containing records and dont wont to use multiple 'or' then you have to use t1.mtype in('cm','pe'). <br /><br />and if you can provide some sample data we can do more for you.<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards.<br /><br />hsGoswami<br />ghemant@gmail.com
  16. dream85 New Member

    Sample Data?
    You mean the data i stored in my database?
    So i just need to provide a sample of a row of data from my database?
    such as
    AdminNo = 015111B
    MCode = IT1510
    Credit = 5
    and carry on?.....
  17. dream85 New Member

    Actually there's one more way to do it but i don't know whether SQL Server can do it anot.
    Another way is to create one more table in my database with columns AdminNo, CM, PE
    I can fill in the AdminNo myself.
    But for the CM and PE columns, can we set a formulae for it to calculate itself?
    I remember Microsoft Access can do it. The method is like Excel which we input the formula (example =sum(C1+C2))

    Is there a way to do it in SQL Server?

    Regards,
    Dream85
  18. FrankKalis Moderator

    I think, what comes near the most are computed columns. You might want to look them up in the SQL Server's Books Online.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  19. dream85 New Member

    Sorry to ask another question...
    How to input the codes for the creating of table?
    As i am using Microsoft SQL Server, it seems like i can't find any screen to input the sql codes.

    I can only create new table by design view, not the sql view.
    meaning they only let me enter the column name, data type and constraint.

    Regards,
    Dream85
  20. FrankKalis Moderator

    Don't use the Enterprise Manager. Use Query Analyzer instead. Gives you more control over what you're doing.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  21. dream85 New Member

    Can i get data from other tables to calculate and put the results in the column?

    CREATE TABLE [dbo].[Student MCredits]
    (
    AdminNo char [50] NOT NULL,
    CM int NOT NULL,
    PE AS (c1 + c2) / 2
    )


    // For the CM, i wanted to first compare the AdminNo from this table to another table, so that i can get the credits from another table. The sequence is like this...

    [Student MCredits]
    AdminNo CM PE
    xxxxxx

    [Student academic record]
    AdminNo MCode
    xxxxxx aaaaa

    [Student Modules]
    MCode MType Credits
    aaaaa CM 5


    First with the admin number we find the adminNo in [Student Academic Record] so we can take out the aaaaa. After taken out aaaaa we use it to search for MType and credits in [Student Modules] so that we know what is the MTYPE and CREDITS then we can add them into the CM column in [Student Mcredits]

    Is this possible? Using computed column?

    Regards,
    Dream85
  22. ghemant Moderator

    you can get data from another table to insert...

    and if possible post some sample data for reference , and if you dont know how to do it open
    Enterprise Manager -> Select your server -> select database ('your database) right click -> all task - > generate sql script -> click on General -> show all -> select script all object -> select generate the create <object> command for each object
    generate the drop <object> command for each object
    generate scripts for all dependent objects
    then goto options and select all checkbox and then click on okay : it will generate SQL Script , and hope now you can provide your db structure and sample data.

    Regards.

    hsGoswami
    ghemant@gmail.com
  23. dream85 New Member

    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ICare')<br />DROP DATABASE [ICare]<br />GO<br /><br />exec sp_dboption N'ICare', N'autoclose', N'false'<br />GO<br />exec sp_dboption N'ICare', N'bulkcopy', N'false'<br />GO<br />exec sp_dboption N'ICare', N'trunc. log', N'false'<br />GO<br />exec sp_dboption N'ICare', N'torn page detection', N'true'<br />GO<br />exec sp_dboption N'ICare', N'read only', N'false'<br />GO<br />exec sp_dboption N'ICare', N'dbo use', N'false'<br />GO<br />exec sp_dboption N'ICare', N'single', N'false'<br />GO<br />exec sp_dboption N'ICare', N'autoshrink', N'false'<br />GO<br />exec sp_dboption N'ICare', N'ANSI null default', N'false'<br />GO<br />exec sp_dboption N'ICare', N'recursive triggers', N'false'<br />GO<br />exec sp_dboption N'ICare', N'ANSI nulls', N'false'<br />GO<br />exec sp_dboption N'ICare', N'concat null yields null', N'false'<br />GO<br />exec sp_dboption N'ICare', N'cursor close on commit', N'false'<br />GO<br />exec sp_dboption N'ICare', N'default to local cursor', N'false'<br />GO<br />exec sp_dboption N'ICare', N'quoted identifier', N'false'<br />GO<br />exec sp_dboption N'ICare', N'ANSI warnings', N'false'<br />GO<br />exec sp_dboption N'ICare', N'auto create statistics', N'true'<br />GO<br />exec sp_dboption N'ICare', N'auto update statistics', N'true'<br />GO<br />use [ICare]<br />GO<br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Student Academic Records_Student Modules]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)<br />ALTER TABLE [dbo].[Student Academic Records] DROP CONSTRAINT FK_Student Academic Records_Student Modules<br />GO<br /><br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Student Specialization_Student Particulars]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)<br />ALTER TABLE [dbo].[Student Specialization] DROP CONSTRAINT FK_Student Specialization_Student Particulars<br />GO<br /><br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student Academic Records]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />drop table [dbo].[Student Academic Records]<br />GO<br /><br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Attendence]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />drop table [dbo].[Attendence]<br />GO<br /><br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student Modules]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />drop table [dbo].[Student Modules]<br />GO<br /><br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student Particulars]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />drop table [dbo].[Student Particulars]<br />GO<br /><br />if not exists (select * from master.dbo.syslogins where loginname = ')<br />exec sp_grantlogin N''<br />exec sp_defaultdb N''<br />exec sp_defaultlanguage N'', N'us_english'<br />GO<br /><br />if not exists (select * from dbo.sysusers where name =' and uid &lt; )<br />EXEC sp_grantdbaccess ', '<br />GO<br /><br />if not exists (select * from dbo.sysusers where name = ' and uid &lt; )<br />EXEC sp_grantdbaccess '<br />GO<br /><br />exec sp_addrolemember N'db_owner', '<br />GO<br /><br />CREATE TABLE [dbo].[Attendence] (<br />[Admin_No] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Mcode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Absent] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Semester] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Year] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL <br />) ON [PRIMARY]<br />GO<br /><br />CREATE TABLE [dbo].[Student Modules] (<br />[MCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />[OldMCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MDesc] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MStage] [int] NULL ,<br />[MType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MCategory] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TrainingHours] [int] NULL ,<br />[Credits] [int] NULL <br />) ON [PRIMARY]<br />GO<br /><br />CREATE TABLE [dbo].[Student Particulars] (<br />[AdminNo] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />[FirstName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[LastName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[FullName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Nric] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[BlockNo] [int] NULL ,<br />[StreetName] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[City] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[State] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Country] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[PostalCode] [bigint] NULL ,<br />[Tel] [bigint] NULL ,<br />[HpNo] [bigint] NULL ,<br /> [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Citizenship] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[DOB] [datetime] NULL ,<br />[HighSchool] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Religon] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[AcdStream] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[PR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Race] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[OLevel] [int] NULL ,<br />[PreU] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[NsStatus] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Gender] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[POB] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TBlockNo] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TStreetName] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TCity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TState] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TCountry] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TPostalCode] [char] (<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TTel] [char] (<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[THpNo] [char] (<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[TEmail] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL <br />) ON [PRIMARY]<br />GO<br /><br /><br />CREATE TABLE [dbo].[Student Academic Records] (<br />[AdminNo] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />[MCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MGrade] [float] NULL ,<br />[TGroup] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MRepeat] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MStartDate] [datetime] NULL ,<br />[MEndDate] [datetime] NULL ,<br />[Programme] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Period] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ModuleGrade] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[MType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL <br />) ON [PRIMARY]<br />GO<br /><br />ALTER TABLE [dbo].[Student Modules] WITH NOCHECK ADD <br />CONSTRAINT [PK_Student Modules] PRIMARY KEY CLUSTERED <br />(<br />[MCode]<br />) ON [PRIMARY] <br />GO<br /><br />ALTER TABLE [dbo].[Student Particulars] WITH NOCHECK ADD <br />CONSTRAINT [PK_Student Particulars] PRIMARY KEY CLUSTERED <br />(<br />[AdminNo]<br />) ON [PRIMARY] <br />GO<br /><br />ALTER TABLE [dbo].[Student Academic Records] ADD <br />CONSTRAINT [FK_Student Academic Records_Student Modules] FOREIGN KEY <br />(<br />[MCode]<br />) REFERENCES [dbo].[Student Modules] (<br />[MCode]<br />)<br />GO<br /><br /><br /><br />/////////////// SAMPLE DATA //////////////////<br /><br />Student Modules<br /><br /> [MCode] IT1111, IT1112, IT1113<br />[MName] Engineering, Manufacturing<br />[MDesc] Description of Modules<br />[MStage] 1, 4, 6<br />[MType] CM, PE<br />[MCategory] Programming, Engineering<br />[TrainingHours] 60hrs, 20hrs<br />[Credits] 5, 3, 6<br /><br />Student Academic Records<br /><br /> [AdminNo] 021201F, 084132Z<br />[MCode] IT1111, IT1112<br />[MGrade] A, B, C, D <br />[MRepeat] Yes, No<br />[MStartDate] 20/06/2003<br />[MEndDate] 20/09/2003<br />[Programme] 5, 4<br />[Period] Y1Sem1 (Year 1 Semester 1)<br />[ModuleGrade] 4 for A, 3 for B, 2 for C<br /><br />///////////////////////////////////////////////////////////////<br />
  24. dream85 New Member

    Or is there any methods to join 2 datagrid results together?

    I am thinking of creating 2 different sql statement of

    select AdminNo, sum(credits) from [student modules], [student academic records] where AdminNo LIKE '%" & textbox1.text & "%' and mtype LIKE '%" & "CM" & "%' GROUP BY AdminNo HAVING sum(credits) > '%" & textbox2.text & "%' "

    select AdminNo, sum(credits) from [student modules], [student academic records] where AdminNo LIKE '%" & textbox1.text & "%' and mtype LIKE '%" & "PE" & "%' GROUP BY AdminNo HAVING sum(credits) > '%" & textbox2.text & "%' "

    Is merging of 2 datasets do the job?
    I found a webpage which talk about it
    http://www.dotnetbips.com/displayarticle.aspx?id=94

    Regards,
    Dream85

Share This Page