calculation hrs for schedule

[ultra5219]

hello


i wana ask about calculation hours for schedule table. lets say how to calculate based on what i select from my schedule table for :
no class: how many hrs
lesson i got class: how many hours.. for example: 1+0.5= 1.5 hrs
total of hours: which is based on "no class" + " lesson i got class" for example: 1+1+0.5= 2.5 hrs


i need to someone to help me how to perform these calculations using access which i want to open schedule report which show the total hours of each this. is it using query or?

help me

Thanks again
Attachment 12463

[June7]

Need to know what the data is like. Provide sample in post or attach database. Follow instructions at bottom of my post.

Review: http://office.microsoft.com/en-us/ac...005188027.aspx

[ultra5219]

here is the sample looks like schedule class.. im having problem to perform calculation on my access.. i did attach the sample zip file above..
Attachment 12465

[June7]

I see the link now.

Consider this query for a start:

SELECT tblAttendance.UserID, tblUSer.userName, tblTime.StartTime, tblTime.EndTime, DateDiff("n",[startTime],[EndTime])/60 AS ElapsedTime
FROM tblTime RIGHT JOIN (tblUSer RIGHT JOIN tblAttendance ON tblUSer.userID = tblAttendance.UserID) ON tblTime.Time_ID = tblAttendance.Attend_Office;

Use that query as the datasource for report using Grouping & Sorting features with aggregate calcs to total the ElapsedTime.

[ultra5219]

ok since u said start by this query:

SELECT tblAttendance.UserID, tblUSer.userName, tblTime.StartTime, tblTime.EndTime, DateDiff("n",[startTime],[EndTime])/60 AS ElapsedTime
FROM tblTime RIGHT JOIN (tblUSer RIGHT JOIN tblAttendance ON tblUSer.userID = tblAttendance.UserID) ON tblTime.Time_ID = tblAttendance.Attend_Office;

u mean, do i have to create a seperate query by using SQL or??
OR create a report first, then from the datasource of report, use that query which i made from SQL??

sorry, if still confusing.. can u teach me steps by steps?

[June7]

I mean use the query designer to build and save that query. Use that query as the RecordSource for a report (unless you prefer to have that SQL statement directly in the report RecordSource).

Access Help has guidelines on building reports with Grouping & Sorting features and aggregate calculations in footer sections. I see that you have a report that uses the Sorting feature but there is no grouping. Grouping might not be needed if you always filter the report for one user.

This latest db posted does not have the same tables as the first db. New query:
SELECT USer.USerID, schedule.class_ID, USer.USerNAme, USer.Department, Timess.Start_Time, Timess.End_Time, Timess.Hours, room.RoomName, Module.ModuleNAme, schedule.dayid, schedule.timeid, schedule.class_Valid_date, day_of_week.day_name FROM [USer] RIGHT JOIN (room RIGHT JOIN ([Module] RIGHT JOIN ((schedule LEFT JOIN Timess ON schedule.timeid = Timess.TimeID) LEFT JOIN day_of_week ON schedule.dayid = day_of_week.dayid) ON Module.ModuleID = schedule.moduleid) ON room.RoomID = schedule.room_id) ON USer.USerID = schedule.userID;

Instead of using popup input parameter, use a form. Review http://datapigtechnologies.com/flash...mtoreport.html

[ultra5219]

I mean use the query designer to build and save that query. Use that query as the RecordSource for a report (unless you prefer to have that SQL statement directly in the report RecordSource).

Access Help has guidelines on building reports with Grouping & Sorting features and aggregate calculations in footer sections. I see that you have a report that uses the Sorting feature but there is no grouping. Grouping might not be needed if you always filter the report for one user.

This latest db posted does not have the same tables as the first db. New query:
SELECT USer.USerID, schedule.class_ID, USer.USerNAme, USer.Department, Timess.Start_Time, Timess.End_Time, Timess.Hours, room.RoomName, Module.ModuleNAme, schedule.dayid, schedule.timeid, schedule.class_Valid_date, day_of_week.day_name FROM [USer] RIGHT JOIN (room RIGHT JOIN ([Module] RIGHT JOIN ((schedule LEFT JOIN Timess ON schedule.timeid = Timess.TimeID) LEFT JOIN day_of_week ON schedule.dayid = day_of_week.dayid) ON Module.ModuleID = schedule.moduleid) ON room.RoomID = schedule.room_id) ON USer.USerID = schedule.userID;

Instead of using popup input parameter, use a form. Review http://datapigtechnologies.com/flash...mtoreport.html

Thank You for respond my message. alright, i try first hope it works..