报告帮助:从同一领域读取记录 [英] Report Help: Reading Records From Same Field

查看:49
本文介绍了报告帮助:从同一领域读取记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,


让我解释一下我的问题。


我在每对记录之间加入了2个短划线以简化

一起看看

。实际上,这只是我查询的一长串结果。

合同--------数量--------组件-------- LENGTH ------- PCS

11111111 2 PartAA101 38 3

11111111 38 CABLE

-

11111111 8 PartAA102 42 8

11111111 42 CABLE

-

22222222 1 PartAA103 66 2

22222222 67电缆

-

22222222 2 PartBB101

22222222 59电缆

-

22222222 2 PartAA109 52 3

22222222 61电缆

我需要制作类似这样的报告:


合同------组件------长度------ PCS

11111111 PartAA101 38 3

11111111 PartAA102 42 8

22222222 PartAA103 66 2

22222222 PartBB101 59 2

非常简单,但这是困难的部分。看看第四套

记录:


合约--------数量--------组件--- ----- LENGTH ------- PCS

22222222 2 PartBB101

22222222 59 CABLE


请注意没有LENGTH或PCS。这是以PartBB开头的所有部分

的情况。在这种情况下,我需要使用59作为

LENGTH而将2作为PCS。如何告诉Access在下一行中查看字段

以获取信息? COMPONENT记录总是

跟随另一个名为CABLE的记录。 QTY值来自之前的

订单。它们可能类似于LENGTH和PCS,但它们并不相同。


这个问题过于简单了,但我需要Access来做

以下:


如果COMPONENT以PartBB开头然后使用相同的

行中的QTY值作为PCS的值和下一行中的QTY值,其中

CABLE为LENGTH - 否则,如果部分不是以

" PartBB"开头的。使用同一行中给出的LENGTH和PCS。


我的问题是将其转换为代码。我这样做的原因

这样我们的客户之一每周都会向我们发送一个电子表格,

有这样的信息给我们。这就是他们继续做事情的方式(改变表格,不幸的是,

不是一个选项),我试图通过导入自动化

电子表格,查询相应的数据,并打印出报告

所需的内容。这将节省大量时间。我编写代码的经验很少,所以示例会有所帮助。同样,这些

只是一个记录列表。有没有办法告诉Access看看

另一条线?感谢您阅读我的问题,任何帮助将是

赞赏。


谢谢,


Chad

解决方案

sh *********************** @ yahoo.com 写道:
< blockquote class =post_quotes>大家好,

让我解释一下我的问题。

我在每对记录之间加入了2个短划线,以便更轻松
一起看看会发生什么。实际上,这只是我查询的一长串结果。

合同--------数量--------组件--------长度------- PCS
11111111 2 PartAA101 38 3
11111111 38电缆
11111111 8 PartAA102 42 8
11111111 42电缆 -
22222222 1 PartAA103 66 2
22222222 67 CABLE
-
22222222 2 PartBB101
22222222 59 CABLE
-
22222222 2 PartAA109 52 3
22222222 61 CABLE

我需要做一个类似这样的报告:

合同------组件--- ---长度------ PCS
11111111 PartAA101 38 3
11111111 PartAA102 42 8
22222222 PartAA103 66 2
22222222 PartBB101 59 2
记录:

合同--------数量--------组件-------- LENGTH-- ----- PCS
22222222 2 PartBB101
22222222 59电缆

请注意,没有LENGTH或PCS。所有以PartBB开头的部分都是这种情况。在这种情况下,我需要使用59作为LENGTH而将2作为PCS。如何告诉Access查看下一行中的字段
以获取信息? COMPONENT记录总是跟随另一个名为CABLE的记录。 QTY值来自之前的订单。它们可能类似于LENGTH和PCS,但它们并不相同。

这是过于简单化的问题,但我需要Access来执行
以下内容:

如果COMPONENT以PartBB开头然后在与PCS的值相同的
行中使用QTY值,在下一行中使用QTY值将
CABLE称为LENGTH - 否则,如果零件不以
PartBB使用同一行中给出的LENGTH和PCS。

我的问题是将其转换为代码。我这样做的原因
这种方式是我们的客户之一每周向我们发送一份电子表格,其中包含以这种方式提供给我们的信息。这就是他们继续做事的方式(不幸的是,改变表格不是一种选择),我试图通过导入
电子表格来自动化,查询适当的数据,并打印出所需的报告。这将节省大量时间。我很少有编写代码的经验,所以示例会有所帮助。同样,这些只是一个记录列表。有没有办法告诉Access看看
另一条线?感谢您阅读我的问题,任何帮助都将受到赞赏。

谢谢,

Chad




嗯,你可以使用Dlookup(查询会慢一些,因为它需要计算这些东西)。


件:IIF(不是IsNull([Pcs]),[Pcs],[Qty])

