dlookup unitprice based on customer and product

[mseo]

hi,
I have a form/subform for adding orders, the problem that the prices are different from one customer to another, what I need to do is: dlookup the unitprice when I select a product and a customer from the comboboxes

thank you very much
Any help would be appreciated

[liimra]

One Question: If you have different prices for products and customers, wouldn't that make it very much complicated as you might have 100 different customers? wouldn't it be easier to set prices based on Customer Groups?

Anyways, Suppose you have three customers CuA, CuB & CuC and two products ProductA & ProductB. Furthermore, CuA & CuB have 30% discount while CuC doesn't; in this case you can populate the selling price field after updating the second combo (supposedly customer). To do this, add something like this to the "Afterupdate Event" of the second combo box:

 Price = IIf([Customer]="CuA" Or [Customer]="CuB",DLookUp("Price","TableName","[Product] = '" & [Product] & "'")*0.7,DLookUp("Price","TableName","[Product] = '" & [Product] & "'"))

and so on...

Regards,
Ali

[mseo]

thank you very much Ali
I really appreciate your reply
you analysed the problem correctly
but, because we have numerous customers and products and thus we will have more than one price for the same product depending on the number of customers, so I can't use iif function to implement your approach
we need more dynamic solution for this problem

thanks and over again
MSEO

[liimra]

You are most welcome. I don't know what you mean by "number of customers" because if you are choosing the customer from a combobox, how would there be a number!

Anyways, it is important to state here that I have created very complicated databases and I have never had any idea there is any other approach to different scenarios but the Nested IIF() & Switch() (both do the job); and what you want is to have different prices for different combination (scenarios).

Another approach is to save the prices in tables when creating the product. Again, this requires grouping customers because otherwise it is not applicable. In this approach, you create "GroupID" field in the customers table; and a number of price fields, to state different pricing for different groups (example: BasePrice, PriceA, PriceB, PriceC..and so on), in the product table. One good thing about this approach is that you don't need to change the Expression used in the first approach every time you create new group but instead, you create additional price field in the product table. Note: Saving prices increases database size.

Hope this helps,

Regards,
Ali

[mseo]

hi,
thank again
I mean by number of customer (I can use iif function statically) which means I need to handle this programatically (something like Dcount or Dlookup)
here's the metadata of tbl_products

productID;autonumber;PK
Productname;String
Customer_ID;Number;FK
UnitPrice;Number
 

I have before update trigger to prevent duplicating records (something like compound index on the productname and custome_ID)
so,I don't to group customers
I just need to get the price of a product for specific customer

thank you
MSEO

[liimra]

You are welcome again MSEO. According to my point of view, the two approaches I stated before serve the point (easy & applicable), but again this is my point of view which is different to yours. I hope some of the great contributors would offer different suggestions.


Best Regards,
Ali

[mseo]

Ok, Ali
I am trying to implement your approach
I need one more thing, please provide me with more explanation and details about how I can implement it, because I am not fully grasped it, and that would result in design changes
thank you again and over again
I really appreciate your help
MSEO

[liimra]

Suppose you have 1,000 customers, it is not applicable to assign different price for each of them because we are talking about a system here and please note that all Accounting Systems I worked with don't have more than 10 price levels (why would there be more!). So, for example you would charge 10% above cost price for 200 of them, 15% above cost price for another 200.. and so on. (Note that you might want to set base price and give discount to each group). Of course you have to decide on the criteria for the groups, for example GroupA would be customers with above $0.5Millions turnover/year, GroupB would be Cash Customers and so on... Or GroupA would be first class customers and so on. Groups here are the same as levels. Whenever you create new customer you choose his/her group (pricing level).


So we add text field (group field/ price level field) to Customers' table.


Suppose you create 5 different groups, then you have to create 5 fields (price field) in the product table. (If you create 6 groups, 6 fields are required and so on. I think you should try to shrink your categories the maximum you can).

Whenever you enter a new product, you will have to enter all 5 different prices (of course this can be easily done using VB, so for example when you enter cost all 5 fields will be populated as cost*1.1,cost*1.2 and so on).


Of course, there will be transaction table which will be handling customers transactions. In this table, the required fields are: TransactionID, CustomerID, ProductID, Date(maybe), Quantity, Price (Of course you can add what else you need).

Although I think you have full idea about relations and although it is not our concern here, but let me just state it hoping it will be helpful for others. You have to link Transaction table to both Customers & Products Tables through One-to-Many relationship:
CustomerID (from Customer Table)-to-CustomerID (from transaction table)
ProductID (from Product Table)-to-ProductID (from Product table).



Now you create the transaction form with two ComboBoxes, Customer and Product. When you choose the customer, you populate the CustomerID field in the Transaction Table with the CustomerID from the Customer Table; same applies to the ProductID. The price will be based on the Customer and the Product chosen. Of course, the quantity will be entered manually while you might set the default value Date() as for the date field.

To set values, you can use macros (AfterUpdate Event) --> SetValue would be the "Action", FieldName of the field to be Updated is the "Item" and ComboName.Column(0) would be is the "Expression" value.
Example: Suppose you have Combo called "Cbo" which has three columns in its record source and ID is the first field, and the Field to be updated with the ID called "Fld", then the Item would be [Fld] and the expression would be Cbo.Column(0). If the ID is the second column then the expression becomes Cbo.Column(1) and so on.
To update the price field based on the users selection, you use IIf() or Switch(). For example: Suppose you have the other product combo box named Cbo2 (this Combo should have Product different prices),the expression would be:

IIF(Cbo.Column(0)="GroupA",Cbo2.Column(2),IIF(Cbo.Column(0)="GroupB",Cbo2.Column(3)))....

In Case of using VB, then it becomes

Fld = IIF(Cbo.Column(0)="GroupA",Cbo2.Column(2),IIF(Cbo.Column(0)="GroupB",Cbo2.Column(3)))

All what I stated is reflected in the attached database. Note that one thing I added there is that the user has to select the customer first because the price will be set after selecting the product; So in case he/she selects the product first - msgbox will popup and the Product Combo is Set back to Null. Another thing is that I used the Switch() Function for setting the price but you can use the IIf() Function instead. Moreover, all ID fields are hidden on the form as the user has nothing to do with them and they are populated automatically. In addition, all prices will be populated based on the cost price when entering new product. Finally, please enable the content of the database when you open it (it was created as accdb and then saved as mdb).


Regards,
Ali

[liimra]

Glad it worked for you.

Regards,
Ali