匹配来自多个来源和格式的部门代码 [英] Matching dept codes from multiple sources and formats

问题描述

我们所有的部门都有代码来识别它们。在提取数据以查看风险分析和成本评估时，我从多个来源获取信息。我试图让所有位置代码匹配。收到的数据示例

10010-0010-301水线

0010-301水线

0010301水线

10301水线

100100010301水线


遗憾的是，工资单，ncci代码，员工数量，损失或预算没有协调，所以数据很难编译。在使用数据之前，我需要使所有代码都相同。

All of our departments have a code to identify them. When pulling data to look at risk analisys and cost evaluations I get info from multiple sources. I am attempting to get all location codes to match. Examples of data received
10010-0010-301 Water lines
0010-301 Water lines
0010301 Water lines
10301 Water lines
100100010301 Water lines

Unfortunately there is no coordination of payroll, ncci code, employee count, losses or budget so the data is difficult to compile. I need to have all codes the same before I can utilize data.

推荐答案

你有一个*这里有一团糟。

研究你添加文字水线的概念。 （否则，为什么不使用它？）你实际拥有的数值是用来查看你的示例数据，它看起来是：

" 10301 Water lines"与其余成员最为相似。
IFF 这是正确的，因为有类似的共性，那么你有一个简单的解决方案的希望，你可以使用替换功能删除破折号，然后使用正确的函数返回最后五位数。


然而，生活很少那么好。


我可能会这样做的一个方法就是拥有一个像water这样的独特名称的表格。 "笔，"等等。然后是一个带有外部代码的表，来自唯一名称，位置代码。然后使用链接表，或者甚至在第二个表中与第一个表有关系。

乍一看它会从这些行开始：

tbl_db_locationcode：

[db_locationcode_pk] autonumber;主键。

[db_locationcode_name] text（50）;必填，无重复

（这将成为您的数据库内部标准化位置代码）


tbl_os_source：

[ os_source_pk] autonumber;主键。

[os_source_name] text（50）;必填，无重复

（使用此表来帮助标准化源名称，如NCCI和Payroll等...）


tbl_os_locationcode：

[os_locationcode_pk] autonumber;主键。

[os_locationcode_fk_os_source] tbl_os_source上的1对多PK

[os_locationcode_fk_db_locationcode] tbl_os_source上的1到多个PK

[os_locationcode_code] text（ 50）;需要;没有重复。

{{应该添加复合键

[os_locationcode_fk_os_source]

[os_locationcode_code]

- 需要是唯一的，以防止添加相同的源和位置代码更多一次}}

（现在这个表将有一个条目为每个来源的位置代码...所以你得到一个表与与您在帖子中列出的信息非常相似）


tbl_metricdata：

[metricdata_pk] autonumber;主键。

[metricdata_fk_DB_locationcode] tbl_db_locationcode上的1到多个

[metricdata _（...）]


（（或者也许

tbl_metricdata：

[metricdata_pk] autonumber;主键。

[metricdata_fk_OS_locationcode] tbl_OS_locationcode上的1到多个

[metricdata _（...）]

））


设置tbl_os_locationcode：

现在进入你的查询，在tbl_os_locationcode上构建查询，拉入所有字段（不要使用星号）。

您将根据表格为字段[os_locationcode_fk_os_source]和[os_locationcode_fk_db_locationcode]设置查找控件。 ..这样你就可以使用人类可读的名字。领域。

您可以构建一个绑定到此查询的表单，如果您使用向导它应该为两个查找设置组合框，如果没有，那么您可以手动执行此操作。


设置tbl_metricdata

根据您使用的是哪一个，基于db_locationcode的查询，或者

非常类似于您将使用查询，根据源和位置字段构建查找字段（连接源代码和位置代码）并输入其他数据。表格中的内容相同。


这是交易，您现在可以按来源（工资单，工资单等）或外部提取外部和内部位置代码代码，或内部数据库代码等...并通过关系查询度量表


应该注意的是，这只是一个初步的想法，没有对数据库设计进行正式分析我通常会做这样的事情...基本上，我遗漏了一些细节;但是，我希望你能看到我在这里做的事情

（>数据库规范化和表结构。）

