连接多个记录问题 [英] Concatenate multiple records question

问题描述

大家好，


我正在寻找关于如何处理一些数据的想法。我是VBA和sql的新手（但是喜欢学习并且没有任何运气使查询做我需要他们在这里做的事。有人指出我的方向会有所帮助。



问题：下面列出的数据是一个机车单元号，它是从商店购买和发布的日期和时间，以及已完成的工作代码。我需要的是发布时间（B）并将其计算到购物时间（C）。如果它们在24小时内，我需要将2个购物一起添加并连接工作代码，如示例＃2所示。我需要这个逻辑继续在最后一个发布日期的24小时内有购买的单位。这可能是1到15个购物。任何建议或推荐的学习材料，以帮助我弄清楚如何在sql或vba中这样做将非常有用。



示例＃1

Eqmt＃ShoppedDate已发布日期工作代码

6198 1 2007年1月11日下午9:50（A）11/12/2007 10:57 AM（B）美国TF

6198 11/12/2007 10:58 AM（C）11/15 / 2007 3:51 PM（D）PM PI

6198 11/15/2007 3:52 PM（E）11/15/2007 4:56 PM（F）PM TF

6198 11/15/2007 4:57 PM（G）11/17/2007 3:38 AM（H）PM EC MM



示例＃2

UP6198 11/11/2007 9:50 PM（A）11/15/2007 3:51 PM（D）US TF PM PI

Hello all,

I am looking for Ideas as to how to handle some data. I am new to VBA and sql(but love to learn and have not had any luck making queries do what I need them to do here. Can someone point me in a direction that would help.


The Problem : The data listed below is a single Locomotive unit number and the date and time that it was shopped and released from the shop as well as the Work codes that were completed. What I need is to take the Release time (B) and compair it to Shopped time (C). If they are within 24 hours i need to add the 2 shoppings together and concatenate the work codes as shown in example #2. I need this logic to contininue as long as there are units shopped within 24 hours of the last release date. This could range from 1 - 15 shoppings. Any suggestions or recommended study material to help me figure out how to do this in sql or vba would be greatly appretiated.


Example #1
Eqmt# ShoppedDate ReleasedDate Work Codes
6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM



Example#2
UP6198 11/11/2007 9:50 PM(A) 11/15/2007 3:51 PM(D) US TF PM PI

推荐答案

大家好，


我正在寻找关于如何处理的想法一些数据。我是VBA和sql的新手（但是喜欢学习并且没有任何运气使查询做我需要他们在这里做的事。有人指出我的方向会有所帮助。



问题：下面列出的数据是一个机车单元号，它是从商店购买和发布的日期和时间，以及已完成的工作代码。我需要的是发布时间（B）并将其计算到购物时间（C）。如果它们在24小时内，我需要将2个购物一起添加并连接工作代码，如示例＃2所示。我需要这个逻辑继续在最后一个发布日期的24小时内有购买的单位。这可能是1到15个购物。任何建议或推荐的学习材料，以帮助我弄清楚如何在sql或vba中这样做将非常有用。



示例＃1

Eqmt＃ShoppedDate已发布日期工作代码

6198 1 2007年1月11日下午9:50（A）11/12/2007 10:57 AM（B）美国TF

6198 11/12/2007 10:58 AM（C）11/15 / 2007 3:51 PM（D）PM PI

6198 11/15/2007 3:52 PM（E）11/15/2007 4:56 PM（F）PM TF

6198 11/15/2007 4:57 PM（G）11/17/2007 3:38 AM（H）PM EC MM



示例＃2

UP6198 11/11/2007 9:50 PM（A）11/15/2007 3:51 PM（D）US TF PM PI

Hello all,

I am looking for Ideas as to how to handle some data. I am new to VBA and sql(but love to learn and have not had any luck making queries do what I need them to do here. Can someone point me in a direction that would help.


The Problem : The data listed below is a single Locomotive unit number and the date and time that it was shopped and released from the shop as well as the Work codes that were completed. What I need is to take the Release time (B) and compair it to Shopped time (C). If they are within 24 hours i need to add the 2 shoppings together and concatenate the work codes as shown in example #2. I need this logic to contininue as long as there are units shopped within 24 hours of the last release date. This could range from 1 - 15 shoppings. Any suggestions or recommended study material to help me figure out how to do this in sql or vba would be greatly appretiated.


Example #1
Eqmt# ShoppedDate ReleasedDate Work Codes
6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM



Example#2
UP6198 11/11/2007 9:50 PM(A) 11/15/2007 3:51 PM(D) US TF PM PI

只想验证，这些是在一个表中，每个名称是单独的字段吗？这是否附加到新表。


所以这只有在同一个EQMT中有（A）和（C）时才有意义＃

Just want to verify, these are in one table and are each name seperate Fields? Is this Appending to a new table.

So this only matters if there are (A) and a (C) in the same EQMT#

感谢Adezii的慷慨提供时间和专业知识。


答案Adezii：包含所有这些数据的表是tblGordonData。


回答Jyoung2：示例＃1是从该表中复制的4条记录。每条记录都有不同的购物日期和时间以及不同的发布日期和时间。每组中可能有多达1-15个属于24小时组。所以基本上我需要第一条记录的购物时间和X记录的发布时间。 vbmenu_register（" postmenu_3038120"，true）;

Thank you Adezii for your generous offer of time and expertise.

Answer for Adezii: The table that contains all this data is tblGordonData.

Answer for Jyoung2: Example #1 is 4 records copied from this table exactly. Each record has a different shopping date and time and a different release date and time. There could be as many as 1-15 of each group that would fall into the 24 hour group. So basicly I need the Shop time of the first record and the release time of the X record. vbmenu_register("postmenu_3038120", true);

这篇关于连接多个记录问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！