Setting up Customer ID as Primary Key

It's very rare for a sequential number to be required as a primary key; its only requirement in the vast majority of cases is for the values to be distinct, which is exactly what an autonumber gives you.  The actual values of the numbers is irrelevant and need never be seen by users of the database.

Tables are related in two different ways:

1.  By having a foreign key column in one table reference the primary key of another table.  This is a one-to-many, or very occasionally a one-to-one, relationship type.  Technically it's a unary relationship type.  An example is where a CustomerID foreign key column in an Orders table references the CustomerID primryay key column of a Customers table.

2.  Where the relationship type is represented by a table which resolves a many-to-many relationship type into two or more one-to-many relationship types.  Technically this can be a binary (2-way), tertiary (3-way) etc relationship type.  An example is an OrderDetails table which models a binary many-to-many relationship type between Orders and Products.

The model below from one of my on-line demos might help you understand how a database is built up of related tables.

The above is the model for the InvoicePDF demo which you'll find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file is primarily intended to illustrate how to output a report (the invoice) as a PDF file, but it happens to provide an example of the sort of model which you are trying to build.  It also shows how data is input into the table via a form in which there are a number of subforms.  In particular note how the invoice details subform is used for inserting data into the table which models the many-to-many relationship type between Invoices and Products.

I don't really.  I just need the Customer ID to remain sequential, which as you stated I can put as an expression.  I had that laid out, but I was told to keep IDs as numeric values.

You make a good point and I didn't think about it like that.  I will try to use the AutoNumber to relate all the fields together.  How do I use the AutoNumber from the Customer Contact Information with the Order Form and Order Content then?  Since those 3 tables will have different AutoNumber values, what would relate them?

The sequence doesn't really matter for any purpose.  I would have a date field anyway so I can use that if I need it for any reason.  

I had no real reason behind breaking the tables apart other than the fact that is what it is like now in the spreadsheet.  I just joined the team and am working with what I have so that was the only reason.  Yes, contact information is e-mail address, physical address, and phone number.

Let me ask this? Why do you need an exact sequential number? There are times when sequential numbering is needed, but not for a primary key. A Primary key is a unique value that identifies a single record. It should be never changing. Many people will tell you that no one should actually see the PK since its used internally by your application. I don't see a problem with making it visible is necessary. 

As for using the person's initials as a prefix, I disagree with  for a PK on three counts. First, Initials can change. , they are not unique and that would make your PK a text value which would waste space. A long Integer (which is what an autonumber is) takes 4 bytes, you are talking about 6 bytes. Not a lot but it could make a difference.

Getting back to the sequence. Does it really matter that you can tell that Richard Brown was the first customer just by looking at his ID? I can't see a reason for that. Sequential Numbering is used primarily where you can't have gaps like in Order #s or the like. You can do Sequential Numbering in a couple of ways (see my blog on Sequential Numbering). But I can't see a value in using it for a Customer ID. 

I would recommend that you use an Autonumber as your Primary key and use it to link your tables. If you want to show an ID that includes the customer initials, you can do that with an expression. Like:

Left(Firstname,1) & Left(Lastname,1) & Format(CustomerID,"0000")

I see no reason for 2 tables for the customer info as you describe. If I knew more about what info you are holding, I might say different. The time to use a 1:1 is when you have additional data that only applies to some parent records. But since each customer will have a profile record, then I don't think its necessary.

However, contact info maybe should go in a child table. I'm assuming that by contact info, you mean phone numbers and e-mail addresses. So you might have a table like this:

tblCustContact

CustContactID (PK Autonmber)

CustomerID (Foreign Key)

ContactTypeID (FK)

ContactData 

ContactTypeID would come from a lookup table that listed types like Home Phone, Work Phone, Cell phone, Fax, Work E-mail, Personal E-Mail etc.

So let me know why you think you need an exact sequential number. You may convince me that it is needed for your app, though I doubt it.

Thanks for the reply.

I will take a look at Normalization principles for databases.

I was hoping to create a unique ID starting at 1 that increments 1 by each row.  I saw the AutoNumber field, but if you have to delete a row it continues counting and doesn't reuse that number.  Is there a way to change that behavior in the table itself?  Right now in Excel we are using the first letter from the first and last name along with an appended 1001 where the number increments for each customer (Ex. Richard Brown would be RB1001 if he was the first entry.  If you think there is a better way to create or generate the Customer ID, I am open to anything.

I could put all the information in one table, but I thought it might be easier to keep it in two.  I have very little experience in databases so I am not sure the best way to lay things out.

I'm at a start up company now so the database is very small.  There will just be 3 or 4 users handling actually inputting the data.  I will just be in charge of pulling the reports and keeping it organized.  I'll take a look at that multi-user database article as well.

First, if you are new to Access and databases, I suggest you review Normalization principles. 

Second, as a general rule we do not store calculated values. What calculation did you want to use for the CustomerID? And why do you want a calculation?

Third, What you describe between Customer Contact and Profile is a one to one relationship. These are rare. Why can't you put all the info in one table?

Fourth, Any multi-user database NEEDS to be split. See this article: http://www.utteraccess.com/forum/index.php?showtopic=1264601

Please answer my questions so we can help further.