Formula and Table Joining in a Query

Hi,

I have two tables - one has all of my company sales data with many columns
and the other has a row for each state, rep, product combination. The second
table is just supposed to show which products and in which states each rep is
allowed to sell. So, for example, rep Michael who is allowed to sell products
X, Y, X in NY and NJ, would have six row entries in the table, one for each
possible combination.

I am trying to write a formula (as part of a larger query) that will write
Correct/Incorrect for each sale in the sales data table. If the
rep/state/product combination in the sales table is also in the lookup table
then the formula should return "correct." Otherwise, the formula should
return "incorrect."

I tried to accomplish this by adding a column called Label to the second
table that said "correct" and then left joining the two tables on
rep/state/product. In the query, I wrote, IIF(label"",label,"Incorrect).
Although the formula worked, my resulting table had 470,000 rows even though
my actual sales data table only had 340,000. Does anyone have any idea why
this might be happening? Is there an easier way to accomplish what I'm trying
to do?

Thanks,
Michael

Here [Michael_R-1] is the sales table and [Michael_R] is the lookup for who
can sale what/where.

SELECT [Michael_R-1].*
FROM [Michael_R-1] LEFT JOIN Michael_R ON ([Michael_R-1].Product =
Michael_R.Product) AND ([Michael_R-1].Rep = Michael_R.Rep) AND
([Michael_R-1].State = Michael_R.State)
WHERE (((Michael_R.State) Is Null)) OR (((Michael_R.Rep) Is Null)) OR
(((Michael_R.Product) Is Null));

--
KARL DEWEY
Build a little - Test a little


"MichaelR" wrote:

Hi,

I have two tables - one has all of my company sales data with many columns
and the other has a row for each state, rep, product combination. The second
table is just supposed to show which products and in which states each rep is
allowed to sell. So, for example, rep Michael who is allowed to sell products
X, Y, X in NY and NJ, would have six row entries in the table, one for each
possible combination.

I am trying to write a formula (as part of a larger query) that will write
Correct/Incorrect for each sale in the sales data table. If the
rep/state/product combination in the sales table is also in the lookup table
then the formula should return "correct." Otherwise, the formula should
return "incorrect."

I tried to accomplish this by adding a column called Label to the second
table that said "correct" and then left joining the two tables on
rep/state/product. In the query, I wrote, IIF(label"",label,"Incorrect).
Although the formula worked, my resulting table had 470,000 rows even though
my actual sales data table only had 340,000. Does anyone have any idea why
this might be happening? Is there an easier way to accomplish what I'm trying
to do?

Thanks,
Michael

Karl, how would I write the "WHERE (((Michael_R.State) Is Null)) OR
(((Michael_R.Rep) Is Null)) OR (((Michael_R.Product) Is Null)); in design
view?

Thanks.

"KARL DEWEY" wrote:

Here [Michael_R-1] is the sales table and [Michael_R] is the lookup for who
can sale what/where.

SELECT [Michael_R-1].*
FROM [Michael_R-1] LEFT JOIN Michael_R ON ([Michael_R-1].Product =
Michael_R.Product) AND ([Michael_R-1].Rep = Michael_R.Rep) AND
([Michael_R-1].State = Michael_R.State)
WHERE (((Michael_R.State) Is Null)) OR (((Michael_R.Rep) Is Null)) OR
(((Michael_R.Product) Is Null));

--
KARL DEWEY
Build a little - Test a little


"MichaelR" wrote:

Hi,

I have two tables - one has all of my company sales data with many columns
and the other has a row for each state, rep, product combination. The second
table is just supposed to show which products and in which states each rep is
allowed to sell. So, for example, rep Michael who is allowed to sell products
X, Y, X in NY and NJ, would have six row entries in the table, one for each
possible combination.

I am trying to write a formula (as part of a larger query) that will write
Correct/Incorrect for each sale in the sales data table. If the
rep/state/product combination in the sales table is also in the lookup table
then the formula should return "correct." Otherwise, the formula should
return "incorrect."

I tried to accomplish this by adding a column called Label to the second
table that said "correct" and then left joining the two tables on
rep/state/product. In the query, I wrote, IIF(label"",label,"Incorrect).
Although the formula worked, my resulting table had 470,000 rows even though
my actual sales data table only had 340,000. Does anyone have any idea why
this might be happening? Is there an easier way to accomplish what I'm trying
to do?

Thanks,
Michael

Put the Is Null on a separate criteria row for each.
--
KARL DEWEY
Build a little - Test a little


"MichaelR" wrote:

Karl, how would I write the "WHERE (((Michael_R.State) Is Null)) OR
(((Michael_R.Rep) Is Null)) OR (((Michael_R.Product) Is Null)); in design
view?

Thanks.

"KARL DEWEY" wrote:

Here [Michael_R-1] is the sales table and [Michael_R] is the lookup for who
can sale what/where.

SELECT [Michael_R-1].*
FROM [Michael_R-1] LEFT JOIN Michael_R ON ([Michael_R-1].Product =
Michael_R.Product) AND ([Michael_R-1].Rep = Michael_R.Rep) AND
([Michael_R-1].State = Michael_R.State)
WHERE (((Michael_R.State) Is Null)) OR (((Michael_R.Rep) Is Null)) OR
(((Michael_R.Product) Is Null));

--
KARL DEWEY
Build a little - Test a little


"MichaelR" wrote:

Hi,

I have two tables - one has all of my company sales data with many columns
and the other has a row for each state, rep, product combination. The second
table is just supposed to show which products and in which states each rep is
allowed to sell. So, for example, rep Michael who is allowed to sell products
X, Y, X in NY and NJ, would have six row entries in the table, one for each
possible combination.

I am trying to write a formula (as part of a larger query) that will write
Correct/Incorrect for each sale in the sales data table. If the
rep/state/product combination in the sales table is also in the lookup table
then the formula should return "correct." Otherwise, the formula should
return "incorrect."

I tried to accomplish this by adding a column called Label to the second
table that said "correct" and then left joining the two tables on
rep/state/product. In the query, I wrote, IIF(label"",label,"Incorrect).
Although the formula worked, my resulting table had 470,000 rows even though
my actual sales data table only had 340,000. Does anyone have any idea why
this might be happening? Is there an easier way to accomplish what I'm trying
to do?

Thanks,
Michael

Karl, I tried to run the query within my query but it returned a blank table.
I think that I might not have explained what I was trying to accomplish as
well as I could have.

What I need this query to do is to make a column in the resulting query
table that says whether each row from the original sales data table was
correctly booked or miscorrectly booked based on whether the sale was in a
state and product that the rep represented.

Thanks and sorry for the confusion. Michael