Query is Truncating Data

[ryguy7272]

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---

 

[Ken Snell \(MVP\)]

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?

 

[Lord Kelvan]

it may be doing it because of the ! try using . insted

 

[ryguy7272]

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

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---

 

[ryguy7272]

I just tried the '.' thing; it didn't work.

 

[Lord Kelvan]

Was this that query i worked out for you by using three different
querys?

Because of the cartesian product as i said it is doing that because it
joins two of the tables then the results to the thrid table creating
the cartesian product.

you wont be able to avoid it simply. Did the multiple queries not work
out for you or are you trying something different now?

 

[ryguy7272]

Hello, again, LK. Well, yes it is kind of related to the issue from last
week. I created two simple queries. The first query is a sales rep table,
with IDs mapped to IDs in a sales region table. The second query is that
sales rep table, with IDs mapped to a different sales region. The numbers in
these two queries come out fine. Now, I am trying to bring these results
together, in one more query. Most of the sales, probably 95% of the
business, comes from one sales region, and I just glanced at a few numbers
and they looked right. I just noticed that about 5% of the sales, all from
the same region, are missing in my query.
This all comes from here:
[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL]

Access just totally ignores this part, so some of the revenue is
understated. I don't know how else to describe it other than the way I
described it above. There must be an easy fix for this thing, I'm just not
seeing it now.

--
RyGuy

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

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---

 

[Dale Fye]

If there is any chance that any one of the 4 columns in your summation query
is NULL, then the sum of those columns will be NULL also. So, I think you
are going to need to wrap your query columns in the NZ( ) function, to make
sure that none of the columns is NULL. Something like:

Sum(NZ([qryMTD_C2RM]![SumOfCurrent_Revenue], 0) + _
NZ([qryMTD_C2RM]![SumOfJUL], 0) + _
NZ([qryMTD_Wanda]![SumOfCUR_MTD_REV], 0) + _
NZ([qryMTD_Wanda]![SumOfJUL], 0)) AS Total

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello, again, LK. Well, yes it is kind of related to the issue from last
week. I created two simple queries. The first query is a sales rep table,
with IDs mapped to IDs in a sales region table. The second query is that
sales rep table, with IDs mapped to a different sales region. The numbers in
these two queries come out fine. Now, I am trying to bring these results
together, in one more query. Most of the sales, probably 95% of the
business, comes from one sales region, and I just glanced at a few numbers
and they looked right. I just noticed that about 5% of the sales, all from
the same region, are missing in my query.
This all comes from here:
[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL]

Access just totally ignores this part, so some of the revenue is
understated. I don't know how else to describe it other than the way I
described it above. There must be an easy fix for this thing, I'm just not
seeing it now.

--
RyGuy

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

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---

 

[ryguy7272]

That's it! Why does Access require that? That does not seem very intuitive.
I thought Nulls would simply get passed over. Access seemed to be
subtracting out the rows of data with these Nulls. Can someone please
explain the purpose of this thing?


Thanks so much,
Ryan---

--
RyGuy

If there is any chance that any one of the 4 columns in your summation query
is NULL, then the sum of those columns will be NULL also. So, I think you
are going to need to wrap your query columns in the NZ( ) function, to make
sure that none of the columns is NULL. Something like:

Sum(NZ([qryMTD_C2RM]![SumOfCurrent_Revenue], 0) + _
NZ([qryMTD_C2RM]![SumOfJUL], 0) + _
NZ([qryMTD_Wanda]![SumOfCUR_MTD_REV], 0) + _
NZ([qryMTD_Wanda]![SumOfJUL], 0)) AS Total

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello, again, LK. Well, yes it is kind of related to the issue from last
week. I created two simple queries. The first query is a sales rep table,
with IDs mapped to IDs in a sales region table. The second query is that
sales rep table, with IDs mapped to a different sales region. The numbers in
these two queries come out fine. Now, I am trying to bring these results
together, in one more query. Most of the sales, probably 95% of the
business, comes from one sales region, and I just glanced at a few numbers
and they looked right. I just noticed that about 5% of the sales, all from
the same region, are missing in my query.
This all comes from here:
[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL]

Access just totally ignores this part, so some of the revenue is
understated. I don't know how else to describe it other than the way I
described it above. There must be an easy fix for this thing, I'm just not
seeing it now.

--
RyGuy

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


:

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---

 

[Dale Fye]

Access is not alone in this, it applies to every relational database that I
know of.

