Maximum value for repeated unique code

moryali

New Member
Joined
Mar 15, 2018
Messages
12
Good Day

Thanks to note i have current open subject in Excel forum under the following link

https://www.mrexcel.com/forum/excel...m-value-repeated-unique-code.html#post5031325

I already reached partial results in the excel forum with full support with our colleagues their

so what is requested is to try to apply this formula on Access inside query because i tried to apply it on excel for file include more than 200 K records but i failed so i need to transfer the implementation on access in addtion to if we can complete what is missing in the formula inside ACCESS

Worksheet Formulas
CellFormula
G2=MEDIAN(0,F2,IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"B"),80000,10000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--(INT($D$2:$D$12)+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000)))


<tbody>
</tbody>


<tbody>
</tbody>


Access File Link

https://ufile.io/mkaho

full details on the required formula discussion inside the Excel thread

Best Regards
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I would suggest you complete the work in Excel first. It will be confusing to try to do this in two forums at the same time, and the Excel version is not being reported as complete yet. Also just to be clear there is no guarantee that putting this in Access will make it faster.
 
Upvote 0
I would suggest you complete the work in Excel first. It will be confusing to try to do this in two forums at the same time, and the Excel version is not being reported as complete yet. Also just to be clear there is no guarantee that putting this in Access will make it faster.

Usual Access is faster than Excel with big volume of data especial excel take a lot of time in calculating formulas and my trial was just work by the current Formula i had it from Excel forum and complete the remaining manually till I reach new results because we may didn't reach any new idea so i didn't prefer to stop the work and to be in phases

Thanks for your cooperation and answer
 
Upvote 0
Upvote 0
If I may offer a word of advice - I think you are assuming anyone who knows a decent amount about Access would be able to derive a solution based on what you've posted. While some might, I'll bet there's many who cannot. I for one believe myself to be somewhat capable in Access, but have absolutely no clue as to what you're trying to accomplish, even after skimming over the solution in the other thread. That's mainly because the formulas and functions are too complex for me to bother trying to figure out. So I think you are limiting the responses you will get.

I would give it a few days to see if that is or is not the case. If you get no help, then provide sample data, the desired outcome, and as much about the process as you think necessary. Likely you will find that you don't provide enough info on your first attempt. Then you should have provided some starting info for members of both camps - Access and Excel.
 
Last edited:
Upvote 0
Thank you Micron for your post and please let me attach to you file as you requested and i will explain to you the required process

Summary

  1. I have area with unique code so i am depending on this unique code to filter if the area is repeated or not
  2. If repeated i will check the grade filed as it is contain 2 grades as follow ( A & B ) so if
    1. Area grade is A so maximum balance should be not exceed 10,000 , what if area have 2 orders by grade A so total of both areas will be not exceed 10,000 that is means area have maximum cap by 10,000 whatever the number of orders and we depending on launching date ( come first , will be , out first in calculation )
    2. Area grade is B so maximum balance should be not exceed 80,000 , what if area have 2 orders by grade B so total of both areas will be not exceed 80,000 that is means area have maximum cap by 80,000 whatever the number of orders and we depending on launching date ( come first , will be , out first in calculation )
    3. Area grade is mixed A & B so maximum balance should be not exceed 80,000 conditional that first cap of A is applied , what if area have 2 orders by grade A & B so total of both areas will be not exceed 80,000 that is means area have maximum cap by 80,000 whatever the number of orders so A will be by maximum 10,000 and B will be by maximum 80,000 and total will be up to 80,000 we depending on launching date ( come first , will be , out first in calculation )

Example Area 52
AreaArea NoBranchLaunch DateGradeBalanceNew Balance
South52Shams101-Nov-04A30000
South52Shams301-Oct-04A1200010000
South52Shams201-Jan-03B6000060000

<caption> Report </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Have 2 order under A and one order under B so total area will be 80,000 so we will start by oldest launching date it will be 01-Jan-03 and it is grade B and it is balance 60,000 so i will cover all balance 60,000 because it is not exceed 80,000 then i have remaining in cap 20,000 so i will go forward for next 01-Oct-04 and it is grade is A with balance 12,000 so i will take only 10,000 because the maximum cap for A is 10,000 third order is under grade A so it will be count and calculated by zero because i already consumed the maximum in A grade

Example Area 52
AreaArea NoBranchLaunch DateGradeBalanceNew Balance
West62Fagr101-Jan-03A50005000
West62Fagr201-Jan-07B4500020000
West62Fagr201-Jan-06B5500055000

<caption> Report </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Area have 3 order so i will take first launch date 01-Jan-03 and it is grade is A with balance 5,000 so i will take all balance because i didn't reach maximum cap of A grade
Then go for next launch date it will be 01-Jan-06 it is grade B with balance 55,000 so it will take all balance as i didn't reach maximum balance of grade B 80,000 so i will go for third order it is grade B with balance 45,000 so i will not take the whole balance because the remaining is only 20,000 because the total area is 80,000

Access File Link

https://ufile.io/g8fi6

Regards
 
Upvote 0
Very difficult to follow the logic. Sorry, but I'm thinking this is a bit too involved for me. If I change my mind and manage to delve into it a bit more, I will post back if I have any questions or suggestions, but I'm not very hopeful at the moment.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top