根据MS Access 2013中的多个字段识别客户 [英] Identify customers based on more than one field in MS access 2013

问题描述

我想创建客户的Microsoft Access（2013）数据库，其客户通过其名称和地址进行标识（我想允许空地址）。例如：



它应该接受



名称---------- ---地址

Joe Smith -------- 123 Main St.

Sarah Green ------ 123 Main St.

Bob Baker -------- Null

Joe Smith -------- 456 First St.



我尝试了什么：



我尝试的最后一个解决方案是将Name和Address作为主键。不幸的是，这不允许地址列中的空值。名称列中不会有任何空值，但会有重复。



我建议的另一个解决方案是建立一个自动编号列并调用它是CustomerID。我想我最终可能会把它包括在内，但这样做可以让我给同一个客户两个不同的客户ID就像这样：



客户ID ------姓名-----------地址

1 ----------------- Joe Smith ------- 123 Main St.

2 ----------------- Joe Smith ------- 123 Main St.

I want to create Microsoft Access (2013) database of customers where customers are identified by their name and address (I want to allow for null addresses). So for example:

It should accept

Name-------------Address
Joe Smith--------123 Main St.
Sarah Green------123 Main St.
Bob Baker--------Null
Joe Smith--------456 First St.

What I have tried:

The last solution I tried was to make both Name and Address the primary key. Unfortunately, that does not allow for null values in the address column. There won't be any nulls in the name column, but there will be duplicates.

Another solution I expect will be suggested is to make an auto number column and call it CustomerID. I think I may end up including that, but doing that would allow me to give the same customer two different customer ID's like so:

CustomerID------Name-----------Address
1-----------------Joe Smith-------123 Main St.
2-----------------Joe Smith-------123 Main St.

推荐答案

整个计划的问题在于你可以得到两个名字和地址相同的顾客：例如父亲和儿子可能住在同一个房子里。

此外，主要街道是一个常见的地址，所以很有可能你会在不同的城市获得相同的名称和地址对。

当乔史密斯穿过城市时会发生什么？使用变量字段作为复合键的一部分，你必须在几个地方更新它并且很容易错过一些。



通常，主键是是一个客户ID - 这使得从其他数据表（如AccoungStatus，OrdersPending，OrderHistory，Invoices等）引用更容易 - 并使用唯一单个字段进行登录，例如电子邮件或用户名您会找到一次CustomerID并将其保留在会话中。

The problem with your whole scheme is that you can get two customers with the same name and address: a father and son for example may well live in the same house.
Additionally, "Main St." is a common address, so it quite possible you will get the same name and address pair in different cities.
And what happens when Joe Smith moves across the city? With a "variable field" as part of a composite key you have to update it in several places and it's very easy to miss some.

Normally, the primary key would be a customer ID - which makes it a lot easier to reference back from other data tables such as AccoungStatus, OrdersPending, OrderHistory, Invoices, and so on - and use a "unique" single field for login such as the email or username which lets you find out the CustomerID once and retain it for the session.

这篇关于根据MS Access 2013中的多个字段识别客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！