stop sum counting the same record twice

[kt1978]

I'll try and explain this the best I can...

Basically I want to stop access counting the same record multiple times

I have two tables checks and errors. A check can have several error types

check  units  errors   rate    reason
 ID1     10      2         20%    overs
 ID1     10      1         10%    unders
 ID1     10      1         10%    damaged

If I put the grouping on the id level and use the formula below i get the correct total as follows

units errors rate
10 4 40%

=Sum([UnitsChecked])/Count([UnitsChecked])

However, I want to group by day and have the daily totals and also have an overall report total.

When I do this it is counting the errors as 30 instead of 10. I think i need to do something like sum the textbox with the formula above but I can't sum a calculated text box...can I?

I hope this makes sense.

Cheers

 

[kt1978]

I thought one way around this my be to use DSUM and create a query just with the daily sums

I tried the following but it just comes up with an #error

=DSum("[UnitsChecked]","qtyTotals","DayName=" & [DayName])

Any ideas people..

 

[kt1978]

I think I have solved this

In the day footer I have this DSum in the text box. I had to use the ' to get it to work.

=DSum("UnitsChecked","qryTotals","[DayName]='" & [DayName] & "'")

in the week footer I use

=DSum("UnitsChecked","qryTotals","[Week]=" & [Week])

Still please post if there is another way of doing this...

 

[gemma-the-husky]

the best way is to consider your data, and construct a query that selects the items you need - so you dont have to "finagle" the report

now, one option in a query is to select unique values - so you can use this idea instead, to exclude duplicates.

 

[vbaInet]

I don't see how you would get double figures if the grouping is setup correctly and the sum textbox is placed in the right section.

Can you upload a stripped down copy of your db (with some data for testing)?

 

[kt1978]

Hi

Thanks for the replies.

I have only selected the data i require for the report but the problem with the grouping is that it can't group the data as the rows are not identical.

The only thing I can think is that I have set up my tables wrong.

I have got it working now by creating the other totals query, but am interested to know if I have done something fundamentally wrong.

As requested here is a completely stripped down version with all but two table removed. I have put some duff data in as in my original db I have look ups etc for options tables.

It shows what my problem is in rptErrorsOLD and my solution in rptErrorsNEW.

Your feedback/solution is welcome

 

[vbaInet]

I took out the Hide Duplicates set on some of the controls and I get the attached set of records.

I don't see anything wrong with your tables but there's something wrong with your request. The UnitsChecked field is adding up correctly which is 234 + 234 + 234 + 54 = 756, it's not tripling.

But what you want is 234 + 54 = 288, that is one UnitsChecked result per Employee. If you want that kind of setup then you need to exclude all the fields after UnitsChecked and use the DISTINCT clause in the SQL statement of the report's record source:

http://www.techonthenet.com/sql/distinct.php

 

[kt1978]

Hi

Yeah, poor explanation on my part around the tripling part. I just meant that is was counting the same check multiple time dependant upon how many errors there were.

I don't see how your solution will fit my purpose. If I use the distinct method how will I still be able to show all the errors for the check (i.e. 3) whilst only counting the check once.

If I'm misunderstood, would you mind modifying the sample db and posting back on. Or is my DSUM solution the way to go...

 

[vbaInet]

See attached.

 

[kt1978]

Hi

Ahh, see what you mean now. Didn't think of the 2 reports combined, not done that before, just found other ways around it.

Will definately change to your solution.

Thanks again..

 

[vbaInet]

You're welcome

In place of the code for getting the Sum of InErrors, you can use just a DSum() function like you did.

Happy coding!