M
mike
Hi. Any suggestions on this would be a huge help. I have a
table that shows what territory our accounts are assigned
to and what their quota is. It looks like this:
tblPlansByAccountOther:
AccountNumber BaseLineTerritory MonthID Year Plan
0000000016 Knoxville 1 2004 $477.42
0000000016 Knoxville 2 2004 $458.88
0000000016 Knoxville 3 2004 $540.66
0000000016 Knoxville 4 2004 $506.08
0000000016 Knoxville 5 2004 $477.34
0000000016 Knoxville 6 2004 $524.69
The problem is that sometimes accounts transfer from one
territory to another, so the future quotas must transfer
also. I haven't had any trouble handling this yet but I
anticipate that an account might transfer twice in the
same year and then my code will be inadequate. I keep
track of account transfers in a table that looks like this:
tblAccountTransfers:
AccountNumber FROMTerritory TOTerritory EffectiveDate
0000000016 Knoxville Boston 2/1/2004
0000000007 Pasadena Los Angeles 4/1/2004
0000000005 Portland Spokane 4/1/2004
I have a query that has a bit of code that handles the
transfer. If there's an account number in
tblAccountTransfers that matches an account number in
tblPlansByAccountOther, then if the quota's month is
greater than or equal to the effective transfer date, I
assign the account to the new territory. Otherwise, it
remains in its original territory. Here's the simple code:
Territory: IIf(tblPlansByAccountOther.MonthID>=DatePart
("m",tblAccountTransfers.EffectiveDate) And
tblPlansByAccountOther.Year=DatePart
("yyyy",tblAccountTransfers.EffectiveDate),tblAccountTransf
ers.TOTerritory,[BaseLineTerritory])
Here's the problem...if tblAccountTransfers has an account
like 0000000016 that transfers twice like below...
tblAccountTransfers:
AccountNumber FROMTerritory TOTerritory EffectiveDate
0000000016 Knoxville Boston 2/1/2004
0000000016 Boston Chicago 3/1/2004
0000000007 Pasadena Los Angeles 4/1/2004
0000000005 Portland Spokane 4/1/2004
.....I'm not sure how to write the code that would allocate
the plans so that the query result looks like below. I
imagine I'd use a subquery of some sort but I'm not
exactly sure how. Any suggestions would be absolutely
fantastic. Thanks!
tblPlansByAccountOther:
AccountNumber BaseLineTerritory MonthID Year Plan
0000000016 Knoxville 1 2004 $477.42
0000000016 Boston 2 2004 $458.88
0000000016 Chicago 3 2004 $540.66
0000000016 Chicago 4 2004 $506.08
0000000016 Chicago 5 2004 $477.34
0000000016 Chicago 6 2004 $524.69
table that shows what territory our accounts are assigned
to and what their quota is. It looks like this:
tblPlansByAccountOther:
AccountNumber BaseLineTerritory MonthID Year Plan
0000000016 Knoxville 1 2004 $477.42
0000000016 Knoxville 2 2004 $458.88
0000000016 Knoxville 3 2004 $540.66
0000000016 Knoxville 4 2004 $506.08
0000000016 Knoxville 5 2004 $477.34
0000000016 Knoxville 6 2004 $524.69
The problem is that sometimes accounts transfer from one
territory to another, so the future quotas must transfer
also. I haven't had any trouble handling this yet but I
anticipate that an account might transfer twice in the
same year and then my code will be inadequate. I keep
track of account transfers in a table that looks like this:
tblAccountTransfers:
AccountNumber FROMTerritory TOTerritory EffectiveDate
0000000016 Knoxville Boston 2/1/2004
0000000007 Pasadena Los Angeles 4/1/2004
0000000005 Portland Spokane 4/1/2004
I have a query that has a bit of code that handles the
transfer. If there's an account number in
tblAccountTransfers that matches an account number in
tblPlansByAccountOther, then if the quota's month is
greater than or equal to the effective transfer date, I
assign the account to the new territory. Otherwise, it
remains in its original territory. Here's the simple code:
Territory: IIf(tblPlansByAccountOther.MonthID>=DatePart
("m",tblAccountTransfers.EffectiveDate) And
tblPlansByAccountOther.Year=DatePart
("yyyy",tblAccountTransfers.EffectiveDate),tblAccountTransf
ers.TOTerritory,[BaseLineTerritory])
Here's the problem...if tblAccountTransfers has an account
like 0000000016 that transfers twice like below...
tblAccountTransfers:
AccountNumber FROMTerritory TOTerritory EffectiveDate
0000000016 Knoxville Boston 2/1/2004
0000000016 Boston Chicago 3/1/2004
0000000007 Pasadena Los Angeles 4/1/2004
0000000005 Portland Spokane 4/1/2004
.....I'm not sure how to write the code that would allocate
the plans so that the query result looks like below. I
imagine I'd use a subquery of some sort but I'm not
exactly sure how. Any suggestions would be absolutely
fantastic. Thanks!
tblPlansByAccountOther:
AccountNumber BaseLineTerritory MonthID Year Plan
0000000016 Knoxville 1 2004 $477.42
0000000016 Boston 2 2004 $458.88
0000000016 Chicago 3 2004 $540.66
0000000016 Chicago 4 2004 $506.08
0000000016 Chicago 5 2004 $477.34
0000000016 Chicago 6 2004 $524.69