Good Day
I have clients with unique number and they are purchasing with credit balance so we have registry including all purchasing transactions over all the whole month and it is registered on rows
What I am seeking for is searching on the column / field related to transaction balance based on the unique number as I have maximum value per each client and purchasing transaction based on his grade and it must not exceed this maximum per client not per transaction
There is 2 maximum tier
• Tier per client : 80,000
• Tier per transaction grade as follow
o Grade A : 10,000
o Grade B : 80,000
Example 1
Area No 52 have 3 orders with 3 different dates so if we see we will found out 2 branches with same grade “A “ so the total for those 2 branches should exceed 10,000 so first branch based on oldest date ( 01-10-04 ) have balance 12,000 so we will take only 10,000 and second branch will be zero as we already reached maximum then third branch grade is “B” so this branch should not exceed 80,000 alone but if the same area have other branches balances so the current is 60,000 so it will be accepted because the client has other balance 10 K which will have remaining 70K
Area Name Area No Branch Start Date Grade Balance New Balance
South 52 Shams1 01-11-04 A 3,000 -
South 52 Shams3 01-10-04 A 12,000 10,000
South 52 Shams2 01-01-03 B 60,000 60,000
Example 2
Here area 62 have 3 orders with different dates
• Oldest date related to branch Fagr 1 with date 01-01-03 and grade A with balance 5 K so it wil be accepted because it is still less than maximum
• Then second date will 01-01-06 with grade B so the maximum for it should be 80 K if the total client have full balance but in this example we already took 5 K so the remaining is 75 K but this order with 55 k so we will accept full order
• Then third date 01-01-07 with grade B with balance 45 K here we have remaining in Grade “ B “ balance by 25K while in total client balance for all his branches only 20K so the third order which equal 45 K we will accept only 20K
Area Name Area No Branch Start Date Grade Balance New Balance
West 62 Fagr1 01-01-03 A 5,000 5,000
West 62 Fagr2 01-01-07 B 45,000 20,000
West 62 Fagr2 01-01-06 B 55,000 55,000
Note : I Already reached the required through excel file as per attached file included formula but due the data volume which exceed more than 500 K records , excel will not able to handle it so I am searching to work on it through Access
I hope to find solution and support here
Regards
I have clients with unique number and they are purchasing with credit balance so we have registry including all purchasing transactions over all the whole month and it is registered on rows
What I am seeking for is searching on the column / field related to transaction balance based on the unique number as I have maximum value per each client and purchasing transaction based on his grade and it must not exceed this maximum per client not per transaction
There is 2 maximum tier
• Tier per client : 80,000
• Tier per transaction grade as follow
o Grade A : 10,000
o Grade B : 80,000
Example 1
Area No 52 have 3 orders with 3 different dates so if we see we will found out 2 branches with same grade “A “ so the total for those 2 branches should exceed 10,000 so first branch based on oldest date ( 01-10-04 ) have balance 12,000 so we will take only 10,000 and second branch will be zero as we already reached maximum then third branch grade is “B” so this branch should not exceed 80,000 alone but if the same area have other branches balances so the current is 60,000 so it will be accepted because the client has other balance 10 K which will have remaining 70K
Area Name Area No Branch Start Date Grade Balance New Balance
South 52 Shams1 01-11-04 A 3,000 -
South 52 Shams3 01-10-04 A 12,000 10,000
South 52 Shams2 01-01-03 B 60,000 60,000
Example 2
Here area 62 have 3 orders with different dates
• Oldest date related to branch Fagr 1 with date 01-01-03 and grade A with balance 5 K so it wil be accepted because it is still less than maximum
• Then second date will 01-01-06 with grade B so the maximum for it should be 80 K if the total client have full balance but in this example we already took 5 K so the remaining is 75 K but this order with 55 k so we will accept full order
• Then third date 01-01-07 with grade B with balance 45 K here we have remaining in Grade “ B “ balance by 25K while in total client balance for all his branches only 20K so the third order which equal 45 K we will accept only 20K
Area Name Area No Branch Start Date Grade Balance New Balance
West 62 Fagr1 01-01-03 A 5,000 5,000
West 62 Fagr2 01-01-07 B 45,000 20,000
West 62 Fagr2 01-01-06 B 55,000 55,000
Note : I Already reached the required through excel file as per attached file included formula but due the data volume which exceed more than 500 K records , excel will not able to handle it so I am searching to work on it through Access
I hope to find solution and support here
Regards
