M
MichaelR
Hi,
I have two tables - one has all of my company sales data (MCB Sales Data)
with many columns and the other (Rep State Product Assignment) has the states
and products that each reps are assigned to. The "Rep State Product
Assignment" Table has 6 columns, 5 of which are exactly the same as columns
in the "MCB Sales Data" table. The 6th column just says "Correctly Booked"
for all rows. The purpose of having the second table is that oftentimes, a
sale will be input into the system incorrectly (meaning that a sale will be
attributed to a rep for a product that doesn't belong to him/her or in a
state that he/she isn't assigned to.
What I would like to do is write a query that looks through the five columns
in the MCB Sales Data Table that are the same as those in the Rep State
Product Assignment Table and whenever the combination of the 5 fields in one
row has a match in the Rep State Product Assignment Table, write "Correctly
Booked in a column of its own (for that row). Otherwise, if those 5 criteria
don't have a match in the MCB Sales Data Table then write "Misbooked" in the
new column.
I tried to do this by left joining the two tables on the five criteria and
among other things, asking the query to return the 6th field in the Rep State
Product Assignment Table. The problem was that my query table, which
originally had 340,852 rows (the same number of rows as the MCB Sales Data
Table) now had 473,892 rows.
Any ideas for how I can achieve what I'm trying to accomplish or why my rows
got increased by so much?
Thanks,
Michael
I have two tables - one has all of my company sales data (MCB Sales Data)
with many columns and the other (Rep State Product Assignment) has the states
and products that each reps are assigned to. The "Rep State Product
Assignment" Table has 6 columns, 5 of which are exactly the same as columns
in the "MCB Sales Data" table. The 6th column just says "Correctly Booked"
for all rows. The purpose of having the second table is that oftentimes, a
sale will be input into the system incorrectly (meaning that a sale will be
attributed to a rep for a product that doesn't belong to him/her or in a
state that he/she isn't assigned to.
What I would like to do is write a query that looks through the five columns
in the MCB Sales Data Table that are the same as those in the Rep State
Product Assignment Table and whenever the combination of the 5 fields in one
row has a match in the Rep State Product Assignment Table, write "Correctly
Booked in a column of its own (for that row). Otherwise, if those 5 criteria
don't have a match in the MCB Sales Data Table then write "Misbooked" in the
new column.
I tried to do this by left joining the two tables on the five criteria and
among other things, asking the query to return the 6th field in the Rep State
Product Assignment Table. The problem was that my query table, which
originally had 340,852 rows (the same number of rows as the MCB Sales Data
Table) now had 473,892 rows.
Any ideas for how I can achieve what I'm trying to accomplish or why my rows
got increased by so much?
Thanks,
Michael