Jump to content

[Qs] Sorting & Grouping Report


This topic is 1844 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Your question is not clear. Do you want your report to be summarized by Staff, then by Branch_Code? If so, why is there a Branch_Code field in your Staff table, too?

Also please explain what fields do you have, and in which table, from which one would calculate the hours and the $.

  • Like 1
Link to comment
Share on other sites

Thanks for your kind attention.

- attendance table have staff_code, branch_code, and working hour

- just want to group attendance.staff_ID then group attendance.branch_code

- base on attendance.staff_ID and attendance.branch_code to calculate each staff working hour in report


attendance table

date staff_id branch_code working_hour
1/1/2016 A111 COD 1
2/1/2016 A111 ABA 2
3/1/2016 A111 COD 1
4/1/2016 A111 ABA 3
5/1/2016 A111 COD 1
6/1/2016 A111 COD 2
7/1/2016 A111 EXE 1
8/1/2016 A111 COD 2
9/1/2016 A111 COD 1
10/1/2016 A111 ABA 3
. A111 COD 3
. A111 ABA 2
. A111 ABA 1
. A111 ABA 2
31/1/2016 A111 EXE 3
1/1/2016 A222 ABA 1
2/1/2016 A222 ABA 2
3/1/2016 A222 EXE 2
4/1/2016 A222 ABA 3
5/1/2016 A222 EXE 4



COD <sum COD working_hour> x hour salary $ = COD salary$
ABA <sum ABA working_hour> x hour salary $ = ABA salary$
EXE <sum EXE working_hour> x hour salary $ = EXE salary$
  Total Salary = COD$ + ABA$ + EXE$


Thanks a lot


Link to comment
Share on other sites

The fields that you need to have in the Attendance table:

• StaffID
• Branch_Code
• Hours_Worked (Number)
• Hourly_Rate (Number)

• cWage (Calculation = Hours_Worked * Hourly_Rate)
• sTotal_Hours (Summary, Total of HoursWorked)
• sTotal_Wage (Summary, Total of cWage)

Once you have that in place, produce your report using a layout of the Attendance table, with three sub-summary parts:

  1. a leading sub-summary when sorted by StaffID;
  2. a leading sub-summary when sorted by Branch_Code;
  3. a trailing sub-summary when sorted by StaffID;

and no body part.

Place the staff name field/s from the Staff table In the leading sub-summary by StaffID part.
Place the Branch_Code, sTotal_Hours and sTotal_Wage in the sub-summary by Branch_Code part.
Place the sTotal_Wage field in the trailing sub-summary by StaffID part.

Rates can change over time. This is why you should have an individual Hourly_Rate value for every record in the Attendance table (you would probably look this up from the related record in the Staff table). The way you have described this - calculating a sub-total of hours for each branch and multiplying it by the hourly rate - would produce incorrect result when the rate has changed during the course of the period being reported or since.

  • Like 2
Link to comment
Share on other sites

29 minutes ago, janet0211 said:

And how to do i want to add date range in the report.

Do a find for the date range. Usually that would be part of your reporting script: go to the reporting layout, find the records you want to report on, show and/or print the report, return to whatever you were doing.

Or are you asking how to display the date range? In such case, I would ask how would you specify the date range to report on?


Edited by comment
  • Like 1
Link to comment
Share on other sites

Help expert

further Qs, I want to make a report like this:


    tblAttendance.Branch x Hour x $
    tblAttendance.Branch x Hour x $
    tblAttendance.Branch x Hour x $
    tblAttendance.Branch x Hour x $
        Total $


I want to print all staff payroll slip under Main branch group, how to setting in the layout/report? Thanks Thanks Expert:)

Edited by janet0211
Link to comment
Share on other sites

This topic is 1844 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.