连接成一个字段 [英] Concatenate into one field
问题描述
您好，
我希望有人可以为我的问题提供一些指导。
我在MS Access中有一个查询由2个表格组成，由
SEQUENCE_ID连接。通过连接这两个表，我能够生成一个查询
，它给了我两个字段，Part_Number和Product_Type。您可以在下方查看一些示例数据：
Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D
理想情况下，查询有一个额外的字段
会很好地连接Product_Type'：
10MC35231 XYZ1A，XYZ1B
23XB94142 XYZ1A，XYZ1B，XYZ1C
42PA76251 XYZ1D
我发现了一些连接函数，但它似乎是它的任何一个/>
不喜欢我在查询中加入两个表，或者
数据只在一个表中。我真的很感激，如果有人可以
帮我指点一下。
提前谢谢，
Aaron
Aaron Hoffman写道：
您好，
我希望有人可以为我的问题提供一些指导。
我在MS Access中有一个查询，其中包含2个表格，由
$ b $连接b SEQUENCE_ID。通过连接这两个表，我能够生成一个查询
，它给了我两个字段，Part_Number和Product_Type。您可以在下方查看一些示例数据：
Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D
理想情况下，查询有一个额外的字段
会很好地连接Product_Type'：
10MC35231 XYZ1A，XYZ1B
23XB94142 XYZ1A，XYZ1B，XYZ1C
42PA76251 XYZ1D
我发现了一些连接函数，但它似乎是它的任何一个/>
不喜欢我在查询中加入两个表，或者
数据只在一个表中。我真的很感激，如果有人可以
帮我指点一下。
提前致谢，
Aaron
有什么方法可以使用数据透视表/交叉表查询吗？
9月4日，12：48 * pm，Salad< o ... @ vinegar.comwrote：
Aaron Hoffman写道：
你好，
我希望有人可以为我的问题提供一些指导。
我有一个MS Access中的查询，包括由
SEQUENCE_ID连接的2个表。通过连接这两个表，我能够生成一个查询
，它给了我两个字段，Part_Number和Product_Type。您可以在下面查看一些示例数据：
Part_Number * * * * Product_Type
10MC35231 * * * * XYZ1A
10MC35231 * * * * XYZ1B
23XB94142 * * * * * XYZ1A
23XB94142 * * * * * XYZ1B
23XB94142 * * * * * XYZ1C
42PA76251 * * * * * XYZ1D
理想情况下，查询有一个额外的字段
会很好地连接Product_Type'：
10MC35231 * * * * XYZ1A，XYZ1B
23XB94142 * * * * * XYZ1A，XYZ1B，XYZ1C
42PA76251 * * * * * XYZ1D
我发现了一些连接函数，但它似乎不是因为我在查询中加入了两个表，或者
数据只在一个表中。我真的很感激，如果有人能够帮助我给我一个方向。
提前致谢，
Aaron
是有什么方法可以使用数据透视表/交叉表查询？ - 隐藏引用文本 -
- 显示引用文本 -
感谢您的答复。其实我有同样的想法。我创建了一个
交叉表查询，它给出了以下输出，每个适当的Product_Type计数为
。
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 1 1
23XB94142 1 1 1
42PA76251 1
我真的是什么需要的是计数值为Product_Type。
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 XYZ1A XYZ1B
23XB94142 XYZ1A XYZ1B XYZ1C XYZ1D
42PA76251 XYZ1D
如果有人能弄清楚如何用字段替换计数
name（Product_Type ）然后每个字段可以连接
给出结果。我真的想从Access
中执行此操作，而不必在Excel中手动操作。我不确定这是否是最好的方法，但我会很感激任何建议。
谢谢，
亚伦
9月4日，11：22 * am，Aaron Hoffman< bikefas ... @ gmail.comwrote：
您好，
我希望有人可以为我的问题提供一些指导。
我在MS Access中有一个查询它包含2个表格，由
SEQUENCE_ID连接。通过连接这两个表，我能够生成一个查询
，它给了我两个字段，Part_Number和Product_Type。您可以在下方查看一些示例数据：
Part_Number * * * * Product_Type
10MC35231 * * * * XYZ1A
10MC35231 * * * * XYZ1B
23XB94142 * * * * * XYZ1A
23XB94142 * * * * * XYZ1B
23XB94142 * * * * * XYZ1C
42PA76251 * * * * * XYZ1D
理想情况下，查询有一个额外的字段
将连接Product_Type'：
10MC35231 * * * * XYZ1A，XYZ1B
23XB94142 * * * * * XYZ1A ，XYZ1B，XYZ1C
42PA76251 * * * * * XYZ1D
我发现了几个连接函数，但它似乎要么它
不喜欢我在查询中加入两个表，或者
数据只在一个表中。我真的很感激，如果有人可以
帮我指点一下。
提前致谢，
Aaron
这里我将使用VBA函数检索所有产品类型
给定的零件号并将它们连接起来用逗号分隔
分隔字符串。例如，您的初始查询看起来像
类似于：
选择不同的part_number，sequence_id，
prodtypes_get（[sequence_id] ）来自tblParts
你的VBA函数看起来像：
函数prodtypes_get（varSeqID as variant）作为变体
dim rst作为DAO.recordset
dim varRtn作为变体
set rst = dbengine（0）（0）.openrecordset（ 选择Product_type从
tblProductTypes，其中Sequence_ID ="& varSeqID）
直到rst.eof
varRtn = varRtn& rst！Product_type& "，"
loop
if isnull（varRtn）then
prodtypes_get = varRtn
else
prodtypes_get = left（varRtn，len（varRtn）-1）
结束如果
结束函数
你可能想要通过一些错误处理来修饰它等等。
但这是基本的想法。
布鲁斯
Hello,
I''m hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:
Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D
Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type''s:
10MC35231 XYZ1A, XYZ1B
23XB94142 XYZ1A, XYZ1B, XYZ1C
42PA76251 XYZ1D
I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.
Thanks in advance,
Aaron
Aaron Hoffman wrote:
Hello,
I''m hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:
Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D
Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type''s:
10MC35231 XYZ1A, XYZ1B
23XB94142 XYZ1A, XYZ1B, XYZ1C
42PA76251 XYZ1D
I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.
Thanks in advance,
AaronIs there any way you could use a PivotTable/Crosstab query?
On Sep 4, 12:48*pm, Salad <o...@vinegar.comwrote:Aaron Hoffman wrote:Hello,
I''m hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:
Part_Number * * * *Product_Type
10MC35231 * * * * XYZ1A
10MC35231 * * * * XYZ1B
23XB94142 * * * * *XYZ1A
23XB94142 * * * * *XYZ1B
23XB94142 * * * * *XYZ1C
42PA76251 * * * * *XYZ1D
Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type''s:
10MC35231 * * * * XYZ1A, XYZ1B
23XB94142 * * * * *XYZ1A, XYZ1B, XYZ1C
42PA76251 * * * * *XYZ1D
I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.
Thanks in advance,
Aaron
Is there any way you could use a PivotTable/Crosstab query?- Hide quoted text -
- Show quoted text -Thanks for your response. Actually I had the same idea. I created a
Crosstab query which gives me the following output with a count for
each appropriate Product_Type.
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 1 1
23XB94142 1 1 1
42PA76251 1
What I really need is the value of the count to be the Product_Type.
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 XYZ1A XYZ1B
23XB94142 XYZ1A XYZ1B XYZ1C XYZ1D
42PA76251 XYZ1D
If someone can figure out how I can replace the count with the field
name (Product_Type) then each of the fields could be concatenated
giving the result. I would really like to do this from within Access
and not have to manipulate manually in Excel. I''m not sure if this is
the best approach but I would appreciate any advice.
Thanks,
Aaron
On Sep 4, 11:22*am, Aaron Hoffman <bikefas...@gmail.comwrote:Hello,
I''m hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:
Part_Number * * * *Product_Type
10MC35231 * * * * XYZ1A
10MC35231 * * * * XYZ1B
23XB94142 * * * * *XYZ1A
23XB94142 * * * * *XYZ1B
23XB94142 * * * * *XYZ1C
42PA76251 * * * * *XYZ1D
Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type''s:
10MC35231 * * * * XYZ1A, XYZ1B
23XB94142 * * * * *XYZ1A, XYZ1B, XYZ1C
42PA76251 * * * * *XYZ1D
I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.
Thanks in advance,
AaronHere''s where I''d use a VBA function to retrieve all the product types
for a given part number and concatenate them all into a comma
delimited string. For example, your initial query would look
something like:
select distinct part_number, sequence_id,
prodtypes_get([sequence_id]) from tblParts
and your VBA function would look something like:
function prodtypes_get(varSeqID as variant) as variant
dim rst as DAO.recordset
dim varRtn as variant
set rst = dbengine(0)(0).openrecordset("select Product_type from
tblProductTypes where Sequence_ID = " & varSeqID)
do until rst.eof
varRtn = varRtn & rst!Product_type & ","
loop
if isnull(varRtn) then
prodtypes_get = varRtn
else
prodtypes_get = left(varRtn,len(varRtn)-1)
end if
end function
You''d probably want to spruce that up with some error handling, etc.
but this is the basic idea.
Bruce
这篇关于连接成一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
