Hi,

I'm having a bit of difficulty with a report that I'm writing. The report lists the summary of claims that have been made against haulier deliveries. I can get the report to sum up the number of cases and the number of SKU's involved but when it comes to adding up the number of claims this goes wrong.

For example if a haulier OUTTR has two claims both with two SKU's ech for 10c/s then the report should say OUTTR 4 SKU's 40c/s across 2 claims. The result that I'm getting is 4 SKU's 40 c/s across 4 claims. Because there is an association between the SKU and Claim No it seems to want to count each occurance of a claim number and not the unique occurances. Can any of you think of a solution for me. If you need more detail please le me know.

Thanks

Figgis,
Have you tried setting a grouping levels for haulier, claims, and SKU's?

You should be able to total within each grouping level.

HTH,

Kevin

dartcoach

Kevin,

I tried that but it still didn't make any difference. There is a function in Excel call Fequency. Do you know if there is a similar function on Access. If the is then I might be able to us an Iif to create a counter

Figgis

Hi,

I figured it out. What I had to do was add line numbering and then add a control to get the last number for each cycle then sum that at the end of the report. Tricky little problem!

I didnt quite get what you did...can you please explain in detail what did you do to get the right numbers?
Thank you

Malekmac,

I take it you are having a similar problem. The report that I was generating was a single line statement that summarised the number of products, the number of cases and the number of claims for each haulier. The problem I had was that I could get it to sum everything else apart from the number of claims. What I was getting was for each sku of a claim it was counting it as a fresh claim. What I had to do was within the hidden part of the report, the part used to calculate the report, next to the field for claim number I set up a Text box with the value of =1. I then set the Running sum Property to over group. This meant that for each fresh claim for a haulier the number increased by one. I then put together a string wich was to be the actual report and pulled the last value of the field into it. The actual string looks like the following:-

="Summary for " & [Haulier] & " =" & Count(*) & " " & IIf(Count(*)=1,"SKU","SKU's") & " with a total number of cases equalling " & Sum([QTY]) & " across " & [rptClaimCount] & IIf([rptClaimCount]=1," Claim"," Claims")

Hope that helps