R
Rose
I'm not quite sure where to start on this one.
I have a table with the following fields: ordernum, badgenum, role_title
(which I'll need to trim first due to minor variances), adj_amount
This table is a list of all manual adjustments for commission. Adj_amount is
either 1 or -1 and represents whether someone (represented by badgenum)
gained or lost commission.
In the ideal situation there should be, per ordernum found, one record to
take commission away from one person, and then another record to give it to
another person with the same role_title. But we know this isn't happening a
lot...there's a lot of records for a -1 adjustment with no matching positive
adjustment, frequently on ordernums that do include a correct match. There's
also a few hanging positive adjustments; thirdly, there's some records with a
negative adjustment to a sales rep and a positive adjustment to an account
exec, which isn't a match.
So, the purpose of this is to be able to
1) query to find all records that do have a correct match (pos to neg on
same ordernum for same role_title)
2) query all the records that don't have a match.
(Since there will be so many records without a match, I'll end up doing a
count of records per badgenum afterwards on the second query, but that's a
seperate query.)
I asked earlier about how to match records using multiple fields and got a
good answer, but the fact that match in adj_amount means a 1 and a -1 in this
case makes this difficult. Help!
I have a table with the following fields: ordernum, badgenum, role_title
(which I'll need to trim first due to minor variances), adj_amount
This table is a list of all manual adjustments for commission. Adj_amount is
either 1 or -1 and represents whether someone (represented by badgenum)
gained or lost commission.
In the ideal situation there should be, per ordernum found, one record to
take commission away from one person, and then another record to give it to
another person with the same role_title. But we know this isn't happening a
lot...there's a lot of records for a -1 adjustment with no matching positive
adjustment, frequently on ordernums that do include a correct match. There's
also a few hanging positive adjustments; thirdly, there's some records with a
negative adjustment to a sales rep and a positive adjustment to an account
exec, which isn't a match.
So, the purpose of this is to be able to
1) query to find all records that do have a correct match (pos to neg on
same ordernum for same role_title)
2) query all the records that don't have a match.
(Since there will be so many records without a match, I'll end up doing a
count of records per badgenum afterwards on the second query, but that's a
seperate query.)
I asked earlier about how to match records using multiple fields and got a
good answer, but the fact that match in adj_amount means a 1 and a -1 in this
case makes this difficult. Help!