Access SQL中的数据突破 [英] Data Breakout in Access SQL

问题描述

在tblMain中我有一个包含以下格式的txt的字段：


" firstname lastname（misc）"


并非所有条目都有（misc）。


我需要打破这三个字段


名字，姓氏和其他字段包括在内条目。


谢谢

in tblMain I have a field that contains txt in the following format:

"firstname lastname (misc)"

Not all entries have the (misc).

I need to break out the three fields

firstname, lastname, and other when included in the entry.

Thanks

推荐答案

你想如何打破这些数据？在一个新表中？


一种方法是创建包含所需字段的新表，然后在VBA中创建基于旧表的记录集，并循环显示每个字符串，查找空格字符（"），然后将字符串拆分为左，中，右（如果​​有）部分，并将这些子字符串保存到新表中。


这应该指向正确的方向。请告诉我们您可能需要其他帮助的位置。

How do you want to break out this data? In a new table?

One way to do it is to create the new table with the fields you want, then, in VBA create a recordset based on the old table, and cycle through each string, looking for the space characters (" "), then split up the string into the Left, middle and right (if any) portions and save those substrings to the new table.

This should point you in the right direction. Let us know where you might need additional help.

这是您从未在单个字段中存储组合数据的完美示例。这些值应该存储在三个单独的字段中，并在需要时将放在一起。


尽管如此，你已经问过要拆分它们以便我们更好处理那个。不是那么容易，因为你还没有解释你想要将数据分解到哪里，甚至没有解释确定断开数据的哪个点的逻辑（是第一个和第二个空格吗？括号是否是更可靠的指标？）。如果您真的想要一个可用的答案，所有这些都是需要包含在问题中的重点。简单来说，您可以使用VBA中的 Split（）函数生成由空格分割的项目数组。这可能会对你有所帮助，但我怀疑你需要更多。如果是这样，请告诉我们完整的问题，以便我们提供帮助。

This is a perfect illustration of why you never store combined data in a single field. These values should be stored in three separate fields and put together when required.

Nevertheless, you''ve asked about splitting them so we''d better deal with that. Not so easy as you haven''t explained where you want the data broken out to or even exactly the logic which determines which point at which to break the data (Is it by the first and second spaces? Are the parentheses a more reliable indicator?). All these are important points that need to be included in a question if you actually want a usable answer. In very simple terms you can use the Split() function in VBA to produce an array of items split by the spaces. This may help you, but I suspect you need more than that. If so, let us know the full question so we can help.

感谢您的回复，伙计们。确切地说，新......完美的插图。我只是一个讨厌的人，并没有像我想的那样经常使用这些程序功能，但我很抱歉没有更清楚。


原始表有字段：

uid，person_info

uid是自动编号，person_info是firstname lastname（misc），这是一段可怕的数据。


我想运行一个make table查询来转储这些信息，如下所示：

uid

pFirstName

pLastName

pCoded


括号不是分隔符，但实际上在文本字段中，问题是，并非所有订单项在那里有（misc）条目，通过空格修剪使一些功能错误。


我一次又一次丢失。


感谢所有的帮助！

Thanks for the reply, folks. Exactly, Neo...perfect illustration. I''m just a dabbler and don''t get to use these programmings function as often as I would like, but I am sorry for not being more clear.

the original table has the fields:
uid, person_info

uid is autonumber and person_info is "firstname lastname (misc)", a horrible piece of data.

I would like to run a make table query to dump these informations as follows:
uid
pFirstName
pLastName
pCoded

The parenthesis aren''t a delimiter, but actually in the text field and the problem is, is that not all line items have the (misc)in there entry, making a trim by space make some functions errors.

I am once and again lost.

thanks for all the help!

这篇关于Access SQL中的数据突破的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！