Calculate time of specific subject (1 Viewer)

[Kayleigh]

Hi,
I'm building database to record sessions in weekly schedule. We have split into several tables to allow for many relationships (each session can be on multiple days, multiple times a day, multiple students...) It is possible that table sessionStudent is not required - since it is probably included in sessionWkdayStudent.

So I am trying to create a query which calculates duration each student has specific subject each week. Can you help me with this please?
Sorry file too large to send so this is link to drive folder containing DB: SubjectHours

Regards,
Krayna

 

[June7]

Run Compact & Repair to reduce file size (goes from 11000KB to 740KB). If still too large, zip file. Attach to forum post.

Why is fldStaffID in both tblSession and jtblSessionLog?

Why no data in jtblSessionLog?

See if this query gets you started:

SELECT tblStudents.fldStudentID, [fldSLastName] & ", " & [fldFirstName] AS FullName, tblSubject.fldSubjectID, tblSubject.fldSubjectName, tblWeekdays.fldDay, jtblSessionDayTimes.fldSessionDayTimesID, jtblSessionDayTimes.fldStart, jtblSessionDayTimes.fldEnd
FROM tblWeekdays 
INNER JOIN (tblSubject 
INNER JOIN (tblSession 
INNER JOIN (jtblSessionWeekday 
INNER JOIN (tblStudents 
INNER JOIN (jtblSessionDayTimes 
INNER JOIN jtblSessionWkdayStudent 
ON jtblSessionDayTimes.fldSessionDayTimesID = jtblSessionWkdayStudent.fldSessionDayTimesID) 
ON tblStudents.fldStudentID = jtblSessionWkdayStudent.fldStudentID) 
ON jtblSessionWeekday.fldSessionDayID = jtblSessionDayTimes.fldSessionDayID) 
ON tblSession.fldSessionID = jtblSessionWeekday.fldSessionID) 
ON tblSubject.fldSubjectID = tblSession.fldSubjectID) 
ON tblWeekdays.fldWeekdayID = jtblSessionWeekday.fldWeekdayID;

 

[Kayleigh]

Hi thanks for that suggestion.
I removed jtblSessionLog and jtblStudentSession - both unnecessary. (Session log is for recording attendance but don't require it in this query.)

Your query collects data based on session timing. Can you aggregate so that for each student we have number of hours per week scheduled for specific subject e.g. English.

 

[June7]

The query includes the only date/time fields in db. Calculate elapsed time using DateDiff() function. Either modify this query to do aggregation or use it as source for another query that aggregates.

 

[Kayleigh]

Trying to do an aggregate function on this but finding it difficult to extract meaningful data.
So far my SQL is: (took out some fields for simplicity)

SELECT Count(tblStudents.fldStudentID) AS CountOffldStudentID, tblWeekdays.fldWeekdayID, tblWeekdays.fldDay
FROM tblStudents INNER JOIN (jtblStudentSession INNER JOIN (tblWeekdays INNER JOIN (tblSubject INNER JOIN (tblSession INNER JOIN (jtblSessionWeekday INNER JOIN jtblSessionDayTimes ON jtblSessionWeekday.fldSessionDayID = jtblSessionDayTimes.fldSessionDayID) ON tblSession.fldSessionID = jtblSessionWeekday.fldSessionID) ON tblSubject.fldSubjectID = tblSession.fldSubjectID) ON tblWeekdays.fldWeekdayID = jtblSessionWeekday.fldWeekdayID) ON jtblStudentSession.fldSessionID = tblSession.fldSessionID) ON tblStudents.fldStudentID = jtblStudentSession.fldStudentID
WHERE (((tblSubject.fldSubjectName)="Work Placement"))
GROUP BY tblWeekdays.fldWeekdayID, tblWeekdays.fldDay;

The result achieved was:

However I am looking for a row for each student taking this subject and the number of days attending. Then I hope to work out time time duration from there...

 

[June7]

Calculating number of days attending requires either a date range to calculate number of weeks and how many days to count for each week (probably need a VBA custom function) or an Attendance table.

 

[The_Doc_Man]

Be aware that Access has some rather limited time formatting abilities. If you have a time that over a period of several days accumulates to a total greater than 24 hours, you will have issues using standard time formatting. I wish to emphasis that if you are using a DATE field for computation, you can clearly get the time correctly. The problem will not be the computation. It will be in formatting the results..

Are you looking for a number of DAYS? Or some other smaller units like hours or minutes?

 

[arnelgp]

how can? you don't have a "date field" on your transaction table.
i think you need this field (just for attendance checking).
with date field, your Aggregation will be easier.

 

[Kayleigh]

Looking at a small number of hours per week (less than 24).
I have attendance table but this calculation is based on sessions scheduled - not what they actually attended.
So the main fields the calculation is based on is weekdays (ID number) and start/end time (date). Can I aggregate with these?

 

[arnelgp]

you can, but most likely, will have same result, on each week.

 

[Kayleigh]

That's fine. Can you suggest SQL please?