locked
Getting conflict data, comparing tables RRS feed

  • Question

  • i have an excel conatining the following:

    S/N School-campus PaperID papertitle No.ofStudents hours Modules/subjects

    1 SEG-AMK EG1832 Mechanics and Materials 286 1.5 EG1832 2 SEG-AMK EG1833 Electrical Principles 375 1.5 EG1833 EG1952 EG1903 3 SEG-AMK EG1835.1 Engineering Mathematics (1) 456 1.5 EG1835 EG1001 4 SEG-AMK EG1835.2 Engineering Mathematics (2) 363 1.5 EG1907 EG1681 EG1951 EGB101 EGC101 EGD101 EGF101 EGH101 5 SEG-AMK EG1004 Analog Electronics 262 1.5 EG1004 6 SEG-AMK EG1009 Electric Circuits 229 1.5 EG1009 7 SEG-AMK EG1010 Digital Electronics 229 1.5 EG1010 8 SEG-AMK EG2008 Engineering Mathematics 2B 563 1.5 EG2008 EG2681 EGB207 EGC207 EGD207 EGF207 EGH207 EGJ207 9 SEG-AMK EG1008 Engineering Mathematics (1A/1C) 570 1.5 EG1008 EGB107 EGC107 EGD107 EGF107 EGH107 EGJ107 10 SEG-AMK EG2010 Electronic Communication 221 1.5 EG2010

    A datagrid and access database table containing the following:

    ModuleCodes AdminNo EG1001 112960A 114424H 120428G 120595K 123452P 124090Q 125559F 125662E 126117Y 126184K 126386A 126387F EG1002 113426D 121208B 122329T 126136J 130016W 130019K 130020D 130022N 130034B

    ALL ARE ONLY PART OF THE DATA.

    I'm intending to import the excel file into access.

    then compare the 2 tables and get the common students between modules.

    Example:

    paper 1 (S/N 1) : EG1832

    paper 2 (S/N 2) : EG1833, EG1952, EG1903

    And StudentID:112673P & 119283H is involed in EG1832 & EG1952

    then, output:

    conflictive No.of students AdminNo

    p1:p2 2 112673P, 119283H

    How can i go about doing this? im really stuck. any help will be appreciated

    thanks

    Tuesday, July 16, 2013 2:05 AM

Answers

  • If I understand well, this "database" is badly designed.

    1) In the Excel file, two data types are mixed :

    • the definition of paper, paperID, paperTitle.
    • the association of one paper with related modules.

    You should really have two tables (two lists) :

    • one list with all information on a paper
    • one list with the associations : one line per association. Each line should contain the paperID and the moduleID

    2) Same problem in the access database. You really should have one line per association module/student and repeat both information on all lines.

    When you have completed this, you should end up with at least 3 tables (3 lists) :

    • the Paper table
    • the PaperModule association table
    • the ModuleStudent association table

    You can find which students are associated with more than one module by :

    SELECT Student, COUNT(*) FROM ModuleStudent
    GROUP BY Student
    HAVING COUT(*) > 1

    • Marked as answer by Youen Zen Friday, July 26, 2013 9:14 AM
    Friday, July 19, 2013 2:42 PM

All replies

  • Hi,

    Since this is a VB language specific issue neither an Excel develop issue, I suggest get support from Microsoft Answers: http://answers.microsoft.com/en-us/office.

    Regards,


    Shanks Zen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 17, 2013 8:28 AM
  • Hello,

    Your post is very difficult to read. Data is not aligned. I don't understand which is which. Looks like a big mess.

    I suggest you use the "Insert HTML" or "Insert Image" buttons on top of the reply window.

    Wednesday, July 17, 2013 8:41 AM
  • If I understand well, this "database" is badly designed.

    1) In the Excel file, two data types are mixed :

    • the definition of paper, paperID, paperTitle.
    • the association of one paper with related modules.

    You should really have two tables (two lists) :

    • one list with all information on a paper
    • one list with the associations : one line per association. Each line should contain the paperID and the moduleID

    2) Same problem in the access database. You really should have one line per association module/student and repeat both information on all lines.

    When you have completed this, you should end up with at least 3 tables (3 lists) :

    • the Paper table
    • the PaperModule association table
    • the ModuleStudent association table

    You can find which students are associated with more than one module by :

    SELECT Student, COUNT(*) FROM ModuleStudent
    GROUP BY Student
    HAVING COUT(*) > 1

    • Marked as answer by Youen Zen Friday, July 26, 2013 9:14 AM
    Friday, July 19, 2013 2:42 PM