Report Problem

[phendrena]

Hi there,

I was wondering if anyone would be able to help me with a problem that I am having producing a report.

I have setup a report, grouped by Team Leader.
The report is then setup to show Customer Managers.
The report should only list each customer manager once and produce a total for the various fields. In some instances the report is listing the customer manager more than once, in others is does produce a total.
Any idea why this is happening?

Also, I want 2 fields on the report to be totaled if the field data = "Yes".
These fields would be "Saved" and "Eligible" (Eligible should be totaled if Saved & Eligible both = "Yes"). In Excel i would've used a Sumproduct formula, how do i do this in access as i've tried a sumproduct formula with no success.
( =SUMPRODUCT(--([Saved]="Yes"),--([Eligible]="Yes")) ).

Thanks,

[OBP]

The duplication most probably comes from the Query that supplies the data, especially if you have Tables joined in the Query, so you want check it there first.

Ok, you are not using a Query, so the Duplication is in the way that the Report/data is set up.

Do you want eligible Totalled or Counted, as it has no Value?

I can't see any reason why it repeats "David Bandey".

[OBP]

I can get rid of the repeats, just move the "Original Premium" Sort Ascending to After the Customer Manager Group.
But I am not sure that output is what you want.

[phendrena]

The duplication most probably comes from the Query that supplies the data, especially if you have Tables joined in the Query, so you want check it there first.

Ok, you are not using a Query, so the Duplication is in the way that the Report/data is set up.

Do you want eligible Totalled or Counted, as it has no Value?

I can't see any reason why it repeats "David Bandey".
Hi OPD,

I've been able to get rid of the repeats (i removed the sub-section group/sort and that seemed to solve the problem.

Regarding 'Eligible', it should show a count of eligible, so for every 'Yes' it counts. Now, i've moved from Excel to Access. In excel i would use a sumproduct formula to produce the results i need. In this case the total of Eligible calls that we have Saved. So the formula needs to count Eligible if it equals Yes but only if Saved also equals yes.

So, if this was excel the formula would be =SUMPRODUCT(--('Saved'="Yes"),--('Eligible'="Yes")).

How can i do this in Access?

thanks,

[OBP]

Well I would normally do this in a query using an IIF() statement by converting the checkbox to a value of 1, as queries count check boxes whether they ar ticked or not.

[phendrena]

Well I would normally do this in a query using an IIF() statement by converting the checkbox to a value of 1, as queries count check boxes whether they ar ticked or not.
The value in the fields is stored as Yes or No not as a tickbox.
I have a query setup, but i need to count the total of yes and no, which Access does automatically on the report (=count([field]), i also need to just count the "Yes" answers, how can i do this?

Thanks,