LengthOf:IIF(Not IsNull([Length]),[Length],_

Dlookup (数量,表名称,合同=& [合约]& _

" And Component =''Cable''"))


您还要排除显示包含组件的记录

命名为Cable


如果您在报告中执行此操作,您可以创建一个

计算字段的字段。例如:有一个名为Pieces的字段。在控制

来源中你可以使用上面的IIF语句......或者你可以创建一个

函数并输入

= GetPieces([合同])

并写一个函数来查找/返回结果。


你好,


感谢您的帮助。你以前帮我摆脱了堵塞。


为了简化我的上一篇文章,我改变了一些变量名。这里

是我更改的内容


QTY = QTY_EXTENDED

桌子名称= Cable_Table

合同= C_CONTRACT

组件保持不变

CABLE真的是一部分#BBB1173


我不知道这是否有所作为,但我想给你所有

的信息:


C_CONTRACT-QTY_EXTENDED ----- COM * PONENT -------- LENGTH ------- PCS

11111111 2 PartAA101 38 3

11111111 38 BBB1173

11111111 8 PartAA102 42 8

11111111 42 BBB1173

22222222 1 PartAA103 66 2

22222222 67 BBB1173

22222222 2 PartBB101

22222222 59 BBB1173


PCS IIF声明效果很好,但我在LENGTH上得到#Error。

这是我试过的IIF声明(插入_用于换行符

目的 - 实际上,它是一长行):


= IIF(不是IsNull([LENGTH]),[LENGTH],_

Dlookup(" Q TY_EXTENDED"," Cable_Table"," C_CONTRACT =" &安培; [C_CONTRACT]& _

"和COMPONENT =''BBB1173''"))


我一直收到错误信息,我想知道我做了什么/>
错了。这些信息现在放在一个表而不是一个查询中 - 我尝试了两种方式来查看这是否会产生影响。另外,仅仅为了我的

信息,DLookup如何知道相应的

CABLE / BBB1173记录是什么?它是否在寻找记录immediatley

以下?孩子们将他们联系在一起的唯一事情是

C_CONTRACT,但它可能用于多个组件。我是新的

,所以我不确定这是怎么回事。任何额外的帮助将是赞赏。


谢谢,
>


sh ***********************@yahoo.com 写道:

