Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 20th, 2005, 11:12 AM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default counting unique entries

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
 
Old October 21st, 2005, 09:57 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old October 22nd, 2005, 06:30 AM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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
 
Old October 24th, 2005, 07:23 AM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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!
 
Old November 22nd, 2005, 03:24 PM
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to malekmac
Default

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

 
Old November 23rd, 2005, 07:01 PM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Blog Entries Tawanda BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 2 January 27th, 2007 03:53 PM
counting unique items malekmac Access 15 November 29th, 2005 04:20 PM
Counting unique orders rlull SQL Server 2000 1 November 12th, 2005 12:34 AM
Counting unique record dlamarche Access 5 March 22nd, 2005 08:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.