You have one "*" of a mess here.
Working on the concept that you added the text "Water Lines" (otherwise, why not use that?) and what you actually have is the numeric values to work with then looking at your example data, it appears that:
"10301 Water lines" has the most in common with the remaining members.
IFF this holds true in that there are simular commonalities then you have a hope of an easy solution in that you can use the replace function to remove the dashes and then use the right function to return the last five digits.

However, life is rarely that nice.

One way I might do this is to have a table with the unique names like "water," "pen," etc.. then a table with the outside codes, who from, the unique name, the location code. Then either use a linking table, or even in the second table have the relationship to the first.

On first blush it would start out along these lines:

tbl_db_locationcode:
[db_locationcode_pk] autonumber; primary key.
[db_locationcode_name] text(50); required, no duplicate
(this will become your databases internal standardized location code)


tbl_os_source:
[os_source_pk] autonumber; primary key.
[os_source_name] text(50); required, no duplicate
(Use this table to help standardize the source names such as NCCI and Payroll etc...)

tbl_os_locationcode:
[os_locationcode_pk] autonumber; primary key.
[os_locationcode_fk_os_source] 1 to many on tbl_os_source PK
[os_locationcode_fk_db_locationcode] 1 to many on tbl_os_source PK
[os_locationcode_code] text(50); required; no duplicates.
{{Should Add compound key on
[os_locationcode_fk_os_source]
[os_locationcode_code]
- needs to be unique to prevent adding the same source and location codes more that once}}
(Now this table will have one entry for each source''s location code... so you get a table with the information much like you listed in your post)

tbl_metricdata:
[metricdata_pk] autonumber; primary key.
[metricdata_fk_DB_locationcode] 1 to many on tbl_db_locationcode
[metricdata_(...)]

(( or maybe
tbl_metricdata:
[metricdata_pk] autonumber; primary key.
[metricdata_fk_OS_locationcode] 1 to many on tbl_OS_locationcode
[metricdata_(...)]
))


Setting up tbl_os_locationcode:
Now in your query, build a query on tbl_os_locationcode pulling in all of the fields (do not use the star).
You will setup the lookup control for the fields [os_locationcode_fk_os_source] and [os_locationcode_fk_db_locationcode] based on their tables... this way you can use the human readable "Name" fields.
You can build a form that is bound to this query, if you use the wizard it should setup comboboxes for the two lookups if not, then you can do this by hand.

Setting up tbl_metricdata
Depending on which one you use, either query based on the db_locationcode or,
Very simular in that you would use a query, build a lookup field against your source and location fields (concatenate the source and location code) and enter your other data. Same thing within your forms.

Here''s the deal, you can now pull the outside and internal location codes by source (payroll, ncci, etc...) or by outside codes, or the internal db code etc... and by relationships query the metrics table

It should be noted that this is just an initial thought without a formal analysis of the database design that I would normally do to construct something like this... basically, I''ve left out some of the details; however, I hope you can see what I''m doing here
(> Database Normalization and Table Structures. )

假设您的位置代码最低限度一致，即：存在某种伪数字代码最多12个字符，后跟一个空格，然后是某种标识符（在这种情况下将使用实用程序），您可以：

Assuming your Location Codes are minimally consistent, namely: There is some kind of Pseudo Numeric Code up to a Maximum of 12 Characters followed by a single Space and then some kind of Identifier (will use Utility in this case), you can:

1. 附加所有位置将代码编码到单个TEXT字段中。
2. 使用计算字段创建查询。这个计算字段将传递每个位置代码的值，在那里它将被解析，分析，格式正确，然后成为函数的返回值。
3. 示例数据，您的原始和一些已添加：
   

1. Append all Location Codes into a single TEXT Field.
2. Create a Query using a Calculated Field. This Calculated Field will pass the Value of each Location Code where it will be parsed, analyzed, properly formatted, then become the return Value of the Function.
3. Sample Data, your Original and some Added:
   

展开 | 选择 | Wrap | 行号

ADezii：

符合我的建议;然而，很多改进和漂亮的光滑！

我可以看到在查询中自动化它如何通过使用结果并从两个表到一个表和一个查询来引导我正在考虑的其余部分。

ADezii:
Along the lines of what I was suggesting; however much improved and pretty slick!
And I can see by automating that in the query how it could lead into the rest of what I was thinking about by using the result and going from two to one table and a query.

这篇关于匹配来自多个来源和格式的部门代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！