How to compute 10% discount for select items?

[captgnvr]

D/Sirs

Thanks to this forum and thanks to mr boblarson, mr. hitechcoach, mr. bill, and mr namliam I have started from scratch and managed to link names table and stores table and got going on various forms and queries successfully.

Now I am onto the reports to make sales report and stock in hand reports and need guidance for:

I need to add 10 pct commission on items like type_A and no commission on items like type_B. type_A and type_B is as field item_type as 1 or 2.

How do I put this in the query which is calculating the field 'av_price' x 'quantity'.

Meaning if the item_type is 1 then av-price x quantity plus 10 percent else av-price x quantity.

Would like also to know which is the best way to do this?

Pls help.

brgds/captgnvr

 

[PNGBill]

Iif(Item_Type((AverPrice*Qty)*1.1)=A,(AverPrice*Qty)) or something similar should add 10% to Type A items and nothing to all others.

 

[captgnvr]

D/BILL

SaleAmt:
IIF(ITM_TYPE(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS]).[QTY])*1.1)=1,[TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS]).[QTY]

I think I have to redo the query bcos I seem to have av-price in two places. One time in 'tslop_list' and another one in table 't_sales_details'. Or can I work on the above formula as u hv guided. Of cource there are some errors coming in the above formula but I wanted to reply soon; otherwise you will think I am gone off to watch football

 

[PNGBill]

D/BILL

SaleAmt:
IIF(ITM_TYPE(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS]).[QTY])*1.1)=1,([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS]).[QTY]))

I think I have to redo the query bcos I seem to have av-price in two places. One time in 'tslop_list' and another one in table 't_sales_details'. Or can I work on the above formula as u hv guided. Of cource there are some errors coming in the above formula but I wanted to reply soon; otherwise you will think I am gone off to watch football

Just check the brackets.

New Zealand out of the comp

 

[captgnvr]

D/BILL
Apologies as I hv missed out to thank you for the immediate guidance. I am checking the brackets. I see in ur response colored brackets in red, green. U telling me something thro that colors??

Is it ok to have same field in two tables in a query?? I did that bcos I am basing the person and items sold data. If I get some free time during the day I will try and remove the field from one of the table and see how it affects my other reports and query.

 

[captgnvr]

D/BILL

The bracket error is not coming for the below one but getting

"the expression u entered has a function containing the wrong number of arguments."

SaleAmt:
IIF(ITM_TYPE([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1)=1,([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS]).[QTY]))

If u can tell me how this formula is ascertaining that it is item-type is 1. I read up abou this formula which to be used iif confirmed as yes do this or do that. In mine the ITM_TYPE = 1 is coming outside the calculations.

 

[PNGBill]

Add Red and remove green - forgot my port and starboard

You shouldn't have the same field twice in one query as not needed but you can Use the same field many times in one query.

SaleAmt is the field. Av_Price is part of the function, not a field in the query.

If you have Ave_Price in more then one table then study that issue and consider if one should be deleted. I would imagine it should.

You often repeat field names in different tables. eg CustomerID is in CustomerTable and SalesHeaderTable.
In Customertable it is the Primary key and Unique Identifier. In SalesHeaderTable, it is just a field that holds the CustomerID data to link the two tables and allow you to keep track of which customer has placed what order.

Average price is another issue as I would imagine this should be in the ProductsTable only.

When you make a sale, you store the SalePrice in the SalesdetailTable. This will be the AveragePrice at the time but referred to as SalePrice because it is no longer an Average price but the exact price for that sale.
In a sence, just playing with words but it can help later to be able to distinguish different data.

For your calculation, if the sale has been made, then you should use the SalePrice from the sales table, not the average price from the products table otherwise, as the products average price changes, your historical sales will also change which will be wrong.

 

[captgnvr]

D/BILL
Well explained n thnks. I got the mistake or the misconception. You have got my exact mistake in using the av-price. Got the concept to use it as sale-price. If a sale is made I have to use the av-price but how to convert that as sale-price. I will redo it tomoro as I am going for stations as river barge coming for cargo. But will come back and see for the guru's tips/guidance.

 

[PNGBill]

Hardly a guru. You will soon be beyond my level

Your form to add a new sale should allow the SalesDetail table to be updated by data from the Product table.
You enter the ProductID and Qty but Description and Price are automatically displayed.
Your new record in the SalesDetail table will include SalesHeaderID, ProductID, QtySold and SalePrice. description can also be saved as should this ever change then your sales records show the correct description at the time of the sale. Also, you should include Commission percentage as again, this can change over the years.

 

[captgnvr]

D/BILL
Good day.

SaleAmt: IIf([ITM_TYPE]="1",[AV_PRICE]*[QTY]*1.1,[AV_PRICE]*[QTY]). I studied last night about iif and also ur suggestions. As I understood the formula I worked it out like above but still getting error. I am still at it and will revert after some more trials. I hv tried the 1 without quotes also.
FYI I hv removed the extra av-price.

