循环遍历计算字段的记录集 [英] looping through recordsets for calculated fields

问题描述

我需要帮助来解决这个问题


问题定义：


我有一个表''tblItemOrdQty''，带有项目名称及其订单数量（Lot1，Lot2，Lot3 ....等）。每件商品都以不同的批量订购。有些是单批订购的..一些有两个批次..一些有三个批次等。

我的目标是有一个新列（一个计算字段），每个列的最新订单数量值项目。


例如。


物品编号|商品名称| Lot1 | Lot2 | Lot3 |最新数量（calc.field）


1 | Item1 | 10 | 11 | 14 | 14

2 | Item2 | 5 | 4 | null | 4

3 | Item3 | 20 | null | null | 20


我已经使用商品ID，商品名称，第1批，第2批，第3批进行了''ItemOrdQuery''查询。我需要知道如何为我期望的目标构建最新数量计算字段的表达式。


使用记录集使用SQL或VBA程序有什么办法吗？


等待你的回复。


谢谢你的时间。

Hi ,

I need help to solve this problem

Problem definition:

I have a table ''tblItemOrdQty'' with item name and its order quantities (Lot1,Lot2,Lot3.... etc). Each items are ordered in different lots. Some are ordered as single lot.. some in two lots.. some in three lots etc.
My objective is to have a new column ( a calculated field) with the value of latest order quantity for each item.

Eg.

Item ID | Item Name | Lot1 | Lot2 | Lot3| Latest Qty (calc.field)

1 | Item1 | 10 | 11 | 14 |14
2 | Item2 | 5 | 4 | null | 4
3 | Item3 |20 | null | null |20


I have made a query ''ItemOrdQuery''with Item ID, Item name, Lot 1 , Lot 2, Lot 3. I need to know how to build expression for the Latest Qty calculated field for my desired objective.

Is there any way it can be done using SQL or VBA program using recordsets?

Waiting for your reply.

Thank you for your time.

推荐答案

首先，我必须问你，你的数据库是否正确规范化。 br />
从您给我的示例数据中，我似乎猜测并非如此。数据库规范化应始终是设计数据库的第一步。

First off, I have to ask you, has your database been properly normalized.
From the example data you have given me, I would seem to guess that it is not the case. Database normalization shall always be the first step in designing the a database.

Enigma，


对于Lot1，Lot2和Lot3字段，物品名称最终订购了所有三个批次？如果不是，那么，为了回应上面的笑脸，你应该看看你的数据库正常化。


然后你会有一个这样的表：

Enigma,

For the fields Lot1, Lot2 and Lot3, will each Item Name eventually have all three lots ordered? If not, then, to echo Smiley above, you should probably look at normalizing your db.

Then you would have a table like this:

展开 | 选择 | Wrap | 行号

@Smiley编码器


我从另一个团队（采购部门）收到这种格式的表格数据量很大，约为450-500项，大约有10个批号（Lot 1，Lot2，Lot3 .. .. Lot10）。我很难将数据拆分成不同的表。我只是导入到我的数据库中并想要这样使用它。这就是我试图找到另一种方法来完成这项工作的原因。

我想知道最新的订单数量如何在计算的文件中显示。

@Smiley Coder

I recieve this table in this format from another team( Procurement department) and the data is huge around 450-500 items and around 10 lot numbers ( Lot 1 , Lot2 , Lot3.. .. Lot10). it is difficult for me to split the data into different tables.I just import into my database and want to use it as such. That is the reason i am trying to find a alternate method to get this done.

I want to know how can the latest order Qty be displayed in the Calculated filed.

这篇关于循环遍历计算字段的记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！