Solved How to divide total wage amount into currency notes.

[Khurramshaikh47]

Hi,
I need guidance for the Microsoft Access query in which I calculate the employee's wage.
I want to get the number of different currency notes making each wage value.
So it will be easy for me to distribute them.
Example:

Name	Total Wage	5000 Notes	1000 Notes	500 Notes	100 Notes	50 Notes	10 Notes	Less Then 10​
Employee 1​	16,765	3​	1​	1​	2​	1​	1​	5​
Employee 2​	7,670	1​	2​	1​	1​	1​	2​	0​
Employee 3​	9,500	1​	4​	1​	0​	0​	0​	0​
Employee 4​	10,000	2​	0​	0​	0​	0​	0​	0​
Employee 5​	3,000	0​	3​	0​	0​	0​	0​	0​
	Total Notes Required​	7​	10​	3​	3​	2​	3​	5​

Thank you in advance.

 

[The_Doc_Man]

I don't know of an elegant solution, but it is certainly amenable to a "ladder" of integer division. Because you are dealing with 7 categories (7 monetary denominations), that amounts to six division operations and six remaindering operations. Normally I would suggest writing a public function that could be called by SQL, but having 7 slots to fill, one function won't do the job in a single pass.

Depending on how many employees you are discussing, it might be few enough that writing a VBA routine to do the six integer divisions and six remainders would be adequately fast. This would be essentially a recordset-based loop stepping through the list of employees one at a time and doing the computations, updating the record, and continuing.

The other way you could do this involves adding one more field to your table - call it WageRemainder - that starts out equal to total wage, but then run six queries, each focusing on a different denomination. You would compute the integer number of notes of a particular denomination and store that, then remove that wage value from the WageRemainder and store the remainder. You would run six queries, one for each denomination, and the last query would be for the 10/less than 10 case.

Before I try to write up anything, I would like to see what other members suggest, because either choice would be tedious.

 

[Minty]

I think you could write this in one query, but the column calculations might exceed Access nesting abilities.

A single function that you passed the denomination to would work I think, based on the knowledge of the other denominations available.

 

[arnelgp]

you need a custom function for that.
call the function in a Query.

see query1

 

[Khurramshaikh47]

you need a custom function for that.
call the function in a Query.

see query1

Thank you so much arnel. You've solved my problem. As always.

Thank you again.