If u can explain how ur formula below works which is different from the one I made after reading iif. Saw u online and so quickly posted before I could work on it some more
IIF(ITM_TYPE(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1)=1,([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY]))

 

[PNGBill]

D/BILL
Good day.

SaleAmt: IIf([ITM_TYPE]="1",[AV_PRICE]*[QTY]*1.1,[AV_PRICE]*[QTY]). I studied last night about iif and also ur suggestions. As I understood the formula I worked it out like above but still getting error. I am still at it and will revert after some more trials. I hv tried the 1 without quotes also.
FYI I hv removed the extra av-price.

If u can explain how ur formula below works which is different from the one I made after reading iif. Saw u online and so quickly posted before I could work on it some more
IIF(ITM_TYPE(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1)=1,([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY]))

Try it this way:

IIF([ITM_TYPE]="1",(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1),([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY]))

 

[PNGBill]

If you still have problems confirm that all the fields are available to the query.

Also, do the calculation as:

SaleAmt: ([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1

and confirm this works.

If it does and ITM_TYPE can also be added to the query then all the variables are there. We just need to get the IIF working.

If you can't get [ITM_TYPE] in the query - just to test - and or the straight forward calculation above does not work then we have a different problem to resolve first.

 

[PNGBill]

I just tested this and it works. Sorry my first post was *%#@!

SELECT TblOrderDetails.ProductQty, TblOrderDetails.SalePrice, TblOrderDetails.SalesCommission, IIf([SalesCommission]=1,([ProductQTY]*[SalePrice])*1.1,[ProductQty]*[SalePrice]) AS InvTotal
FROM TblOrderDetails;

This is the important bit:

IIf([SalesCommission]=1,([ProductQTY]*[SalePrice])*1.1,[ProductQty]*[SalePrice])

 

[captgnvr]

D/BILL
BINGO!!!! Thanks to ur record time responses, I was able to figure it out and now the thread can be considered closed. I tried both and both worked.

1. SaleAmt: IIf([ITM_TYPE]=1,(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1),([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY]))

Note: in the equation =1, the 1 was entered without quotes.

2. SaleAmt: IIf([ITM_TYPE]=1,([QTY]*[AV_PRICE])*1.1,([QTY]*[AV_PRICE]))

I will now move on to get report based on 10pct commission and another report without commission etc.

Now this thread can be considered closed with lots of thanks for your quick guidance.

 

[namliam]

Probably over doing this, but I do want to point out that +10% is different from -10%

I.e. 100 + 10% = 110
However 110 - 10% = 99

Since your thread title is 10% discount, this suggests the latter, but your doing the former.

This could simply be a perceptional error or a logical error... either being non-sense (like my usual) or (unlikely) pretty important

 

[PNGBill]

Good point namliam. I read the post which asks for 10% commission to be added hence *1.1.

If in fact 10% is to deducted then *.9

If 10% only is to be calculated then *.1

If the Sales Tax of 10% is already charged and needs to be removed then ([fieldname1]*[fieldname2])/1.1

If Sales tax of 10% is already charged and needs to be identified then ([fieldname1]*[fieldname2])/11

Lets hope one of these options covers the expected result.

 

[captgnvr]

D/Namliam

Trust me. If this was not pointed out, I would not hv been aware. I read ur point and will keep in mind. From where I started, I hv gotten a long way and now close to completing the program and handing it over to the officers. Thanks for the assistance.

 

[namliam]

D/

hv ... ur ... hv

You seem to have a pretty structural problem with your keyboard, must be causing major headaches when trying to write code...

I suggest you get a replacement

 

[PNGBill]

D/BILL
BINGO!!!! Thanks to ur record time responses, I was able to figure it out and now the thread can be considered closed. I tried both and both worked.

1. SaleAmt: IIf([ITM_TYPE]=1,(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1),([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY]))

Note: in the equation =1, the 1 was entered without quotes.

2. SaleAmt: IIf([ITM_TYPE]=1,([QTY]*[AV_PRICE])*1.1,([QTY]*[AV_PRICE]))

I will now move on to get report based on 10pct commission and another report without commission etc.

Now this thread can be considered closed with lots of thanks for your quick guidance.

Just thought you may have problems with Nulls in your data.

This will occur where one or more of the variables do not exist ie, the record is blank.

Just watch for this as it won't effect your original calculation we did here but if you then want to say, Sum these in a report, if some of them don't exist then your Sum will not work.

One way around this is to put your function above inside an Nz function so then all records will get a result. Either a value you expect or Zero (0) if no purchase was made.

Try this as replacement for 1. above

SaleAmt:  Nz((IIf([ITM_TYPE]=1,(([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY])*1.1),([TSLOP_LIST].[AV_PRICE]*[T_SALES_DETAILS].[QTY]))),0)

If it works, you will see zero where no value appeared previously.

 

[captgnvr]

D/BILL
Very thoughtful of u. Bcos of the month end papers and cargo inventories, I will devote the time in a day or two and first thing that will be done immediately is to put that nz in the equation.