= IIF(不是IsNull ([LENGTH]),[LENGTH],_
Dlookup(QTY_EXTENDED,Cable_Table,C_CONTRACT ="& [C_CONTRACT]& _
和COMPONENT = ''BBB1173''"))
Chad




为了节省沙拉一段时间,你应该尝试在''和'之前放一个空格。'' br />

"和COMPONENT ......


James A. Fortune


Hello Everyone,

Let me explain my problem.

I have included 2 dashes between each pair of records to make it easier
to see what goes
together. In reality, it is just a long list of results from my query.
Contract--------QTY--------COMPONENT--------LENGTH-------PCS
11111111 2 PartAA101 38 3
11111111 38 CABLE
--
11111111 8 PartAA102 42 8
11111111 42 CABLE
--
22222222 1 PartAA103 66 2
22222222 67 CABLE
--
22222222 2 PartBB101
22222222 59 CABLE
--
22222222 2 PartAA109 52 3
22222222 61 CABLE
I need to make a report that looks something like this:

Contract------Component------Length------PCS
11111111 PartAA101 38 3
11111111 PartAA102 42 8
22222222 PartAA103 66 2
22222222 PartBB101 59 2

Pretty simple, but here is the hard part. Look at the fourth set of
records:

Contract--------QTY--------COMPONENT--------LENGTH-------PCS
22222222 2 PartBB101
22222222 59 CABLE

Notice that there is no LENGTH or PCS. This is the case with all parts
that start with "PartBB". In this case, I need to use the 59 as the
LENGTH and the 2 as the PCS. How do I tell Access to look at a field
in the next line for the information? The COMPONENT record is always
followed another record called CABLE. The QTY values are from previous
orders. They may be similar to the LENGTH and PCS, but they are not
the same.

This is oversimplifying the problem, but I need Access to do the
following:

If COMPONENT begins with "PartBB" then use the QTY value in the same
line as the value for PCS and the QTY value in the next line that says
CABLE as the LENGTH--Otherwise, if the part does not start with
"PartBB" use the LENGTH and PCS that are given in the same line.

My problem is transfering this into code. The reason I am doing things
this way is one of our customers sends us a spreadsheet each week that
has the information give to us this way. That is the way they are
going to continue to do things (changing the sheet, unfortunately, is
not an option), and I am trying to automate things by importing the
spreadsheet, querying the appropriate data, and printing out a report
of what is needed. This will save a lot of time. I have little
experience writing code, so examples would be helpful. Again, these
are just a list of records. Is there a way to tell Access to look at
another line? Thanks for reading my problem, and any help would be
appreciated.

Thank You,

Chad

解决方案

sh***********************@yahoo.com wrote:

Hello Everyone,

Let me explain my problem.

I have included 2 dashes between each pair of records to make it easier
to see what goes
together. In reality, it is just a long list of results from my query.
Contract--------QTY--------COMPONENT--------LENGTH-------PCS
11111111 2 PartAA101 38 3
11111111 38 CABLE
--
11111111 8 PartAA102 42 8
11111111 42 CABLE
--
22222222 1 PartAA103 66 2
22222222 67 CABLE
--
22222222 2 PartBB101
22222222 59 CABLE
--
22222222 2 PartAA109 52 3
22222222 61 CABLE
I need to make a report that looks something like this:

Contract------Component------Length------PCS
11111111 PartAA101 38 3
11111111 PartAA102 42 8
22222222 PartAA103 66 2
22222222 PartBB101 59 2

Pretty simple, but here is the hard part. Look at the fourth set of
records:

Contract--------QTY--------COMPONENT--------LENGTH-------PCS
22222222 2 PartBB101
22222222 59 CABLE

Notice that there is no LENGTH or PCS. This is the case with all parts
that start with "PartBB". In this case, I need to use the 59 as the
LENGTH and the 2 as the PCS. How do I tell Access to look at a field
in the next line for the information? The COMPONENT record is always
followed another record called CABLE. The QTY values are from previous
orders. They may be similar to the LENGTH and PCS, but they are not
the same.

This is oversimplifying the problem, but I need Access to do the
following:

If COMPONENT begins with "PartBB" then use the QTY value in the same
line as the value for PCS and the QTY value in the next line that says
CABLE as the LENGTH--Otherwise, if the part does not start with
"PartBB" use the LENGTH and PCS that are given in the same line.

My problem is transfering this into code. The reason I am doing things
this way is one of our customers sends us a spreadsheet each week that
has the information give to us this way. That is the way they are
going to continue to do things (changing the sheet, unfortunately, is
not an option), and I am trying to automate things by importing the
spreadsheet, querying the appropriate data, and printing out a report
of what is needed. This will save a lot of time. I have little
experience writing code, so examples would be helpful. Again, these
are just a list of records. Is there a way to tell Access to look at
another line? Thanks for reading my problem, and any help would be
appreciated.

Thank You,

Chad



Well, you can use Dlookup (the query will be a bit slower because it
needs to calculate the stuff).

Pieces:IIF(Not IsNull([Pcs]),[Pcs],[Qty])
LengthOf:IIF(Not IsNull([Length]),[Length],_
Dlookup("Qty","TableName","Contract = " & [Contract] & _
"And Component = ''Cable''"))

You''d also want to exclude displaying records that have a component
named "Cable"

If you are doing this in a report, you can create a field that is a
calculated field. Ex: Have a field called Pieces. In the control
source you could use the IIF statements above...or you could create a
function and enter
=GetPieces([Contract])
and write a funtion to look up/return the result.


Hello,

Thanks for the help. You''ve helped me out of a jam before.

To simplify my last post, I changed some of the variable names. Here
is what I changed

QTY=QTY_EXTENDED
Name of table=Cable_Table
CONTRACT=C_CONTRACT
COMPONENT remains unchanged
"CABLE" is really a part # "BBB1173"

I don''t know if this makes a difference, but I wanted to give you all
the information:

C_CONTRACT-QTY_EXTENDED-----COM*PONENT--------LENGTH-------PCS
11111111 2 PartAA101 38 3
11111111 38 BBB1173
11111111 8 PartAA102 42 8
11111111 42 BBB1173
22222222 1 PartAA103 66 2
22222222 67 BBB1173
22222222 2 PartBB101
22222222 59 BBB1173

The PCS IIF statement works great, but I get #Error on the LENGTH.
Here is the IIF statement I tried (with _ inserted for line breaks
purposes-in reality, it is one long line):

=IIF(Not IsNull([LENGTH]),[LENGTH], _
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRACT = " & [C_CONTRACT] & _
"And COMPONENT = ''BBB1173''"))

I keep getting an error message and am wondering what I have done
wrong. This information is now in a table instead of a query--I tried
both ways to see if this would make a difference. Also, just for my
information, how does the DLookup know what the corresponding
CABLE/BBB1173 record is? Does it look for the record immediatley
following? The only thing that kid of ties them together is the
C_CONTRACT, but it might be used for more than one Component. I''m new
at this, so I am unsure how this works. Any additional help would be
appreciated.

Thank You,

Chad


sh***********************@yahoo.com wrote:

=IIF(Not IsNull([LENGTH]),[LENGTH], _
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRACT = " & [C_CONTRACT] & _
"And COMPONENT = ''BBB1173''")) Chad



To save Salad some time, you should try putting a space before ''And.''

" And COMPONENT..."

James A. Fortune


这篇关于报告帮助:从同一领域读取记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