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

[AHMEDRASHED]

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

 

[ebs17]

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]

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

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));

 

[ebs17]

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]

Access doesn't use CASE it uses SWITCH:

MS Access: Switch Function

This MSAccess tutorial explains how to use the Access Switch function with syntax and examples. The Microsoft Access Switch function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE.

www.techonthenet.com

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]

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]

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

 

[Pat Hartman]

You're welcome