Да си мислим, че имаме релационна база данни - стоките имат категории, стоките имат продажби с количество, дата и цена.
Количествата са различни за всяка продажбаа, но цената? Тя е относително постянна, но понекагоа все пак се променя.
Няма как да я зададем в таблицата със стоките, защото, когато трябва да я променим, това би променило старите ни продажби.
Ше я оставим в несвързана таблица и ще я викаме, когато нанясме продажбата. Но ще си оставим и възможност да я променим, ако се доголорим нещо друго с клиента.
Hi! My name is Evgenia Hristova and in this video, I will show how to use DLookUP function to hint value of table field for price.
This is s simple database for sales. The database has a table category - every category has one or more products.
The table for products has a field for category too and the relation type is One to Many.
The category table is related to table products. I can select from category dropdown the value for every product.
The table for sales has field for products.
One product has many sales. In the table for sales, there is a column for quantity and column for price.
I am going to input a new sale.
The quantity of products is different for every sale id but the price is constant for a long time.
It is a bad idea to set the price in the product table. The single price of the product must be changed in one moment and in this case, all sales will be changed because the tables have relationships between.
I want to set the product price in the unrelated table.
The product ID is a primary key with used Lookup wizard for the name of the product.
I am setting the price for the new product.
I am goiI want to use this table to get the current price and easy to set this price in the current sale.ng to show a simple solution to this task.
The first step is creating a form for the sales table.
Here is how the form works.
I am going to show this solution step by step. I am switching to design view.
I am going to add form control that has the price for selected product id. The price must have the value from unrelated to the table for sales another table - price list.
I am going to use an Aggregate function Dlookup. The first argument is the necessary field - price. the second argument is the domain, which means the name of the table - price list. the third argument is logical condition - for the current product id in the form that is equal to the product id from the table price list.
Now I have the price of the current product id.
I do not want to retype this price. I am going to use Code Builder to set the value after clicking on the form field.
=DLookUp("[price]";"[pricelist]";"[productID]=" & [Form]![productID])