Need idea or way to To build Commission Report 35 % from profit (1 Viewer)

AHMEDRASHED

Member
Local time
Today, 06:16
Joined
Feb 2, 2020
Messages
50
Hello
Need idea or way to To build Commission Report 35 % from the profit
(TOTAL Selling - TOTAL Bills ) * 35 % if invoice is Commission (checkbox) true

its multi Currency , Based Currency =AED , Exchange rate is diffrent from invoices to other


1691402690127.png
 

ebs17

Well-known member
Local time
Today, 05:16
Joined
Feb 7, 2020
Messages
2,022
Which ideas do you need exactly?
Calculating percentages and converting into other currencies is the simplest of mathematics.

Instead of looking at big colorful pictures, one should be clear about the processes of the invoices - on paper, then at a higher level in a query, in order to then give the colorful pictures or the forms content.
 

AHMEDRASHED

Member
Local time
Today, 06:16
Joined
Feb 2, 2020
Messages
50
Which ideas do you need exactly?
Calculating percentages and converting into other currencies is the simplest of mathematics.

Instead of looking at big colorful pictures, one should be clear about the processes of the invoices - on paper, then at a higher level in a query, in order to then give the colorful pictures or the forms content.

i try before to added two new calculated fields: SumOfSellingPriceAED and SumOfPurchaseAmountAED. These fields calculate the total selling price and total purchase amount converted to AED using the aedX IF IT USD USE USDX , IF IT SYP USE SYPX , IF IT EUR USE EURX


1691407327888.png


SELECT DISTINCT tblInvoice.InvoiceNumber, tblInvoice.InvoiceDate, tblInvoice.CustomersName, tblInvoice.eurX, tblInvoice.sypX, tblInvoice.usdX, tblInvoice.IsCommission, Sum(tblSelling.SellingPrice) AS SumOfSellingPrice, First(tblSelling.SellingCurrency) AS FirstOfSellingCurrency, tblBills.CurrencyType, tblBills.PurchaseAmount
FROM (tblInvoice INNER JOIN tblSelling ON tblInvoice.InvID = tblSelling.SellingID) INNER JOIN tblBills ON tblInvoice.InvID = tblBills.BillID
GROUP BY tblInvoice.InvoiceNumber, tblInvoice.InvoiceDate, tblInvoice.CustomersName, tblInvoice.eurX, tblInvoice.sypX, tblInvoice.usdX, tblInvoice.IsCommission, tblBills.CurrencyType, tblBills.PurchaseAmount
HAVING (((tblInvoice.IsCommission)=True));


1691406279738.png

1691406257793.png
 

ebs17

Well-known member
Local time
Today, 05:16
Joined
Feb 7, 2020
Messages
2,022
Exchange rate is diffrent
Of course. Therefore, I would expect a course table with daily updated exchange rates, if only for one's own historicization of the event.

Based Currency =AED
At the latest in a query, I would run the invoice currency and base currency side by side with values related to the exchange rate day. Whether you show both in one form would have to be answered on a case-by-case basis. In any case, one could calculate very simply without falling into deep if-then cascades.
 

plog

Banishment Pending
Local time
Yesterday, 22:16
Joined
May 11, 2011
Messages
11,694
Access doesn't use CASE it uses SWITCH:


Also, 'First' is a horrible function and often doesn't perform as the coder expects. My suggestion would be to make a sub query using tblSelling, do all the calculations you need in it then use it in your query instead.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
43,720
Doing this in the report is incorrect. It doesn't allow for historical reporting where the commission percentage may change over time as well as the exchange rate. A better solution is to calculate the commissions monthly or weekly or whenever they are supposed to be paid. Run the query to calculate the commission and create records from it to append to the commissions table. Then the report doesn't do any calculation. It simply selects the data from the commissions table based on a date parameter.
 

AHMEDRASHED

Member
Local time
Today, 06:16
Joined
Feb 2, 2020
Messages
50
Doing this in the report is incorrect. It doesn't allow for historical reporting where the commission percentage may change over time as well as the exchange rate. A better solution is to calculate the commissions monthly or weekly or whenever they are supposed to be paid. Run the query to calculate the commission and create records from it to append to the commissions table. Then the report doesn't do any calculation. It simply selects the data from the commissions table based on a date parameter.
Thank you for your advices . @Pat Hartman
 

Users who are viewing this thread

Top Bottom