Is there a way to supress a line if there is no data? 1

[shanl]

I have created an access payroll report that has many categories that a person could charge time to but if a person has not charged time to those categories I don't want to print them. Currently I've hardcoded all those categories into my report as text fields but I would like to suppress those lines if there are no hours. Is there a technique to accomplish this?

Example of the hard coded lines and hours

023 -Funeral - 0 hrs
080 -Military - 0 hrs
099 - LWOP - 0 hrs
080 - Jury - 16 hrs

In this example the only thing I would like to print is

080 - Jury - 16 hrs.

Is there a way to do this?

Shan

 

[Remou]

The usual way is to base your report on a query.

 

[shanl]

Remou,
I do base my report on a query but I wonder if I'm missing your point. I have many calculations added to my query for all the fields in question. I then build my WHERE clause in VBA code from a previous form that users fill out which allows them to select which people should be on the payroll report and then I open my report. There are many detail records that I must read through and summarize before I know what I have. I only get detail records on time worked or taken off. If no time came through on a category then I know those fields will show zero but I'm unclear how to suppress the line from printing even when I know the count is zero?

Sample of input records
Mary 4/10/07 OT 2 hrs
Mary 4/10/07 Reg 8 hrs
Mary 4/11/07 Jury 8 hrs
Mary 4/12/07 Jury 8 hrs
Mary 4/13/07 Ill 8 hrs

In this example I know that I have 4 pieces of info to report and many other cateogories that she doesn't have to report but I didn't know that until I've processed all of Mary's details records.

Shan

 

[Remou]

Surely you can select only those records that do not have a zero in the hours column? For example (very roughly):

[tt]SELECT tblEmpoyees.EmployeeID,
tblDeductions.Category,
tblDeductions.Hours
FROM tblEmpoyees
INNER JOIN tblDeductions
ON tblEmpoyees.EmployeeID = tblDeductions.EmployeeID
WHERE tblDeductions.Hours>0[/tt]

If this is not possible, please post schemas for your tables.

 

[dhookom]

I believe Shanl has an un-normalized table structure where categories are stored in field names rather than data values. The way to resolve this is to normalize the table structure or use a union query to normalize. Then create a subreport based on the union query.

It would help if we knew the table and significant field names and data types.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

Duane,
You have hit the nail on the head. Since I'm calculating info on each detail record for various counts and categories it is very much un-normalized. This is fine as I process all the detail on the report without any problem and also because I make those lines invisible.My problem (and perhaps this problem is self-inflicted, is that when I get to my subtotals (where I want to print the totals of what came through I have hard coding into the report descriptions and codes that go with the hrs. Its the categories that have zero hours that I don't want to print those hard coded descriptions. Perhaps there is a way to print them some other way. This is where I'm stuck. Here is a copy of the query that is run prior to opening the report. It's quite ugly and very complicated based on many table joins and unions. While it gives me the detail I need, its at subtotal time that I'm challenged. A where clause is programmatically built and added on to each query before the report runs which you don't see here now.

SELECT dbo_EmpSickVacs.lEmpID, dbo_Emp.lEmpInfoID, dbo_EmpInfo.zBadgeID, dbo_Emp.lDeptID, dbo_Dept.zDeptDesc, dbo_EmpInfo.zLName, dbo_EmpInfo.zFName, dbo_EmpInfo.zMName, dbo_EmpActPer.bActive, dbo_EmpActPer.bFullTime, dbo_EmpActPer.dtActStart, dbo_EmpActPer.dtActEnd, dbo_Schedule.Date_, dbo_Schedule.zID, dbo_Schedule.Type, dbo_Schedule.Hrs, dbo_EmpSickVacs.snWkHoursADay,

IIf([Type]="Hour OT" And ([zID]<>"BBQ Contest" And [zID]<>"PARKS-BIKE RIDE" And [zID]<>"DUI Grnt#211760" And [zID]<>"STEP Gr #211770"),[Hrs],0) AS OThr,

IIf([zID]="Ha",[snWkHoursADay],0) AS HaHr,

IIf([zID]="H" Or [zID]="PtH",[snWkHoursADay],0) AS HHr,

IIf([zID]="BBQ Contest",[Hrs],0) AS BBQ,

IIf([zID]="PARKS-BIKE Ride",[Hrs],0) AS Bike,

IIf([zID]="DUI Grnt#211760",[Hrs],0) AS DUI,

IIf([zID]="STEP Gr #211770",[Hrs],0) AS STEP,

IIf(([zID]="C" And [Hrs]>0),[Hrs],IIf([zID]="C" And [snWkHoursADay]>0,[snWkHoursADay],0)) AS [Comp],

IIf([zID]="WC",[Hrs],0) AS WC,

IIf(([zID]="ill" Or [zID]="Mat" Or [zID]="z-ill"),[snWkHoursADay],0) AS ill,

IIf([zID]="F",[Hrs],0) AS Funeral,

IIf([zID]="V" Or [zID]="V-10",[snWkHoursAday],0) AS Vhr,

IIf([zID]="pH",[snWkHoursAday],0) AS pH,

IIf([zID]="Mil",[snWkHoursAday],0) AS Mil,

