Can you hide blank text boxes (rows) in a report?

[Ajabu]

I’m trying to create pay slips which not only include the standard deductions but also itemise any additions/deductions which affect the salary. The number of additions/deductions can vary for every month and employee. How can I create a report which includes this information but without big spaces between the data?

Say for example that I have the following query, the monthly data for two employees:

Notice that John Smith has many more rows of data compared to Jane Doe’s.

I could create the following pay slip report for John Smith:

But the result will look awful for the other employee (left). I’d much prefer a result like on the right. In other words, can I make the report dynamic in some way, eliminating the empty spaces?

Or maybe I’m just totally overthinking this and there’s a much easier way to structure the report.

One possible solution is to include designated rows as in the examples below. However, the number of additions/subtractions can very a lot in my case which means lots of unnecessary rows. I would also have to concatenate comments for duplicates (eg, John Smith’s sickness).

Or perhaps I could structure the pay slip reports completely differently but in a way which gives all the necessary information? Any suggestions would be greatly appreciated!

 

[Gasman]

You should only be bringing in data that needs to be reported.
I would probably have two reports, one for the left and one for the right.

However you data would need to be normalised for even that to work.

Here is one report of mine for my Bibbys DB. That has 3 subreports to emulate the layout of the Bibby Gazette.
The left is the Deck dept, the right the Engineering dept and the bottom middle, the rest of the crew.

 

[Ajabu]

Gasman, thank you for your response.

I'm not following. What do you mean one report for the right and one for the left? If you're talking about the two payslips I showed next to each other (one right, one left), those both have the same information in them; the one on the left just had all the big spaces, something I want to avoid. Like the one on the right. The only difference between those two is the spacing between the data.

What did you mean by normalising the data? I'm unfamiliar with that expression.

Finally, you suggested subreports. Would that eliminate the spaces? I have considered this option though I've never done it before. If it is the solution I would then have to figure out how to separate the data into different queries. Can you create a net total including numbers from subreports?

 

[Gasman]

I am talking about my report.
That consists of 3 subreports.
As you can see on my example, no gaps, but the right has one more record than the left.
Everything above the thick line is in the main report.

As far as I can tell, you should be able to identify the Additions for the left report, the deductions for the right and be able to calc the difference between the totals, which might well be the third sub report, or just the page footer.

This would be one way to do it.

For normalisation, I would expect an extra field to identify whether Addition or Deduction, not a column for each, but you can get away with what you have now with separate queries for each subreport. However what happens if you have another category, that involves code and design changes.

Google Database Normalization
You really should be aware of that if you are going to work with databases.

 

[Pat Hartman]

If the data is as you show it, then there is a problem with the report since the report should just be using the table or a query of the table and therefore, should have the same rows.

Can you show us what rows are selected by the query when you run the Jane Doe report? Or upload the db if there is only test data in it.

 

[Ajabu]

Thank you Gasman. I am thinking that I'll have to sort this out with Subqueries as you suggest. Not quiet sure yet how I can structure the data better (or the queries) but will have to do that.

Pat Hartman. Yes, the report is just using one query. The problem is some of the fields are empty as each row has different data. Had I been able to format all of the data onto one row per employee, I could have structured the report. But I was at a loss how to do that.

I've attached a copy of the database. Any advice would be appreciated. Thanks.

 

[Gasman]

Not subQueries, subReports.

If you want the payslip as your first picture, then just order the data correctly.
Use Groups for Salary, Additions, Deductions etc.

 

[Ajabu]

I have three main categories which affect salary:

1. Paid absences (eg including paid leave)
2. Unpaid absence (eg absent, sick)
3. Other factors which impact salary (eg Staff loan, bonuses, training) – may or may not be paid.

Would you advise that I create three (possibly four as the third category can be paid or unpaid) different subReports so as to show the payslip without spaces?

Bear with me as I try to understand this; haven’t done it before. I assume I create a new query with the data I want (eg, category one above), filter out the blank data rows and then make what will be a “subreport” from that. Is that correct? If so, how does the subreport data know how to link to the parent report, ie, that John’s absences will be listed under John and not Mary?

 

[Gasman]

Trying in just one report first. That would be your first layout.

Sort the data in the order you want within the sort option of the report. Group the same way,
So Salary would be 1, Additions would be 2 and Deductions would be 3 and so on?

That would be the easiest I believe.

 

[Ajabu]

This will take me a bit longer to figure out in my situation. But I appreciate your help, Gasman. Will update or post a follow-up question if needed.

Thanks for your suggestions.

 

[MajP]

This is wrong (The white space is a give away of wrong design)
tAbsence tAbsence

School Fees	Staff Loan	Bonus	Other Addition
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	3000​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	200​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
​	​	​	​
2000​	​	​	​
​	​	​	​

At the minimum it should be
tAbsence tAbsence

Addition_Deduction	Addition_Deduction_Type
​
​
​
​
​
($3,000.00)​	Staff Loan
​
​
​
$200.00​	Bonus
​
​
​
​
​
​
​
​
​
​
($2,000.00)​	School Fees

But more likely this gets pulled out of the absence table and these additions and deductions go into their own table. If these values always reference an absence then they go int
tblAdditionsDeductions
-- AdditionDeductionID
-- AdditionDeduction ' currency
--AdditionDeductionType ' Staff Loan, Bonus, School Fees
-- AbsenceID_FK ' a foreign key to absence table.

 

[MajP]

Same here. Should be salary and SalaryPeriod
tSalary

Monthly	Daily	Salary	SalaryPeriod
40000​	​	$4,000.00​	Monthly
50000​	​	$5,000.00​	Monthly
​	500​	$500.00​	Daily
80000​	​	$8,000.00​	Monthly
40000​	​	$4,000.00​	Monthly
​	1000​	$1,000.00​	Daily

 

[MajP]

FYI, when you start seeing a lot of white space in a table you can tell it is not properly designed (normalized).

 

[Ajabu]

MajP, that is very helpful! Thank you. Yes, I realised there are problems with the structure, actually wanted the entries all on one row but didn't know how to do that.

From your examples, if I understand correctly, you're merging various column headers into one column. I would then fill that column with foreign keys and create a new category table (including staff loans, bonuses, etc).

I will try that but I'm struggling to see if that will remove the empty spaces. Maybe that's the cause of all my problems on this. Thank you!

It really is helpful to have someone look over the structure and give suggestions on how the foundation can be improved. Appreciate that!

Will try it and report back with solutions/issues. Thanks.

 

[MajP]

By adding tblAdditionsDeductions there are no blank records.