Sum and Count being applied before UniqueValues property 1

[grnzbra]

I have an totals query being driven by aother query with a number of fields. Two of the records in the source query are exactly the same except for one field. The totals query only uses four of the fields, none of which are the one field that has different values in these two records. The totals query is counting both of these records instead of just one and the value field is being added twice. The UniqueValues property is set to "Yes" in the totals query. Also, when the totaling is turned off, it only sees one of the records.

So I assume that in the totaling mode, it does its totaling and then looks for duplicate records. Is there any way to set this up to look for the duplicate records before doing the totaling?

Here is the SQL:

SELECT DISTINCT qry8570CurBrokSalesDays.ReportingDivisionNo, qry8570CurBrokSalesDays.RegionalBroker, Count(qry8570CurBrokSalesDays.Sale_No) AS CntOfSales, Sum(qry8570CurBrokSalesDays.DaysListToAcc) AS TotOfDays
FROM qry8570CurBrokSalesDays
GROUP BY qry8570CurBrokSalesDays.ReportingDivisionNo, qry8570CurBrokSalesDays.RegionalBroker
HAVING (((qry8570CurBrokSalesDays.RegionalBroker) Is Not Null));

 

[rac2]

DISTINCT has no meaning in a GROUP BY query because the GROUP BY yields one row for each different combination of values in the listed columns. In this case for each combination of division and broker.

When you specify a column in the COUNT() function you mean to count only rows which have a value in that column. This is useful when a column can have NULL instead of a value. Or, when looking at a column in an outer join. Otherwise you can use COUNT(*) to count every row.


You might make some progress on this by creating a query that has just the rows that you want to count. This query might use DISTINCT to combine the underlying two rows into a single row based on the values in the four fields. Then the GROUP BY query would have the right rows to work with.

 

[grnzbra]

Thank you. That's what I was thinking of doing. It's just that this report has about 40 or 50 queries driving it. I was hoping to avoid having to add another 12 queries to it (There are 12 totals queries that all have the potential for the same problem).

 

[JonFer]

I was curious why there are so many queries. Are there a lot of sub-reports or something? Post examples of a few of the queries or describe what a few are doing because maybe they can be combined.

 

[grnzbra]

Each column on the report is a sub report. When I was given the report to do, no one could tell me the calculations for the various columns. All I was told was that this column came from that report and that column came from that other report.

I tried to combine them into one query but after a couple days I was told the numbers were all wrong and that I should hurry up and get it done and use the original report queries for the sources. So that's what I did. They're happy with it (except when something like this occurs) and would not tolerate me trying to "fix" it.