NULL, to most database professionals, means "is not known", so when you add
a number to an unknown, you get, ..... another unknown.

So, anytime you want to add numbers from a query, you need to account for
the possibility that one of the numbers is NULL, and transform it to a zero.

Glad I could help.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

That's it! Why does Access require that? That does not seem very intuitive.
I thought Nulls would simply get passed over. Access seemed to be
subtracting out the rows of data with these Nulls. Can someone please
explain the purpose of this thing?


Thanks so much,
Ryan---

--
RyGuy

If there is any chance that any one of the 4 columns in your summation query
is NULL, then the sum of those columns will be NULL also. So, I think you
are going to need to wrap your query columns in the NZ( ) function, to make
sure that none of the columns is NULL. Something like:

Sum(NZ([qryMTD_C2RM]![SumOfCurrent_Revenue], 0) + _
NZ([qryMTD_C2RM]![SumOfJUL], 0) + _
NZ([qryMTD_Wanda]![SumOfCUR_MTD_REV], 0) + _
NZ([qryMTD_Wanda]![SumOfJUL], 0)) AS Total

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello, again, LK. Well, yes it is kind of related to the issue from last
week. I created two simple queries. The first query is a sales rep table,
with IDs mapped to IDs in a sales region table. The second query is that
sales rep table, with IDs mapped to a different sales region. The numbers in
these two queries come out fine. Now, I am trying to bring these results
together, in one more query. Most of the sales, probably 95% of the
business, comes from one sales region, and I just glanced at a few numbers
and they looked right. I just noticed that about 5% of the sales, all from
the same region, are missing in my query.
This all comes from here:
[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL]

Access just totally ignores this part, so some of the revenue is
understated. I don't know how else to describe it other than the way I
described it above. There must be an easy fix for this thing, I'm just not
seeing it now.

--
RyGuy


:

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


:

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---

 

[ryguy7272]

Ah, now it makes more sense. I'm new to Access. I worked in finance for
several years and did Excel/VBA programming for a few years. Recently I'm
finding myself exceeding the limits of Excel, and becoming more involved in
Access types of projects. I bought the Access 2007 Bible a few months ago;
it devotes all of two sentences to the NZ() function. I think the book
provides some good overall knowledge of Access, but this Discussion Group is
infinitely better when people have specific questions about real-world
problems.

Thanks for the information Dale!
Ryan--



--
RyGuy

Access is not alone in this, it applies to every relational database that I
know of.

NULL, to most database professionals, means "is not known", so when you add
a number to an unknown, you get, ..... another unknown.

So, anytime you want to add numbers from a query, you need to account for
the possibility that one of the numbers is NULL, and transform it to a zero.

Glad I could help.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

That's it! Why does Access require that? That does not seem very intuitive.
I thought Nulls would simply get passed over. Access seemed to be
subtracting out the rows of data with these Nulls. Can someone please
explain the purpose of this thing?


Thanks so much,
Ryan---

--
RyGuy

If there is any chance that any one of the 4 columns in your summation query
is NULL, then the sum of those columns will be NULL also. So, I think you
are going to need to wrap your query columns in the NZ( ) function, to make
sure that none of the columns is NULL. Something like:

Sum(NZ([qryMTD_C2RM]![SumOfCurrent_Revenue], 0) + _
NZ([qryMTD_C2RM]![SumOfJUL], 0) + _
NZ([qryMTD_Wanda]![SumOfCUR_MTD_REV], 0) + _
NZ([qryMTD_Wanda]![SumOfJUL], 0)) AS Total

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hello, again, LK. Well, yes it is kind of related to the issue from last
week. I created two simple queries. The first query is a sales rep table,
with IDs mapped to IDs in a sales region table. The second query is that
sales rep table, with IDs mapped to a different sales region. The numbers in
these two queries come out fine. Now, I am trying to bring these results
together, in one more query. Most of the sales, probably 95% of the
business, comes from one sales region, and I just glanced at a few numbers
and they looked right. I just noticed that about 5% of the sales, all from
the same region, are missing in my query.
This all comes from here:
[qryMTD_Wanda]![SumOfCUR_MTD_REV]+[qryMTD_Wanda]![SumOfJUL]

Access just totally ignores this part, so some of the revenue is
understated. I don't know how else to describe it other than the way I
described it above. There must be an easy fix for this thing, I'm just not
seeing it now.

--
RyGuy


:

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


:

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---