Access的新手，需要帮助进行映射 [英] New to Access, need help with mapping

问题描述

你好，

我一直在使用excel一段时间，现在数据太重了，无法运行高效的vba宏。 

我有一个包含以下字段的数据表，其中许多都有重复的条目：保单持有人姓名，保单持有人ID，保单号和帐户

帐户字段包含一些被归类为UNMAPPED的条目

我有一个包含以下字段的映射表：保单持有人姓名，保单持有人ID，保单号码和账户描述

我在VBA中能够实现的是通过映射更新未映射的帐户他们首先是一个政策号码。如果映射表中的策略编号不匹配，则按策略持有者ID匹配该帐户。没有
匹配数字或ID的数据表中的任何条目都保留为UNMAPPED。

但数据表现在超过200,000行和代码：（a）运行时间太长，（b）导致excel挂起

我很想知道是否有人一种将代码移植到访问中的方法或可以完成的任何类似过程。我模糊地熟悉Left Joins并尝试了解决方案，但没有达到我想要的结果。结果表重复
几行。我将不胜感激如何在Access中开始解决这个问题。在此先感谢

解决方案

您好，

如果您在Access中执行此操作，您可能会甚至不需要任何代码。将两个Excel表导入Access表，您应该可以使用UPDATE查询来映射帐户。

您可以在一次通过中执行此操作，但可能更安全地在两个中执行传递。

第一个传递是使用策略编号进行映射，第二个传递是使用策略持有者。

希望它有所帮助。 ..

Hello,

I have been working with excel for a while now and the data is now too heavy to run an efficient vba macro. 

I have a data table with the following fields, many of which have duplicate entries: Policy Holder Name, Policy Holder ID, Policy Number and Account

The account field has some entries that are classified as UNMAPPED

I have a mapping table with the following fields: Policy Holder Name, Policy Holder ID, Policy Number and Account description

What i was able to achieve in VBA was updating the unmapped accounts by mapping them first with a policy number. If there is not match for policy number in the mapping table then match the account by policy holder ID. Any entries in the data table without matching numbers or IDs are left as UNMAPPED.

However the data table is now over 200,000 lines and the code : (a) takes too long to run, (b) causes excel to hang

I was curious to know if theres a way to port over the code into access or any similar procedure that can be done. I am vaguely familiar with Left Joins and attempted a solution but didnt achieve the results I wanted. The resulting table was duplicating several rows. I would appreciate any help or indication as to how to begin tackling this in Access. Thanks in advance

解决方案

Hi,

If you perform this operation in Access, you might not even need any code. Importing both Excel table into Access tables, you should be able to use an UPDATE query to map the accounts.

You could probably do it in one pass, but probably safer to do it in two passes.

The first pass is to map using the policy number, and the second pass is to use the policy holder.

Hope it helps...

这篇关于Access的新手，需要帮助进行映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！