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
0000098529 Knoxville Boston 2/1/2004
0000024267 Pasadena Los Angeles 4/1/2004
0000096735 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 0000098529 that transfers twice like below...
tblAccountTransfers:
AccountNumber FROMTerritory TOTerritory EffectiveDate
0000098529 Knoxville Boston 2/1/2004
0000098529 Boston Madison 4/1/2004
0000024267 Pasadena Los Angeles 4/1/2004
0000096735 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 an alias 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 Boston 3 2004 $540.66
0000000016 Madison 4 2004 $506.08
0000000016 Madison 5 2004 $477.34
0000000016 Madison 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
0000098529 Knoxville Boston 2/1/2004
0000024267 Pasadena Los Angeles 4/1/2004
0000096735 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 0000098529 that transfers twice like below...
tblAccountTransfers:
AccountNumber FROMTerritory TOTerritory EffectiveDate
0000098529 Knoxville Boston 2/1/2004
0000098529 Boston Madison 4/1/2004
0000024267 Pasadena Los Angeles 4/1/2004
0000096735 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 an alias 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 Boston 3 2004 $540.66
0000000016 Madison 4 2004 $506.08
0000000016 Madison 5 2004 $477.34
0000000016 Madison 6 2004 $524.69