locked
Looping through access datatables and compare values to get conflicting data RRS feed

  • Question

  • User1864573222 posted

    i have an excel which i transferred to access database containing 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	EG20

    Another table in access 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 paper    No.of students    AdminNo
     
    paper1 : paper2                        2                    112673P, 119283H
     

    How can i go about doing this? im really stuck. any help will be appreciated
     
    Any help will be greatly appreciated. Thanks in advance!

    Tuesday, July 16, 2013 7:43 AM

Answers

  • User-1373950060 posted

    Hi,

    Perhaps that's a database question. you can post the problem on sql server forum. and here some suggestion about your question:

    the code/pseudocode is something like
    ----------- dim rs as recordset
    set rs = currentdb.openrecordset "mytable"
    while not rs.eof {process the record} rs.movenext wend
    rs.close -------------------------- when you are in a record, you can examine fields with thisvalue = rs!myfield
    to set/update fields you need to add
    rs.edit 'to edit it then you can say rs!myfield = "whatever" rs.update 'to save the edit
    various other commands exist for recordsets 
    

    Hope that's helpful.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 30, 2013 10:21 PM

All replies

  • User-1373950060 posted

    Hi, 

    Thank you for your question.  I am currently looking into this issue and will give you an update as soon as possible.

    Thanks for the understanding.

    Friday, July 19, 2013 4:01 AM
  • User-1373950060 posted

    Hi,

    Perhaps that's a database question. you can post the problem on sql server forum. and here some suggestion about your question:

    the code/pseudocode is something like
    ----------- dim rs as recordset
    set rs = currentdb.openrecordset "mytable"
    while not rs.eof {process the record} rs.movenext wend
    rs.close -------------------------- when you are in a record, you can examine fields with thisvalue = rs!myfield
    to set/update fields you need to add
    rs.edit 'to edit it then you can say rs!myfield = "whatever" rs.update 'to save the edit
    various other commands exist for recordsets 
    

    Hope that's helpful.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 30, 2013 10:21 PM