Here is the SQL:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep],
Sum([qryMTD_C2RM]![SumOfCurrent_Revenue]+[qryMTD_C2RM]![SumOfJUL]+[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL]) AS Total
FROM (tblRVP_Mapping LEFT JOIN qryMTD_C2RM ON tblRVP_Mapping.[Sales Rep] =
qryMTD_C2RM.[Sales Rep]) LEFT JOIN qryMTD_Wanda ON tblRVP_Mapping.[Sales Rep]
= qryMTD_Wanda.[Sales Rep]
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep];
Basically, I have one table with sales reps, and ‘map’ to two other tables,
using some 'Left Joins'. Both tables (MTD_C2RM & MTD_Wanda) contain IDs that
match the IDs of the sales reps in the first table. MTD_C2RM has data for
current revenue and data for July sales. MTD_Wanda has data for current
revenue and data for July sales. I am trying to sum all revenue, from both
tables, for the Month To Date (MTD) and for the month of July. Access seems
to sum the MTD_C2RM fine when I do this:
Total: Sum([qryMTD_C2RM]![SumOfCurrent_Revenue]+[qryMTD_C2RM]![SumOfJUL])
However, it just ignores the data from MTD_Wanda when I add in the data from
the other table, like this:
Total:
Sum([qryMTD_C2RM]![SumOfCurrent_Revenue]+[qryMTD_C2RM]![SumOfJUL]+[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL])
I am kind of new to this world of Access. I suspect there is a simple
solution, but I just can’t see it. Hopefully someone sees my shortcoming.
Please let me know what to do.
Regards,
Ryan---
--
RyGuy
Ken Snell (MVP) said:
In order to understand what you mean by "truncated data", can you show us
examples of the original data and the resulting data from this query?
--
Ken Snell
<MS ACCESS MVP>
Below is my query:
Total:
Sum([qryMTD_C2RM]![SumOfCurrent_Revenue]+[qryMTD_C2RM]![SumOfJUL])+Sum([qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL])
It seems rather simple (it's all relative, isn't it). However, some data
is
always truncated and the results are always understated. Access seems to
just sum the first part (before the second sum). I've tried all kinds of
different combination of stuff. I just can't see what the problem is
here.
Do I need some kind of IIF or IsNull?
I'd really appreciate it if someone can help out.
Thanks,
Ryan---