sql - Can't complete complicated query -

i have question sql in c# want implement i'm bit stuck. below tables example data:

student:

(pk)tagid  studentid (fk)courseid 4855755    huj564334    25 4534664    red231232    33 

course

(pk)courseid coursename 25           computer science 33           biology 

coursemodule

(fk)courseid (fk)moduleid 25              cmp2343 25              cmp3456 33              bio3422 33              bio2217 

module

(pk)moduleid modulename cmp2343      networking cmp3456      databases bio3422      human body bio2217      genetics 

modulesession

(fk)moduleid (fk)sessionid cmp2343      1acmp2343 cmp2343      2acmp2343  cmp3456      1acmp3456 cmp3456      2acmp3456 bio3422      1abio3422       bio3422      2abio3422 bio2217      1abio2217       bio2217      2abio2217    

session

(pk)sessionid sessionstartdate sessiontimestart sessiontimeend 1acmp2343      09/05/2013          12:00         14:00 pm 2acmp2343      05/05/2013          09:00         11:00 pm 1acmp3456      15/05/2013          12:00         13:00 pm 2acmp3456      01/05/2013          10:00         13:00 pm 1abio3422      30/04/2013          11:00         13:00 pm 2abio3422      01/04/2013          14:00         16:00 pm 1abio2217      12/05/2013          16:00         18:00 pm 2abio2217      03/05/2013          12:00         14:00 pm    

attendance

(fk)tagid (fk)sessionid scanningtime 

i using rfid scanner scan student tagid. query find "current" sessionid (by looking date , time of session, saved in db) given tagid, , display tagid , sessionid in attendance table, along time of scanning.

so achieve need to:

• look @ student table , tagid
• see course tagid associated (looking @ courseid column)
• look @ coursemodule table see modules associated course
• look @ modulesession table see sessions associated module
• look @ session table check sessionid happening "today , now"
• display tagid , sessionid in attendance table

so if want find current session tagid = 4855755, attendance output should be:

attendance:

    (fk)tagid (fk)sessionid scanningtime      4855755    1acmp2343 

this because:

• looking @ student table: student 4855755 on courseid = 25
• looking @ coursemodule table: courseid = 25 compromises of 2 modules: cmp2343 , cmp3456
• looking @ modulesession table: modules cmp2343 , cmp3456 compromise of 2 sessions each: 1acmp2343; 2acmp2343 , 1acmp3456; 2acmp3456
• looking @ session table: session 1acmp2343 taking place today , now

my question how query suppose written able achieve example mentioned above? have made start on query need working should be.

code is:

select student.tagid, session.sessionid  student s , session se inner join course c on c.courseid = s.courseid inner join coursemodule cm on cm.courseid = c.courseid inner join module m on m.moduleid = cm.moduleid inner join modulesession ms on ms.moduleid = cm.moduleid inner join session se on se.sessionid = ms.sessionid inner join attendance on a.sessionid = se.sessionid  student.tagid = 4820427 , s.sessiondate=date(); 

(updated) c# code of class is:

    public void setsql()         {             string connstr = "provider=microsoft.ace.oledb.12.0;data source=c:\\users\\kacper\\desktop\\autoreg\\autoreg\\autoreg.accdb;";`              oledbconnection myconn = new oledbconnection(connstr);             myconn.open();              dataset ds = new dataset();              //query ask             string query = @"select s.tagid, se.sessionid, " + "'" +  datetime.now.tostring("mm/dd/yy hh:mm:ss tt") + @"' scanningtime                               student s,                             courseid-moduleid cm,                             moduleid-sessionid ms,                             session se                              1=1                              , s.tagid = 4820427                             , s.courseid = cm.courseid                             , ms.moduleid = cm.moduleid                             , ms.sessionid = se.sessionid                             , se.sessiondate = date();";              oledbcommand command = new oledbcommand(query, myconn);             oledbdataadapter adapter = new oledbdataadapter(command);                  adapter.fill(ds);                 datagridview2.datasource = ds.tables[0];                 myconn.close();          } 

i have updated sql code still cannot running, , error: unhandled exception of type 'system.data.oledb.oledbexception' occurred in system.data.dll additional information: ierrorinfo.getdescription failed e_fail(0x80004005).

just make c# easier read, might want use "@" convention insert newlines. i'm going cheat third field -- think want output time ran query.

query = @"select s.tagid, se.sessionid, " +      "'" + datetime.now.tostring("mm/dd/yy hh:mm:ss tt") + @"' scanningtime      student s     inner join course c on c.courseid = s.courseid     inner join coursemodule cm on cm.courseid = c.courseid     inner join module m on m.moduleid = cm.moduleid     inner join modulesession ms on ms.moduleid = cm.moduleid     inner join session se on se.sessionid = ms.sessionid     inner join attendance on a.sessionid = se.sessionid      student.tagid = 4820427     , se.sessiondate=date()"; 

there few ways clean up, think. need value course? if not, drop join. same module , module session.

query = @"select s.tagid, ms.sessionid, " +      "'" + datetime.now.tostring("mm/dd/yy hh:mm:ss tt") + @"' scanningtime       student s,     coursemodule cm,     modulesession ms,     session sess      1=1     , s.tagid = 4820427     , s.courseid = cm.courseid     , ms.moduleid = cm.moduleid     , ms.sessionid = sess.sessionid     , sess.sessionstartdate = date()"; 

note sql has session se in same line from student s , has s.sessiondate instead of se.sessiondate.

try blasting comment in second sql; it's been long time since i've thrown sql @ access.

past that, you're moving on different question. try standard debugging moves -- can run simpler query, query = "select * student"; same spot without error?