UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Maximum Value For Repeated Unique Code, Access 2007    
 
   
Mory Ali
post Aug 11 2018, 01:57 AM
Post#1



Posts: 2
Joined: 11-August 18



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 70 K

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 File(s)
Attached File  Area___Access.zip ( 7.84K )Number of downloads: 1
Attached File  AREA.zip ( 21.8K )Number of downloads: 1
 
Go to the top of the page
 
isladogs
post Aug 11 2018, 02:51 AM
Post#2



Posts: 382
Joined: 4-June 18



Cross posted at another forum



--------------------
nil illigetimi carborundem est
Go to the top of the page
 
Kamulegeya
post Aug 11 2018, 05:14 AM
Post#3



Posts: 1,815
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hi isladogs,
Cross posting applies to UtterAccess forums. E.g I post a question both in Reports and Queries forums.

That what i thought..............

Ronald


Go to the top of the page
 
cheekybuddha
post Aug 11 2018, 05:47 AM
Post#4


UtterAccess VIP
Posts: 10,258
Joined: 6-December 03
From: Telegraph Hill


Hi Ronald,

>> That what i thought.............. <<

Yes, you are right .... or half-right!

Cross-posting the same post in different sub forums is not allowed. It doesn't help you/the poster get an answer any quicker, and is more likely to confuse the members here because they may not know whether an answer has been given already in the other thread. It can be irritating to answer a question that has already been answered.

Cross-posting between different forums is fine. However, since many members here also frequent several other Access forums too, it is polite to inform members you are asking that you have posted the same in another forum. Pretty much for the same reasons as above.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Aug 11 2018, 06:10 AM
Post#5



Posts: 382
Joined: 4-June 18



Yes I agree but it is also true that not all UA members frequent other forums.
The reason why it should be mentioned (and links provided) is so nobody wastes time repeating answers that have already been posted elsewhere.

An extreme case recently involved someone posting on at least 7 sites. In that case, the OP mentioned cross posting but gave no links
Multiple answers were given at several sites. Some were effectively duplicates.

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
GroverParkGeorge
post Aug 11 2018, 07:58 AM
Post#6


UA Admin
Posts: 33,251
Joined: 20-June 02
From: Newcastle, WA


Let's try to focus on what's important, addressing the OP's problem. Cross-posting is a side issue.

And that, to be honest, is a bit difficult. Part of that, of course, is a result of language differences, but partly it's because the terms being used are not defined clearly.

Please help by explaining what a "tier" is? Why are you interested in tiers?

What is a Grade? Why are you interested in Grades?

You have clients with unique numbers? What does that mean? Are these unique numbers assigned to your clients in order to identify them? And how does a "unique number" relate to things like "Area 52"? Is that the unique number you are talking about? or is that something else?


In order to get to the LOGIC required, I would like to get a better handle on the business problem we're trying to solve.

Thanks.

--------------------
Go to the top of the page
 
Mory Ali
post Aug 11 2018, 03:41 PM
Post#7



Posts: 2
Joined: 11-August 18



Sorry for cross posting and it is clear for me now so it will not repeated again

regarding to the below questions raised from you

What is a Grade? Why are you interested in Grades?

Grade : It is just classification way as type of credit classification because normally in the trade you must classify your clients based on their payment capability so here in my example there is 2 grades ( grade A is branch can credit orders up to 20 K while grade B for branch can credit order up to 80 K and in case of client in addition to client capping is 80 k because you must put maximum for each client per none cash order )



You have clients with unique numbers? What does that mean? Are these unique numbers assigned to your clients in order to identify them? And how does a "unique number" relate to things like "Area 52"? Is that the unique number you are talking about? or is that something else?


Unique number means that each client has serial as a code to be easy to identify them in addition facilitate in searching and link instead of search by name or link through letters noting that the example unique numbers mentioned in my files was 32 , 42 , 52 , 62 for example unique number 52 is related to name South


Noting that each client same as group which have branch so the unique number is taken for group name then we are deal with each branch according to his name and start date


** Excel sheet previous attached already included real example with excel formula which i need to perform the same through access
This post has been edited by Mory Ali: Aug 11 2018, 03:42 PM
Go to the top of the page
 
mike60smart
post Aug 12 2018, 06:15 AM
Post#8


UtterAccess VIP
Posts: 12,700
Joined: 6-June 05
From: Dunbar,Scotland


Hi

The Access file you uploaded needs to be normalised.

You have just copied an Excel file into an Access Table.

For your process you need a Minimum of 2 Tables

Table1 - Parent Table

tblAreas
-AreaID - Primary Key - Autonumber
-Area
-AreaNr

Table2 - Child Table which contains all Child Records for a Specific Parent

tblAreaBranches
-AreaBranchID - Primary Key - Autonumber
-AreaID - Foreign Key - Number - Duplicates OK (Linked to AreaID from tblAreas)
-Branch
-LaunchDate
-Grade
-Balance



--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th August 2018 - 02:41 PM