IIf((([zID]="LWOP" Or [zID]="ADM" Or [zID]="SUS") And [Hrs]>0),[Hrs],

IIf((([zID]="LWOP" Or [zID]="ADM" Or [zID]="SUS") And [snWkHoursAday]>0),[snWkHoursAday],0)) AS LWOP,

IIf([zID]="Ltd",[snWkHoursAday],0) AS Ltd,
IIf([zID]="Jry",[snWkHoursAday],0) AS Jury,
IIf([zID]="FM",[snWkHoursAday],0) AS FMLA,
IIf([zID]="std",[snWkHoursAday],0) AS std,
IIf([zID]="W",[snWkHoursAday],0) AS Well,
IIf((([zBadgeID]="4230" And "7874" And "8107") And [Type]="Hour OT"),[Hrs],IIf(([zBadgeID]="4230" And "7874" And "8107") And [Type]="Job",[Hrs],0)) AS PartTimehrs

FROM ((((dbo_EmpActPer INNER JOIN dbo_Emp ON dbo_EmpActPer.lEmpID = dbo_Emp.lEmpID)

INNER JOIN dbo_EmpInfo ON dbo_Emp.lEmpInfoID = dbo_EmpInfo.lEmpInfoID)

INNER JOIN dbo_Dept ON dbo_Emp.lDeptID = dbo_Dept.lDeptID)

INNER JOIN dbo_Schedule ON (dbo_EmpInfo.zLName = dbo_Schedule.LName) AND (dbo_EmpInfo.zFName = dbo_Schedule.FName) AND (dbo_Dept.zDeptDesc = dbo_Schedule.Department))

INNER JOIN dbo_EmpSickVacs ON dbo_EmpActPer.lEmpID = dbo_EmpSickVacs.lEmpID

ORDER BY dbo_Emp.lDeptID, dbo_EmpInfo.zLName, dbo_EmpInfo.zFName, dbo_EmpInfo.zMName;

 

[dhookom]

It looks like your original records might be normalized. I would get rid of most of the IIf()s and use a table of zIDs and "title" values.

Back to your issue, I would either go back to the normalized data for a subreport of zIDs that can grow or shrink based on the number of non-zero records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

Duane,
The input file that I get that tells me what a person has worked either stores the number of minutes worked in one of two fields. Either in a field called (Hrs)or (snwkshoursaday) which is what all of my IIf statements above do. I then further divide which category those minutes belong to by putting it in the proper Leave or OT category. I don't know how I would put that info into a table. You may have thought it was just title's for fields and I'd agree if it had been some stable hard coded field that would make sense but the IIf logic was intended to determine the category and determine which Hr or snwkshoursaday field to get the time worked from. (Some were missing the second field in the above example but I have since added that logic).
I've never used a sub-report before so I'll have to figure out how to do that. I can't base the subreport on the original query that prints out the report. The detail records are one record for every day . e.g. If I need to know whether to print the Military subtotal, I can't check Day 1 to determine if Military time was used because that may have zero but Day 2 may have used Military time, but I don't know until I've processed all the detail records. So my question is, since I have calcualted a Subtotal of all the counts on the report, is there any way to use the Subtotal counts by person to trigger a Sub-report?

Shan

 

[dhookom]

I don't think you are understanding me. I expect you began somewhere with a normalized table of hours where the type of hours was a value in a field rather than a field name. You seem to have a field zID that stored the type of hours. If I charged time to "std" and "FM" that would create at least 2 records. [blue]That was good.[/blue]

I could summarize these records where the hours was greater than zero and use them in a subreport. If I didn't have any "Jry" hours, they simply wouldn't show.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

Duane,
As I read through your scenario, I still asked myself how will I know which totals are coming through for a person so I can supply the correct hard coded descriptions to go with the counts >0? (The whole problem that I've been wrestling with). Thats when it occurred to me that I could provide Iif logic to my existing report. O.k. this may be a little clunky but it satisfies what my goal was. To supress the lines when there are no totals for them. I realize it will take up some space between people but I think that will be fine.

So the solution I came up with is to add the
IIF logic to my hard coded descriptions in text boxes so that it only prints when there is a total.

stdDesc:Iif(stdhrs>0,"Short Term Disability - code 033 -"," "

stddhours:Iif(stdhrs>0,stdhrs,0)

The only tricky part of this is if I do the same kind of thing for the total hrs and I make the default null or nothing, the report will be blank but it messes up other totals that use that field. If I make it zero, it prints a zero but all my other totals that this is tied to work. Is there a way to zero suppress a field so it won't show up when it's zero?

Thanks Duane for giving me an idea to get what I need. Your solution sounds like a good way to go but I have some reservations (probably more ignorance on how to accomplish) but you got me at least thinking in a direction that might also work.

 

[dhookom]

I'm totally lost regarding your original table structures and their relationships. You must have a table that includes fields for EmployeeID, DateWorked, TypeOfHours, HoursAmt. Other tables should provide lookup values based on the hours table. Please explain....

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

It was a six way table join to get all the pieces of data that I needed. Very ugly. It's a vendor table that can't be changed.

Is there a way to zero suppress values so the zero won't show up?

 

[dhookom]

I'm afraid the solution that I would use and recommend involves normalized records. Your query listed above seems to take possibly normalized data and spreads it out into a spreadsheet type view. I can't (won't take the time) to work with values that are displayed across fields rather than across records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]