识别表中的相关记录 [英] Identify Related Records in a Table
问题描述
我收到的数据包含类似的记录，其中客户有类似的记录。客户，即Jane Doe可能已经移动，或者某些东西，或者对她的帐户进行了更改。当发生这种情况时，她获得了一个新的客户ID，所有未来的订单都与此相关。所以现在我们有3个Jane Doe记录，但它们确实都是相关的。
我想做的是将它们识别为相关，以便用户可以查看记录和给定的客户与...有关。合理？让我们继续前进。
我已经开始把东西放在一起了。我认为它将在表单上很好地表示，但我想尽可能多地填充关系，以便用户不必手动完成所有操作。我希望有人可以帮我解决这个问题。
我有一个名为tblcustomers的表，现在我有一个名为tblrelatedcustomers的第二个表
tblcustomers
CiD - pk
CNum - 与订单绑定的客户编号
CName - 客户名称
tblrelatedcustomers
RiD - pk
RMasterC - 相关系列中的最小CNum（将在一分钟内解释相关规则） - 查找CiD
RRelatedC - 与RMasterC或主客户记录相关的CNum。 - 查询CiD
相关记录具有相同的客户名称。我想要做的是创建一个查询或某种类型的函数，找到重复项，然后用最小客户记录和与之相关的每个客户记录填充这个tblrelatedcustomers。
例如：
tblCustomers
CiD CNum CName
1 123 Jane Doe
2 124 Jane Doe
3 125 Jane Doe
4 126 John Doe
5 127 John Doe
我想要做的是填充tblRelatedCustomers，如下所示。
tblRelatedCustomers
RiD RMasterC RRelatedC
1 1 1
2 1 2
3 1 3
4 4 4
5 4 5
这有意义吗？任何人都可以帮忙吗？
谢谢！
I have received data which contains similar records, where the customers have similar records. A customer, i.e. Jane Doe may have moved, or something, or a change to her account. When that happened she was given a new customer ID, and all future orders were tied to that. So now we have 3 Jane Doe records, but they are really all related.
What I would like to do is identify them as related, so that users can see what records and given customer is related to. Make sense? Let''s move on.
I have started to put something together. I think it will represent on the form well, but I would like to populate as many of the relationships as I can, so that the users don''t have to do it all manually. I am hoping someone can help me with how to do this.
I have a table called tblcustomers and now I have a second table called tblrelatedcustomers
tblcustomers
CiD - pk
CNum - Customer number tied to orders
CName - Customer name
tblrelatedcustomers
RiD - pk
RMasterC - the min CNum in a related series (will explain rules for relating in a minute) - lookup to CiD
RRelatedC - a CNum related to the RMasterC or master customer record. - lookup to CiD
The related records have the same Customer name. What I would like to do is create a query or function of some sort that will find the duplicates and then populate this tblrelatedcustomers with the minimum customer record and each customer record it is related to.
For Example:
tblCustomers
CiD CNum CName
1 123 Jane Doe
2 124 Jane Doe
3 125 Jane Doe
4 126 John Doe
5 127 John Doe
What I want to do is populate tblRelatedCustomers as shown below.
tblRelatedCustomers
RiD RMasterC RRelatedC
1 1 1
2 1 2
3 1 3
4 4 4
5 4 5
Does this make sense? Can anyone help?
Thank you!
推荐答案
我有收到包含类似记录的数据，其中客户有类似的记录。客户，即Jane Doe可能已经移动，或者某些东西，或者对她的帐户进行了更改。当发生这种情况时，她获得了一个新的客户ID，所有未来的订单都与此相关。所以现在我们有3个Jane Doe记录，但它们确实都是相关的。
我想做的是将它们识别为相关，以便用户可以查看记录和给定的客户与...有关。合理？让我们继续前进。
我已经开始把东西放在一起了。我认为它将在表单上很好地表示，但我想尽可能多地填充关系，以便用户不必手动完成所有操作。我希望有人可以帮我解决这个问题。
我有一个名为tblcustomers的表，现在我有一个名为tblrelatedcustomers的第二个表
tblcustomers
CiD - pk
CNum - 与订单绑定的客户编号
CName - 客户名称
tblrelatedcustomers
RiD - pk
RMasterC - 相关系列中的最小CNum（将在一分钟内解释相关规则） - 查找CiD
RRelatedC - 与RMasterC或主客户记录相关的CNum。 - 查询CiD
相关记录具有相同的客户名称。我想要做的是创建一个查询或某种类型的函数，找到重复项，然后用最小客户记录和与之相关的每个客户记录填充这个tblrelatedcustomers。
例如：
tblCustomers
CiD CNum CName
1 123 Jane Doe
2 124 Jane Doe
3 125 Jane Doe
4 126 John Doe
5 127 John Doe
我想要做的是填充tblRelatedCustomers，如下所示。
tblRelatedCustomers
RiD RMasterC RRelatedC
1 1 1
2 1 2
3 1 3
4 4 4
5 4 5
这有意义吗？可以有人帮忙吗？
谢谢！
I have received data which contains similar records, where the customers have similar records. A customer, i.e. Jane Doe may have moved, or something, or a change to her account. When that happened she was given a new customer ID, and all future orders were tied to that. So now we have 3 Jane Doe records, but they are really all related.
What I would like to do is identify them as related, so that users can see what records and given customer is related to. Make sense? Let''s move on.
I have started to put something together. I think it will represent on the form well, but I would like to populate as many of the relationships as I can, so that the users don''t have to do it all manually. I am hoping someone can help me with how to do this.
I have a table called tblcustomers and now I have a second table called tblrelatedcustomers
tblcustomers
CiD - pk
CNum - Customer number tied to orders
CName - Customer name
tblrelatedcustomers
RiD - pk
RMasterC - the min CNum in a related series (will explain rules for relating in a minute) - lookup to CiD
RRelatedC - a CNum related to the RMasterC or master customer record. - lookup to CiD
The related records have the same Customer name. What I would like to do is create a query or function of some sort that will find the duplicates and then populate this tblrelatedcustomers with the minimum customer record and each customer record it is related to.
For Example:
tblCustomers
CiD CNum CName
1 123 Jane Doe
2 124 Jane Doe
3 125 Jane Doe
4 126 John Doe
5 127 John Doe
What I want to do is populate tblRelatedCustomers as shown below.
tblRelatedCustomers
RiD RMasterC RRelatedC
1 1 1
2 1 2
3 1 3
4 4 4
5 4 5
Does this make sense? Can anyone help?
Thank you!
但如果你真的有两个同名的人会怎么样？
但是无论如何，你可以跑追加查询并使用子查询返回Min（CiD）。
顺便说一下，你的CiD和CNum似乎服务于同样的目的，为什么重复数据？
But what happens if you really do have two different people with the same name?
But anyways, you can run an append query and use a subquery to return the Min(CiD).
By the way, your CiD and CNum seem to serve the same purpose, why the duplication of data?
感谢您的回复。事实证明，我们可以通过这种方式进行删除。 cnum是识别订单的数字，但由于这些数据不是我们的，我们在其他地方得到了它，我为表做了一个pk，jsut为了后人的缘故（为了解决这个问题太过分了）。
谢谢，
Thank you for your response. It turns out that we can de-dup in this manner. The cnum is the number that identifies the orders, but since this data is not ours, we got it elsewhere, I made a pk for the table, jsut for posterity sake (way too far in to undo that).
Thank you,
那么，一切顺序呢？它有用吗？
So, everything in order then? Got it working?
这篇关于识别表中的相关记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
