Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 10:38 PM   #1
Mory Ali
Newly Registered User
 
Join Date: Aug 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Mory Ali is on a distinguished road
Maximum value for repeated unique code

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
Attached Files
File Type: zip Area - Access.zip (7.8 KB, 6 views)
File Type: zip AREA.zip (21.8 KB, 4 views)

Mory Ali is offline   Reply With Quote
Old 08-10-2018, 11:51 PM   #2
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,805
Thanks: 79
Thanked 1,428 Times in 1,333 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Maximum value for repeated unique code

Cross posted at http://www.utteraccess.com/forum/ind...ic=2050307&hl=

Please see this link for more info about the etiquette of cross posting: https://www.excelguru.ca/content.php?184
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Repeated code fat controller Modules & VBA 3 11-26-2014 03:04 AM
Rewriting repeated blocks of code for combo boxes thebionicredneck General 5 05-10-2013 03:23 PM
Unique Number code agorman Queries 6 02-03-2009 01:35 AM
Code Repeated .... Can I use a function and call it ? jagstirling Modules & VBA 1 09-17-2008 12:06 AM
Suggestions for code for Unique id powellm1976 General 8 10-04-2002 01:26 PM




All times are GMT -8. The time now is 04:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World