用新的扭曲计算？ [英] Counting Up With A New Twist?
问题描述
我感谢这个小组的帮助。我知道我已经在最后几周发布了很多内容，但是在我的工作中我被扔进了一个数据库项目
非常少的访问体验。没有其他员工知道
Access。我搜索了Google网上论坛，这有很多帮助，但
有些问题我找不到答案。在这个问题之后，我会尝试在团队中轻松取笑。
我还有一个问题，我一直试图解决，我希望你们能够
帮助我提出一些建议。我上周发布了这个问题，但是我已经让我的老板给了我另一个曲线球并且不得不修改
我想做什么。
我想为每条记录分配一个号码，该号码将成为运费
号码的一部分。我想要数字值计算，直到合同号
改变。然后，我希望这个号码回到1并再次开始计算
，直到下一次合同变更为止。
我得到了这个建议。 />
DCount（" *，Label_Info，[Contract] =''"& [Contract]&"''和
[Label_Info]！[ID]< ="& [Label_Info]！[ID]）+ 0
合同是合约编号，Label_Info是表格
合约号码被保留，adn ID是一个自动编号字段。
这很好用。我的新问题是我必须添加另一个名为
Packed_With的字段来覆盖其他框中打包的部分。 Packed_With
值将为C或W（包含或With）。我的大多数记录都不会包含C或W，但是如果我选择C，那么下一条记录将是W.我需要W记录才能获得与前一个相同的值。如果有超过1个部分，我可能还有下一个W值的
。所以，我希望所有的W'
与C具有相同的数值，我想开始计算我在W之后剩下的
。我知道我不是很好解释，所以这里是
另一个例子：
例如
合同1111111第1箱第2组数值：1
合同1111111第2箱第2组数值：2
合同1111111防滑1 of 1数值：3
合同1111111 C第1栏，共1页数值：4
合同1111111 W数值：4
合同1111111 W数值：4
合同1111111 W数值：4
合同1111111防滑1 of 1数值：5
合同1111111方框1的1数值：6
合同2222222方框1 of 2数值：1
合同2222222方框2 of 2数值：2
合同2222222防滑1 of 1价值：3
合同2222222捆绑1 of 2数值：4
合同2222222捆绑2 of 2数值：5
有没有修改DCount方法做我想要的方法？其他
建议也欢迎。
我真的很感激帮助。我正在努力学习。
Chad
ChadDiesel写道：我真的很感激帮助。我正在努力学习。
我们都不是。
这似乎有效对于我尝试过的几个案例：
qryNumberToAssign：
SELECT Label_Info.ID，Label_Info.Contract，Label_Info.Packed_With，
Label_Info.Box，（SELECT Count（A.ID）FROM Label_Info AS A WHERE
A.Contract = Label_Info.Contract And A.ID< = Label_Info.ID） - （SELECT
计数（Nz（A.Packed_With））FROM Label_Info AS A WHERE A.Contract =
Label_Info.Contract And A.ID< = Label_Info.ID And A.Packed_With =' 'W''）
AS NumberToAssign FROM Label_Info;
也许你可以把它转换成两个独立的DCount'。请注意，这个
方法要求第一个Contract项目中没有''W''
Packed_With所以请准备好让老板要求:-)。如果W的
是连续的，你真的想要尝试类似的东西：
SELECT Label_Info.ID，Label_Info.Contract，Label_Info。 Packed_With，
Label_Info.Box，（SELECT Count（B.ID）FROM Label_Info AS B WHERE
B.Contract = Label_Info.Contract And B.Packed_With =''W ''AND B.ID< =
Label_Info.ID AND Label_Info.ID> DMax（" ID"，" Label_Info"，" Contract =
''"& Label_Info.Contract&"''And Packed_With =''W''"））AS
WsPreviousToThisNonW来自Label_Info;
即，DMax函数可以在DCount函数的Criteria
字符串中使用，以帮助获得只有你的老板可以想象的功能。
James A. Fortune
有一件事可能改变你对整个设计的看法...
使用记录顺序的整体想法表格描述
集装箱关系是非常糟糕的关系设计。是的，您可能希望
-present-这样的数据，但不是那样描述的。
相反，我建议的是你有一个运输项目表，
和一个运输子项目表。每个子项目都有一个项目的外键
关系。层次结构如下：
发货：
货号：
子项目
现在，只有项目记录有数字，而不是子项目记录，所以你不再需要麻烦如何增加项目的编号方案
个案，而不是子项目案例。
2005年4月6日星期三02:03:37 GMT，ChadDiesel
< sh ******** ***************@yahoo.com>写道：
我感谢这个小组的帮助。我知道我已经在最近几周发布了很多内容，但是在我的工作中我被扔进了一个数据库项目，很少有Access体验。没有其他员工了解
访问权限。我搜索了Google网上论坛，这提供了很多帮助，但是有些问题我找不到答案。在这个问题之后，我会试着让小组轻松一点。
我还有一个问题，我一直试图解决，我希望你们能够
帮我解决一些建议。我上周发布了这个问题，但是我已经让我的老板给我扔了另一个曲线球并且不得不修改我想做的事情。
我想分配每个记录中的一个数字，它将成为发货号码的一部分。我希望数字值能够计算，直到合同号
发生变化。然后，我希望这个号码回到1并再次开始计算
直到下一次合同变更。
我得到了这个建议。
DCount（ " *"，"" Label_Info"，" [Contract] =''"& [Contract]&"''和
[Label_Info]！[ID]< ="& ; [Label_Info]！[ID]）+ 0
合同是合同号，Label_Info是
合同号码的表格，adn ID是自动编号字段。
这很有效。我的新问题是我必须添加另一个名为
Packed_With的字段来覆盖其他框中包含的部分。 Packed_With
值将为C或W（包含或With）。我的大多数记录都不会包含C或W，但是如果我选择C，那么下一条记录将是W.我希望W记录具有与之前相同的值。如果有超过1个部分，我可能还有下一个W值。所以，我希望所有的W'具有与C相同的数值，并且我想开始计算我在W之后离开的地方。我知道我没有很好地解释自己，所以这里是
另一个例子：
例如
合同1111111第1栏，共2个数字价值：1
合同1111111第2箱第2号价值：2
合同1111111防滑1 of 1数值：3
合同1111111 C盒子1的1数值：4
合同1111111 W数值：4
合同1111111 W数量值：4
合同1111111 W数量值：4
合同1111111防滑1 of 1数值：5
合同1111111第1箱第1个数值：6
合同2222222第1栏，共2页数值：1
Co ntract 2222222第2栏第2页数值：2
合同2222222防滑1 of 1数值：3
合同2222222捆绑1 of 2数值：4
合同2222222捆绑2 of 2数值：5
有没有办法修改DCount方法来做我想要的？其他
建议也欢迎。
我真的很感激帮助。我正在努力学习。
Chad
Steve Jorgensen写道：有一件事可能改变你对整个设计的看法......
使用表中记录顺序来描述集装箱关系的整个想法真的很糟糕关系设计。是的，你b $ b可能想要 - 以这种方式呈现数据，但不是那样描述的。
相反，我建议的是你有一个运费表
项目和运输子项目表。每个子项目与项目具有外键关系。层次结构如下：
发货：
项目：
子项目
现在，只有项目记录有数字，而不是子项目记录，所以你没有
更长时间需要麻烦如何增加
" item"的编号方案。情况，而不是子项目案例。
这是一个好主意。这个小小的SQL拼图很有趣，但它不是必需的。$ / b
James A. Fortune
一些与单词相关的网站：
http://www.mw.com
http://www.1911encyclopedia.org/index.htm
http://www.askoxford.com/dictionarie ... t_oed /？view = uk
http：//字典。 cambridge.org/
http：/ /www.tiscali.co.uk/reference/d...ifficultwords/
http://www.infoplease.com/dictionary.html
http://www.etymonline.com/index.php?...earchmode=none
http://www.bartleby.com/61/
http://www.wordsmyth.net/
I appreciate the help on this group. I know I''ve posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other employee knows anything about
Access. I''ve searched Google Groups, and that has been a lot of help, but
there are some questions that I just can''t find the answer to. I''ll try to
take it easy on the group after this question.
I have one more problem I''ve been trying to solve, and I hope you guys can
help me out with some suggestions. I posted this question last week, but I
have since had my boss throw me another curve ball and have had to modify
what I wanted to do.
I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to go back to 1 and start counting up again
until the next contract change.
I was given this suggestion.
DCount("*","Label_Info","[Contract]=''" & [Contract] & "'' And
[Label_Info]![ID] <=" & [Label_Info]![ID])+0
With Contract being the contract number, Label_Info being the table the
contract number is held, adn ID being an autonumber field.
This worked great. My new problem is I have to add another field called
Packed_With to cover parts that are packed in other boxes. The Packed_With
value is going to be C or W (Contains or With). Most of my records will not
contain either C or W, but if I pick C, then the next record will be W. I
want the W record to have the same value as the previous. I might have yet
another W value next if there is more than 1 part. So, I want all the W''s
to have the same number value as the C, and I want to start counting where I
left off after the W. I know I am not explaining myself well, so here is
another example:
For example
Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number Value: 3
Contract 1111111 C Box 1 of 1 Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 Skid 1 of 1 Number Value: 5
Contract 1111111 Box 1 of 1 Number Value: 6
Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5
Is there a way to modify the DCount method to do what I want? Other
suggestions are welcome also.
I would really appreciate the help. I''m trying to learn as I go.
Chad
ChadDiesel wrote:I would really appreciate the help. I''m trying to learn as I go.
Aren''t we all.
This seemed to work for several of the cases I tried:
qryNumberToAssign:
SELECT Label_Info.ID, Label_Info.Contract, Label_Info.Packed_With,
Label_Info.Box, (SELECT Count(A.ID) FROM Label_Info AS A WHERE
A.Contract = Label_Info.Contract And A.ID <= Label_Info.ID) - (SELECT
Count(Nz(A.Packed_With)) FROM Label_Info AS A WHERE A.Contract =
Label_Info.Contract And A.ID <= Label_Info.ID And A.Packed_With = ''W'')
AS NumberToAssign FROM Label_Info;
Perhaps you can convert it into two separate DCount''s. Note that this
method requires that the first Contract item not have a ''W'' in
Packed_With so be ready for your boss to ask for that :-). If the W''s
are contiguous and you really want to get fancy try something like:
SELECT Label_Info.ID, Label_Info.Contract, Label_Info.Packed_With,
Label_Info.Box, (SELECT Count(B.ID) FROM Label_Info AS B WHERE
B.Contract = Label_Info.Contract And B.Packed_With = ''W'' AND B.ID <=
Label_Info.ID AND Label_Info.ID > DMax("ID", "Label_Info", "Contract =
''" & Label_Info.Contract & "'' And Packed_With = ''W''")) AS
WsPreviousToThisNonW FROM Label_Info;
I.e., the DMax function can be used inside a DCount function''s Criteria
string to help get functionality that only your boss can imagine.
James A. Fortune
One thing that might change your thinking about the whole design...
The whole idea of using the order of records in the table to describe
containership relations is really bad relational design. Yes, you might want
to -present- the data that way, but not describe it that way.
Instead, what I would recommend is that you have a table of shipping items,
and a table of shipping sub-items. Each sub-item has a foreign key
relationship to an item. The hierarchy looks like this:
Shipment:
Item:
Sub-Item
Now, only item records have numbers, not sub-item records, so you no longer
need to hassle with how to increment the numbering scheme for the "item"
cases, and not for the "sub-item" cases.
On Wed, 06 Apr 2005 02:03:37 GMT, "ChadDiesel"
<sh***********************@yahoo.com> wrote:
I appreciate the help on this group. I know I''ve posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other employee knows anything about
Access. I''ve searched Google Groups, and that has been a lot of help, but
there are some questions that I just can''t find the answer to. I''ll try to
take it easy on the group after this question.
I have one more problem I''ve been trying to solve, and I hope you guys can
help me out with some suggestions. I posted this question last week, but I
have since had my boss throw me another curve ball and have had to modify
what I wanted to do.
I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to go back to 1 and start counting up again
until the next contract change.
I was given this suggestion.
DCount("*","Label_Info","[Contract]=''" & [Contract] & "'' And
[Label_Info]![ID] <=" & [Label_Info]![ID])+0
With Contract being the contract number, Label_Info being the table the
contract number is held, adn ID being an autonumber field.
This worked great. My new problem is I have to add another field called
Packed_With to cover parts that are packed in other boxes. The Packed_With
value is going to be C or W (Contains or With). Most of my records will not
contain either C or W, but if I pick C, then the next record will be W. I
want the W record to have the same value as the previous. I might have yet
another W value next if there is more than 1 part. So, I want all the W''s
to have the same number value as the C, and I want to start counting where I
left off after the W. I know I am not explaining myself well, so here is
another example:
For example
Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number Value: 3
Contract 1111111 C Box 1 of 1 Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 Skid 1 of 1 Number Value: 5
Contract 1111111 Box 1 of 1 Number Value: 6
Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5
Is there a way to modify the DCount method to do what I want? Other
suggestions are welcome also.
I would really appreciate the help. I''m trying to learn as I go.
Chad
Steve Jorgensen wrote:One thing that might change your thinking about the whole design...
The whole idea of using the order of records in the table to describe
containership relations is really bad relational design. Yes, you might want to -present- the data that way, but not describe it that way.
Instead, what I would recommend is that you have a table of shipping items, and a table of shipping sub-items. Each sub-item has a foreign key
relationship to an item. The hierarchy looks like this:
Shipment:
Item:
Sub-Item
Now, only item records have numbers, not sub-item records, so you no longer need to hassle with how to increment the numbering scheme for the "item" cases, and not for the "sub-item" cases.
That''s a great idea. This little SQL puzzle was fun but it wasn''t
necessary.
James A. Fortune
Some sites related to words:
http://www.m-w.com
http://www.1911encyclopedia.org/index.htm
http://www.askoxford.com/dictionarie...t_oed/?view=uk
http://dictionary.cambridge.org/
http://www.tiscali.co.uk/reference/d...ifficultwords/
http://www.infoplease.com/dictionary.html
http://www.etymonline.com/index.php?...earchmode=none
http://www.bartleby.com/61/
http://www.wordsmyth.net/
这篇关于用新的扭曲计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
