Form not summing correctly

  • Thread starter Thread starter Ray Ash
  • Start date Start date
R

Ray Ash

Greetings,

I recently took over an Access database, and a formula
that my predecessor designed for a report does not seem to
be working correctly. I cannot figure out why, but the
field in the report is supposed to display a total from a
SUM calculation that comes from two other fields in
different queries. One field is entitled "consumers" and
the other is entitled "PDC" (I know that won't mean much
to you but there it is anyway). The SUM function works
just fine, EXCEPT when the total of "consumers" and "PDC"
is the same number i.e. consumer = 29 and PDC = 29.
Whenever these two values are identical the report simply
displays the value without summing, i.e. instead of
displaying 58 (the SUM of 29 + 29) in simply displays 29.

Can anyone possibly help me with this? I will be happy to
clarify any of this if need be...I'm at my wits' end!
 
Ray Ash said:
Greetings,

I recently took over an Access database, and a formula
that my predecessor designed for a report does not seem to
be working correctly. I cannot figure out why, but the
field in the report is supposed to display a total from a
SUM calculation that comes from two other fields in
different queries. One field is entitled "consumers" and
the other is entitled "PDC" (I know that won't mean much
to you but there it is anyway). The SUM function works
just fine, EXCEPT when the total of "consumers" and "PDC"
is the same number i.e. consumer = 29 and PDC = 29.
Whenever these two values are identical the report simply
displays the value without summing, i.e. instead of
displaying 58 (the SUM of 29 + 29) in simply displays 29.

Can anyone possibly help me with this? I will be happy to
clarify any of this if need be...I'm at my wits' end!
Click to expand...

Please post the exact formula used in the calculation. Am I right in
understanding that this is a calculated control on the report? What
report section is it in?
 
You sir are a genius! I don't know what they're paying
you but it isn't enough! It works!!!

A thousand thanks!

Ray
-----Original Message-----
Ray Ash said:
The formula in the report is =Sum([consumers])...this is
also the Control Source. I know this is going to be hard
for you to troubleshoot without seeing this poorly-
designed database for yourself, but I believe that this
report field's total is based on a union query with the
following SQL format:

select [attendancedate] as attend,[count of consumers] as
consumers from [Daily 1]

UNION select [attenddate] as attend, [total pdc] as total
from [Daily 2];

The Daily 1 query's SQL structure is:

SELECT [Attendance Information].AttendanceDate, Count
([Attendance Information].ConsumerID) AS [Count of
Consumers]
FROM [Attendance Information]
WHERE ((([Attendance Information].Services)="1799"))
GROUP BY [Attendance Information].AttendanceDate;

The Daily 2 query's SQL structure is:

SELECT Staffing.AttendDate, Sum(Staffing.PDC) AS [Total
PDC], Staffing.[Projected Staff], Staffing.NumofStaff
FROM Staffing
GROUP BY Staffing.AttendDate, Staffing.[Projected Staff],
Staffing.NumofStaff;

I am not an Access professional (wish I was) but from what
I can tell the report should generate output in the SUM
([consumers]) field that totals [count of consumers] and
[total pdc] in one field. As I said before, the current
config works fine as long as "count of consumers"
and "total pdc" are not the same amount.

Thanks in advance for your help.
Click to expand...

I believe I see the problem. The UNION operator in SQL discards
duplicate records. So if the two queries being "unioned" return two
records with the same date and the same count, only one of those records
will be returned.

Change the query to use UNION ALL instead, and the problem will
disappear:

select [attendancedate] as attend,[count of consumers] as consumers
from [Daily 1]
UNION ALL
select [attenddate] as attend, [total pdc] as total
from [Daily 2];

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top