report as excle report

[mohamedmatter]

Hi Group, thank you for providing assistance and solving most of the problems that many members encounter. I have a problem in preparing a report for a group of employees and printing it. I have attached a picture of the format of the report that was prepared on the Excel program. I ask for help in preparing this report on the Access program. I have also attached a simplified version of the database. An explanation of the problem is to work out the total salaries at the end of each page for the group of employees on this page, and on the next page at the top of the row that contains the total of the previous page, and so on until the end of the report.

 

[arnelgp]

maybe you can use temporary table to ouput your report.
see the code behind the report.
and open the report in "print preview".

 

[mohamedmatter]

maybe you can use temporary table to ouput your report.
see the code behind the report.
and open the report in "print preview".

Thank you very much. Great work. I would like to understand the idea of the work

 

[Gasman]

maybe you can use temporary table to ouput your report.
see the code behind the report.
and open the report in "print preview".

@arnelgp
If you have 18 entries on the first page all at 1000 each, how does that total become 19000?
On the next page you get 37000 for the next 18 entries? which is correct for that page, and the next one at 55000 ?

 

[mohamedmatter]

@arnelgp
If you have 19 entries on the first page all at 1000 each, how does that total become 19000?
On the next page you get 37000 for the next 18 entries? which is correct for that page, and the next one at 55000 ?

Yes, there is a big error in the totals. Is there a solution?

 

[mohamedmatter]

@arnelgp
If you have 19 entries on the first page all at 1000 each, how does that total become 19000?
On the next page you get 37000 for the next 18 entries? which is correct for that page, and the next one at 55000 ?

@arnelgp
If you have 19 entries on the first page all at 1000 each, how does that total become 19000?
On the next page you get 37000 for the next 18 entries? which is correct for that page, and the next one at 55000 ?

I thank Professor Gasman for this note

 

[Gasman]

Sorry, I meant 18 on the page.
The final total is correct though?
I have amended my post accordingly.

 

[arnelgp]

If you have 18 entries on the first page all at 1000 each, how does that total become 19000?

i corrected it (hopefully) on this new upload

 

[Gasman]

Hi @arnelgp that now totals correctly, but appears now not to as the last number is 66, but the total is for the 60 entries.
There are gaps between 19 & 23 and 42 & 46.

I expect if they were just names it would not be noticable, so I would cetrainly not show the code_id.

Nice work all the same

 

[arnelgp]

There are gaps between 19 & 23 and 42 & 46.

thanks for noticing.
but that is the original data i got when i downloaded the db on post #1.
you can also check that on the employee_tbl, there is already a gap on them.

 

[Gasman]

thanks for noticing.
but that is the original data i got when i downloaded the db on post #1.
you can also check that on the employee_tbl, there is already a gap on them.

Ah, I see, my apologies.

 

[mohamedmatter]

i corrected it (hopefully) on this new upload

Thank you, Professor Arnelgp, for your wonderful effort. Although I was late in thanking you for your efforts. I would have liked a simple explanation of the idea of this work so that I could train myself on similar ideas. You have my thanks and appreciation.

 

[mohamedmatter]

Ah, I see, my apologies.

There is no need to apologize, enough for your wonderful diamond

 

[mohamedmatter]

In continuation of my previous question, I created a form "Department_CountRow_Frm"through which I want to control the number of rows in the report pages. And display the employees’ salaries by selecting the department. thank you

 

[DHookom]

The basics are in the attached. I added a running sum text box to number the rows. If the running sum values divided by the number of records per page is an even number, the pagebreak control is made visible.

    Dim intRecPerPage As Integer
    intRecPerPage = Forms!Department_CountRow_Frm!Countrow
    'determine if the txtRunCount is evenly divided by the records per page
    ' the \ operator is "integer divide". 10\3 = 3
    If Me.txtRunCount / intRecPerPage = Me.txtRunCount \ intRecPerPage Then
        Me.PgBrk.Visible = True
        'Debug.Print "Equal", "PgBrk Vis: " & Me.PgBrk.Visible
     Else
        Me.PgBrk.Visible = False
    End If

I also added the department field to the record source of the report to allow for filtering. If there are no records returned by the report's record source, you will get an error.

I don't know what all the other code is since I'm late to the party. Adding comments to your code is your friend ;-)

 

[mohamedmatter]

The basics are in the attached. I added a running sum text box to number the rows. If the running sum values divided by the number of records per page is an even number, the pagebreak control is made visible.

    Dim intRecPerPage As Integer
    intRecPerPage = Forms!Department_CountRow_Frm!Countrow
    'determine if the txtRunCount is evenly divided by the records per page
    ' the \ operator is "integer divide". 10\3 = 3
    If Me.txtRunCount / intRecPerPage = Me.txtRunCount \ intRecPerPage Then
        Me.PgBrk.Visible = True
        'Debug.Print "Equal", "PgBrk Vis: " & Me.PgBrk.Visible
     Else
        Me.PgBrk.Visible = False
    End If

I also added the department field to the record source of the report to allow for filtering. If there are no records returned by the report's record source, you will get an error.

I don't know what all the other code is since I'm late to the party. Adding comments to your code is your friend ;-)

hank you for your response and I apologize for not being able to clarify my request. I made some modifications 1- Adding the department field to the tables, but for the reporting _tbl table, the departments' data does not appear 2- The "Department_CountRow_Frm" form, I want to do two things through it: -

First - Control the number of employees on a single print sheet through the control element in the form, where I made it between 15: 19 employees "Select row between 15: 19"
Second - I also control the printing of employees and their salaries for each department separately on the same report through the element on the same form "Select Department"
Summary "I want to modify the existing code?" in controlling the number of rows and employee departments through the form "
Clarification:
With the total appearing at the end of each page and the previous total at the top of each page following the first page and
I also attached the rule after making modifications

 

[arnelgp]

you may also try this database.

 

[DHookom]

hank you for your response and I apologize for not being able to clarify my request. I made some modifications 1- Adding the department field to the tables, but for the reporting _tbl table, the departments' data does not appear 2- The "Department_CountRow_Frm" form, I want to do two things through it: -

First - Control the number of employees on a single print sheet through the control element in the form, where I made it between 15: 19 employees "Select row between 15: 19"
Second - I also control the printing of employees and their salaries for each department separately on the same report through the element on the same form "Select Department"
Summary "I want to modify the existing code?" in controlling the number of rows and employee departments through the form "
Clarification:
With the total appearing at the end of each page and the previous total at the top of each page following the first page and
I also attached the rule after making modifications

Didn’t my file satisfy the first two requirements for setting the number of records on a page and providing a filter by department?

 

[mohamedmatter]

Didn’t my file satisfy the first two requirements for setting the number of records on a page and providing a filter by department?

I appreciate your effort and wish you a nice time. I could not explain what I want. I saw your work but it is not what I want and I also modified some things. I extend my sincere thanks to you and I hope to follow up with you in case there are some modifications. I also hope and welcome any idea to learn from your experience. many thanks

 

[mohamedmatter]

you may also try this database.

hank you Mr. arnelgp for your wonderful work, i wishing from you something . I appreciate that you are able to do . Is it possible to insert "department filed " in text box at the top of the report without inserting column a department filed . as picture ? thank you.