Identify customers based on more than one field in MS access 2013

Question

0.00/5 (No votes)

See more:

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.

Posted 19-Nov-17 21:58pm

Member 13530575

Updated 19-Nov-17 22:15pm

Add a Solution

Comments

Richard MacCutchan 20-Nov-17 4:07am

Why would you ever want two entries for the same customer?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-11-19T22:15:00

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.