如何计算Access中字段的新值(How to calculate new value for field in Access)

如何计算Access中字段的新值(How to calculate new value for field in Access)

我对我创建的数据库有一些问题。

我想计算总价（三明治数量乘以三明治价格）。 之前我有过工作，但是我必须从最初的OrderDetailsT表中删除Sandwich Price。我现在遇到了这个计算的问题，因为我无法在OrderDetailsT表中进行计算（三明治价格不是那里）。

如果总价格超过50美元，我如何将折扣应用于总价？ 折扣应用于总价格字段后，我还想将其存储在NewPriceAfterDiscount字段中。

这是一张详细说明我情况的图片：

I've got a few problems with a database I have created.

I want to calculate a Total Price (Sandwich Quantity multiplied by Sandwich Price). I had it working before, but I had to delete Sandwich Price from the OrderDetailsT table of which it was originally in. I'm now having issues with this calculation, as I cannot make a calculation in the OrderDetailsT table (Sandwich Price isn't there).

How can I apply the Discount to the Total Price if the Total Price is more than $50 for instance? After the Discount has been applied to the Total Price field, I would also like to store it in the NewPriceAfterDiscount field.

Here is an image detailing my situation:

最满意答案

你有多个问题：但首先是。 如图所示，为什么OrderDetails和三明治之间有左联接？ 在订单计算中，您不需要订购三明治。

总计价格计算：向查询网格添加新列（假设折扣是存储的百分比，其数字介于0和1之间）：

[SandwichT].[SandwichPrice] * [OrderDetailT].[SandwichQuantity] * [OrderDetailT].[Discount]

存储总价：您可以使用上面的公式，但使用更新查询。 如果您打算在表单或报告中显示价格：

你可以动态进行计算（并不存储总数 价钱） 或者您应该在一个查询中更新总价格，然后再构建另一个查询 查询作为表单/报告的数据源。 另一个可能性（我的推荐）是将总数存储在输入表格中

You have multiple questions in one: But, first of all. As the image shows, why do you have a left join between OrderDetails an Sandwich? In a order calculation you don't need not ordered sandwiches.

To total price calculation: Add a new column to the query grid (assuming discount is a percentaje stored has a number between 0 and 1):

[SandwichT].[SandwichPrice] * [OrderDetailT].[SandwichQuantity] * [OrderDetailT].[Discount]

To store total price: you can use the above formula, but using a update query. If you plan to show the prices in a form or in a report:

you can do de calculations on the fly (and don't store the total price) or you should update the total price un one query and then build another query as datasource of the form/report. another posibility (my recomendation) is to store the total in the input form