Duplicate records in Crystal Reports 2010

Littlehelpplease 0 Reputation points
2023-05-21T00:36:40.8433333+00:00

I have 2 tables as follow. Table 1 has fields employee number, shift date, and hours. Table 2 has employee number, leave type, leave start date, and leave end date. I am trying to sum hours in one column and hours, less those that fall within a specific leave type between the start and end dates of a leave, in another. Problem I have begins with an employee having multiple leave types with varying ranges of start and and dates. My results are duplicate records/hours being being totalled because the date range, and type, is being queried for each leave, bringing in hours multiple times. I only have employee number to link in each table. I hope that makes sense? Thank you so much for your help!

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,001 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Muhammad Ahsan Khan 245 Reputation points
    2023-05-21T08:02:43.47+00:00

    Greetings,

    According to the details you have provided, it appears that you are facing the problem of repetitive entries in your Crystal Reports 2010 because of multiple categories of leaves having different start and end dates for the same employee. To fix this matter and prevent the occurrence of recurring entries, you can follow the below-mentioned steps:

    1. Create a new formula field in Crystal Reports to calculate the hours for each leave type within the specified date range. In the Field Explorer, right-click on "Formula Fields" and select "New." Give a name to the formula, such as "AdjustedHours."
    2. Insert a new summarized field in Crystal Reports to sum the adjusted hours.
    • Drag and drop the "AdjustedHours" formula field created in the previous step to the desired section of the report (e.g., Details section).
    • Right-click on the field, go to "Insert," and select "Summary."
      • In the "Summarize Field" dialog, choose the appropriate summary operation (e.g., Sum) and place it in the desired section (e.g., Report Footer).

    After following these steps you should be able to calculate the total hours and the adjusted hours (excluding hours within the specified leave type and date range) without duplicating the records.

    Hope it helps!